Ответ 1

Я бы рассмотрел query_a как канонический запрос, когда сомневался.

Я нашел способ сделать query_a на полсекунды быстрее.

Ваш внутренний запрос из query_a

SELECT DISTINCT ON (pr_id, pa_id)

требуется

ORDER BY pr_id, pa_id, t DESC

особенно с pr_id и pa_id, указанными первыми. c_id = 5 является константой, но вы не можете использовать свой индекс event_idx (c_id, t DESC, pr_id, pa_id, r), потому что столбцы не организованы (pr_id, pa_id, t DESC), что требует ваше предложение ORDER BY. Если у вас был индекс не менее (pr_id, pa_id, t DESC), сортировка не должна произойти, потому что условие ORDER BY выравнивается с этим индексом.

Итак, вот что я сделал.

CREATE INDEX events_idx2 ON events (c_id, pr_id, pa_id, t DESC, r);

Этот индекс может использоваться вашим внутренним запросом - по крайней мере теоретически. К сожалению, планировщик запросов считает, что лучше сократить количество строк с помощью индекса events_idx с помощью c_id и x <= t < y. Postgres не имеет указателей, поэтому нам нужен другой способ убедить планировщика запросов взять новый индекс events_idx2.

Один из способов принудительного использования events_idx2 - сделать другой индекс более дорогим. Это можно сделать, удалив последний столбец r из events_idx и сделав его непригодным для query_a (по крайней мере, непригодным для использования без загрузки страниц из кучи).

Контр-интуитивно перемещать столбец t позже в макете индекса, потому что обычно первые столбцы будут выбраны для = и диапазонов, для которых c_id и t подходят хорошо. Однако ваш ORDER BY (pr_id, pa_id, t DESC) мандат, по крайней мере, этого подмножества как есть в вашем индексе. Конечно, мы по-прежнему сначала помещаем c_id для сокращения строк как можно быстрее.

У вас все еще может быть индекс на (c_id, t DESC, pr_id, pa_id), если вам нужно, но он не может использоваться в query_a.

Вот план запроса для query_a с events_idx2 и events_idx удален. Найдите events_c_id_pr_id_pa_id_t_r_idx, так как индексы имен PG автоматически, если вы не дадите им имя. Мне это нравится, потому что я вижу порядок столбцов в имени индекса в каждом плане запроса.

 Sort  (cost=30076.71..30110.75 rows=13618 width=23) (actual time=426.898..426.914 rows=30 loops=1)
   Sort Key: (count(1)), events.r, events.pr_id DESC
   Sort Method: quicksort  Memory: 27kB
   ->  HashAggregate  (cost=29005.43..29141.61 rows=13618 width=23) (actual time=426.820..426.859 rows=30 loops=1)
         Group Key: events.pr_id, events.r
         ->  Unique  (cost=0.56..26622.33 rows=136177 width=40) (actual time=0.037..328.828 rows=117204 loops=1)
               ->  Index Only Scan using events_c_id_pr_id_pa_id_t_r_idx on events  (cost=0.56..25830.50 rows=158366 width=40) (actual time=0.035..178.594 rows=154940 loops=1)
                     Index Cond: ((c_id = 5) AND (t >= '2017-01-03 00:00:00'::timestamp without time zone) AND (t < '2017-01-06 00:00:00'::timestamp without time zone))
                     Heap Fetches: 0
 Planning time: 0.201 ms
 Execution time: 427.017 ms
(11 Zeilen)

Планирование выполняется мгновенно, а производительность - второй, потому что индекс соответствует ORDER BY внутреннего запроса.

При хорошей производительности на query_a нет необходимости в дополнительной функции для ускорения альтернативных запросов query_b и query_c.

Примечания:

Как-то я не мог найти первичный ключ в ваших отношениях. Вышеупомянутое предлагаемое решение работает без каких-либо первичных предположений. Я все еще думаю, что у вас есть первичный ключ, но, возможно, забыл упомянуть об этом.

Естественный ключ pa_id. Каждый pa_id означает "вещь", которая имеет Об этом сообщается около 1... 30 событий.

Если pa_id относится к нескольким c_id 's, то pa_id не может быть ключом. Если pr_id и r являются данными, возможно, (c_id, pa_id, t) является уникальным ключом? Также ваш индекс events_idx не создается уникальным, но охватывает все столбцы отношения, поэтому вы можете иметь несколько равных строк - вы хотите разрешить это?

Если вам действительно нужны оба показателя events_idx и предлагаемый events_idx2, то вы будете иметь данные, которые будут храниться 3 раза (в два раза по индексам, один раз в куче).

Так как это действительно сложная оптимизация запросов, я прошу вас хотя бы подумать над добавлением щедрости для тех, кто отвечает на ваш вопрос, также потому, что он довольно долго сидел на SO без ответа.

РЕДАКТИРОВАТЬ A Я вставил еще один набор данных, используя превосходную настройку выше, в основном удваивая количество строк. На этот раз даты начинались с '2017-01-10'. Все остальные параметры остались прежними.

Вот частичный индекс атрибута времени и его поведение запроса.

CREATE INDEX events_timerange ON events (c_id, pr_id, pa_id, t DESC, r) WHERE '2017-01-03' <= t AND t < '2017-01-06';

Sort  (cost=12510.07..12546.55 rows=14591 width=23) (actual time=361.579..361.595 rows=30 loops=1)
   Sort Key: (count(1)), events.r, events.pr_id DESC
   Sort Method: quicksort  Memory: 27kB
   ->  HashAggregate  (cost=11354.99..11500.90 rows=14591 width=23) (actual time=361.503..361.543 rows=30 loops=1)
         Group Key: events.pr_id, events.r
         ->  Unique  (cost=0.55..8801.60 rows=145908 width=40) (actual time=0.026..265.084 rows=118571 loops=1)
               ->  Index Only Scan using events_timerange on events  (cost=0.55..8014.70 rows=157380 width=40) (actual time=0.024..115.265 rows=155800 loops=1)
                     Index Cond: (c_id = 5)
                     Heap Fetches: 0
 Planning time: 0.214 ms
 Execution time: 361.692 ms
(11 Zeilen)

Без индекса events_timerange (регулярный полный индекс).

Sort  (cost=65431.46..65467.93 rows=14591 width=23) (actual time=472.809..472.824 rows=30 loops=1)
   Sort Key: (count(1)), events.r, events.pr_id DESC
   Sort Method: quicksort  Memory: 27kB
   ->  HashAggregate  (cost=64276.38..64422.29 rows=14591 width=23) (actual time=472.732..472.776 rows=30 loops=1)
         Group Key: events.pr_id, events.r
         ->  Unique  (cost=0.56..61722.99 rows=145908 width=40) (actual time=0.024..374.392 rows=118571 loops=1)
               ->  Index Only Scan using events_c_id_pr_id_pa_id_t_r_idx on events  (cost=0.56..60936.08 rows=157380 width=40) (actual time=0.021..222.987 rows=155800 loops=1)
                     Index Cond: ((c_id = 5) AND (t >= '2017-01-03 00:00:00'::timestamp without time zone) AND (t < '2017-01-06 00:00:00'::timestamp without time zone))
                     Heap Fetches: 0
 Planning time: 0.171 ms
 Execution time: 472.925 ms
(11 Zeilen)

С частичным индексом время выполнения примерно на 100 мс быстрее, а вся таблица в два раза больше. (Примечание: во второй раз это было всего лишь на 50 мс. Преимущество должно увеличиваться, тем больше событий регистрируется, потому что запросы, требующие полного индекса, будут замедляться, поскольку вы подозреваете (и соглашаетесь)). Кроме того, на моей машине полный индекс составляет 810 МБ для двух вставок (создайте таблицу + дополнительно с 2017-01-10). Частичный индекс WHERE 2017-01-03 <= t < 2017-01-06 - всего 91 МБ. Возможно, вы можете создавать частичные индексы ежемесячно или ежегодно? В зависимости от того, какой временной диапазон запрашивается, возможно, нужно индексировать только последние данные или иначе только старые данные частично?

Я также попробовал частичное индексирование с помощью WHERE c_id = 5, поэтому разбиение на разделы на c_id.

Sort  (cost=51324.27..51361.47 rows=14880 width=23) (actual time=550.579..550.592 rows=30 loops=1)
   Sort Key: (count(1)), events.r, events.pr_id DESC
   Sort Method: quicksort  Memory: 27kB
   ->  HashAggregate  (cost=50144.21..50293.01 rows=14880 width=23) (actual time=550.481..550.528 rows=30 loops=1)
         Group Key: events.pr_id, events.r
         ->  Unique  (cost=0.42..47540.21 rows=148800 width=40) (actual time=0.050..443.393 rows=118571 loops=1)
               ->  Index Only Scan using events_cid on events  (cost=0.42..46736.42 rows=160758 width=40) (actual time=0.047..269.676 rows=155800 loops=1)
                     Index Cond: ((t >= '2017-01-03 00:00:00'::timestamp without time zone) AND (t < '2017-01-06 00:00:00'::timestamp without time zone))
                     Heap Fetches: 0
 Planning time: 0.366 ms
 Execution time: 550.706 ms
(11 Zeilen)

Таким образом, частичная индексация также может быть жизнеспособным вариантом. Если вы получаете все больше данных, вы также можете рассмотреть раздел, например, все строки в возрасте от двух лет и старше в отдельную таблицу или что-то еще. Я не думаю, что здесь могут помочь индексы блоков Range BRIN (индексы). Если машина более мясистая, чем моя, то вы можете просто вставить 10-кратное количество данных и сначала проверить поведение обычного полного индекса и как он ведет себя в возрастающей таблице.

Ответ 2

[EDITED] Хорошо. Поскольку это зависит от вашего распределения данных, это еще один способ сделать это.

Сначала добавьте следующий индекс:

CREATE INDEX events_idx2 ON events (c_id, t DESC, pr_id, pa_id, r);

Извлеките MAX(t) как можно быстрее, исходя из предположения, что подмножество будет меньше для объединения в родительскую таблицу. Однако он может быть медленнее, если набор данных не так мал.

SELECT
    e.pr_id,
    e.r,
    count(1) AS quantity
FROM events e
JOIN (
    SELECT
        pr_id,
        pa_id,
        MAX(t) last_t
    FROM events e
    WHERE
        c_id = 5 
        AND t >= '2017-01-03Z00:00:00' 
        AND t < '2017-01-06Z00:00:00'
    GROUP BY 
        pr_id, 
        pa_id
) latest 
    ON (
        c_id = 5 
        AND latest.pr_id = e.pr_id
        AND latest.pa_id = e.pa_id
        AND latest.last_t = e.t
    )
GROUP BY
    e.pr_id,
    e.r
ORDER BY 3, 2, 1 DESC

Полный скрипт

SQL Fiddle

Настройка схемы PostgreSQL 9.3:

--PostgreSQL 9.6
--'\\' is a delimiter

-- CREATE TABLE events AS...

VACUUM  ANALYZE events;
CREATE INDEX idx_events_idx ON events (c_id, t DESC, pr_id, pa_id, r);

Запрос 1:

  -- query A
explain analyze SELECT
        pr_id,
        r,
        count(1) AS quantity
    FROM (
        SELECT DISTINCT ON (pr_id, pa_id)
          pr_id,
          pa_id,
          r
        FROM events
        WHERE
          c_id = 5 AND
          t >= '2017-01-03Z00:00:00' AND
          t < '2017-01-06Z00:00:00'
        ORDER BY pr_id, pa_id, t DESC
    ) latest
    GROUP BY
        1,
        2
    ORDER BY 3, 2, 1 DESC

Результаты:

QUERY PLAN
Sort  (cost=2170.24..2170.74 rows=200 width=15) (actual time=358.239..358.245 rows=30 loops=1)
Sort Key: (count(1)), events.r, events.pr_id
Sort Method: quicksort  Memory: 27kB
->  HashAggregate  (cost=2160.60..2162.60 rows=200 width=15) (actual time=358.181..358.189 rows=30 loops=1)
->  Unique  (cost=2012.69..2132.61 rows=1599 width=40) (actual time=327.345..353.750 rows=12098 loops=1)
->  Sort  (cost=2012.69..2052.66 rows=15990 width=40) (actual time=327.344..348.686 rows=15966 loops=1)
Sort Key: events.pr_id, events.pa_id, events.t
Sort Method: external merge  Disk: 792kB
->  Index Only Scan using idx_events_idx on events  (cost=0.42..896.20 rows=15990 width=40) (actual time=0.059..5.475 rows=15966 loops=1)
Index Cond: ((c_id = 5) AND (t >= '2017-01-03 00:00:00'::timestamp without time zone) AND (t < '2017-01-06 00:00:00'::timestamp without time zone))
Heap Fetches: 0
Total runtime: 358.610 ms

Запрос 2:

  -- query max/JOIN
explain analyze     SELECT
        e.pr_id,
        e.r,
        count(1) AS quantity
    FROM events e
    JOIN (
        SELECT
            pr_id,
            pa_id,
            MAX(t) last_t
        FROM events e
        WHERE
            c_id = 5 
            AND t >= '2017-01-03Z00:00:00' 
            AND t < '2017-01-06Z00:00:00'
        GROUP BY 
            pr_id, 
            pa_id
    ) latest 
        ON (
            c_id = 5 
            AND latest.pr_id = e.pr_id
            AND latest.pa_id = e.pa_id
            AND latest.last_t = e.t
        )
    GROUP BY
        e.pr_id,
        e.r
    ORDER BY 3, 2, 1 DESC 

Результаты:

QUERY PLAN
Sort  (cost=4153.31..4153.32 rows=1 width=15) (actual time=68.398..68.402 rows=30 loops=1)
Sort Key: (count(1)), e.r, e.pr_id
Sort Method: quicksort  Memory: 27kB
->  HashAggregate  (cost=4153.29..4153.30 rows=1 width=15) (actual time=68.363..68.371 rows=30 loops=1)
->  Merge Join  (cost=1133.62..4153.29 rows=1 width=15) (actual time=35.083..64.154 rows=12098 loops=1)
Merge Cond: ((e.t = (max(e_1.t))) AND (e.pr_id = e_1.pr_id))
Join Filter: (e.pa_id = e_1.pa_id)
->  Index Only Scan Backward using idx_events_idx on events e  (cost=0.42..2739.72 rows=53674 width=40) (actual time=0.010..8.073 rows=26661 loops=1)
Index Cond: (c_id = 5)
Heap Fetches: 0
->  Sort  (cost=1133.19..1137.19 rows=1599 width=36) (actual time=29.778..32.885 rows=12098 loops=1)
Sort Key: (max(e_1.t)), e_1.pr_id
Sort Method: external sort  Disk: 640kB
->  HashAggregate  (cost=1016.12..1032.11 rows=1599 width=36) (actual time=12.731..16.738 rows=12098 loops=1)
->  Index Only Scan using idx_events_idx on events e_1  (cost=0.42..896.20 rows=15990 width=36) (actual time=0.029..5.084 rows=15966 loops=1)
Index Cond: ((c_id = 5) AND (t >= '2017-01-03 00:00:00'::timestamp without time zone) AND (t < '2017-01-06 00:00:00'::timestamp without time zone))
Heap Fetches: 0
Total runtime: 68.736 ms

Запрос 3:

DROP INDEX idx_events_idx
CREATE INDEX idx_events_flutter ON events (c_id, pr_id, pa_id, t DESC, r)

Запрос 5:

  -- query A + index by flutter
explain analyze SELECT
        pr_id,
        r,
        count(1) AS quantity
    FROM (
        SELECT DISTINCT ON (pr_id, pa_id)
          pr_id,
          pa_id,
          r
        FROM events
        WHERE
          c_id = 5 AND
          t >= '2017-01-03Z00:00:00' AND
          t < '2017-01-06Z00:00:00'
        ORDER BY pr_id, pa_id, t DESC
    ) latest
    GROUP BY
        1,
        2
    ORDER BY 3, 2, 1 DESC

Результаты:

QUERY PLAN
Sort  (cost=2744.82..2745.32 rows=200 width=15) (actual time=20.915..20.916 rows=30 loops=1)
Sort Key: (count(1)), events.r, events.pr_id
Sort Method: quicksort  Memory: 27kB
->  HashAggregate  (cost=2735.18..2737.18 rows=200 width=15) (actual time=20.883..20.892 rows=30 loops=1)
->  Unique  (cost=0.42..2707.20 rows=1599 width=40) (actual time=0.037..16.488 rows=12098 loops=1)
->  Index Only Scan using idx_events_flutter on events  (cost=0.42..2627.25 rows=15990 width=40) (actual time=0.036..10.893 rows=15966 loops=1)
Index Cond: ((c_id = 5) AND (t >= '2017-01-03 00:00:00'::timestamp without time zone) AND (t < '2017-01-06 00:00:00'::timestamp without time zone))
Heap Fetches: 0
Total runtime: 20.964 ms

Ответ 3

Только два разных метода (YMMV):

-- using a window finction to find the record with the most recent t::
EXPLAIN ANALYZE
SELECT pr_id, r, count(1) AS quantity
    FROM (
        SELECT DISTINCT ON (pr_id, pa_id)
          pr_id, pa_id,
                 first_value(r) OVER www AS r
                 -- last_value(r) OVER www AS r
        FROM events
        WHERE c_id = 5
         AND t >= '2017-01-03Z00:00:00'
         AND t < '2017-01-06Z00:00:00'
        WINDOW www AS (PARTITION BY pr_id, pa_id ORDER BY t DESC)

        ORDER BY 1, 2, t DESC
    ) sss
    GROUP BY 1, 2
    ORDER BY 3, 2, 1 DESC
        ;

-- Avoiding the window function; find the MAX via NOT EXISTS() ::

EXPLAIN ANALYZE
SELECT pr_id, r, count(1) AS quantity
    FROM (
        SELECT DISTINCT ON (pr_id, pa_id)
          pr_id, pa_id, r
        FROM events e
        WHERE c_id = 5
         AND t >= '2017-01-03Z00:00:00'
         AND t < '2017-01-06Z00:00:00'
         AND NOT EXISTS ( SELECT * FROM events nx
                WHERE nx.c_id = 5 AND nx.pr_id =e.pr_id AND nx.pa_id =e.pa_id
                AND nx.t >= '2017-01-03Z00:00:00'
                AND nx.t < '2017-01-06Z00:00:00'
                AND nx.t > e.t
                )
    ) sss
    GROUP BY 1, 2
    ORDER BY 3, 2, 1 DESC
        ;

Примечание: DISTINCT ON можно исключить из второго запроса, результаты уже уникальны.

Ответ 4

Я бы попытался использовать стандартную функцию ROW_NUMBER() с соответствующим индексом вместо Postgres-specific DISTINCT ON, чтобы найти "последние" строки.

Индекс

CREATE INDEX ix_events ON events USING btree (c_id, pa_id, pr_id, t DESC, r);

Query

WITH
CTE_RN
AS
(
    SELECT
        pa_id
        ,pr_id
        ,r
        ,ROW_NUMBER() OVER (PARTITION BY c_id, pa_id, pr_id ORDER BY t DESC) AS rn
    FROM events
    WHERE
        c_id = 5
        AND t >= '2017-01-03Z00:00:00'
        AND t < '2017-01-06Z00:00:00'
)
SELECT
    pr_id
    ,r
    ,COUNT(*) AS quantity
FROM CTE_RN
WHERE rn = 1
GROUP BY 
    pr_id
    ,r
ORDER BY quantity, r, pr_id DESC
;

У меня нет Postgres, поэтому я использую http://rextester.com для тестирования. Я установил scale_factor в 30 в генерации данных script, иначе для рекстератора слишком много времени. Я получаю следующий план запроса. Компонент времени должен быть проигнорирован, но вы можете видеть, что промежуточных сорта нет, только сортировка для окончательного ORDER BY. См. http://rextester.com/GUFXY36037

Пожалуйста, попробуйте этот запрос на вашем оборудовании и ваших данных. Было бы интересно посмотреть, как он сравнивается с вашим запросом. Я заметил, что оптимизатор не выбирает этот индекс, если таблица имеет указанный вами индекс. Если вы видите то же самое на своем сервере, попробуйте сбросить или отключить другие индексы, чтобы получить план, который у меня есть.

1   Sort  (cost=158.07..158.08 rows=1 width=44) (actual time=81.445..81.448 rows=30 loops=1)
2     Output: cte_rn.pr_id, cte_rn.r, (count(*))
3     Sort Key: (count(*)), cte_rn.r, cte_rn.pr_id DESC
4     Sort Method: quicksort  Memory: 27kB
5     CTE cte_rn
6       ->  WindowAgg  (cost=0.42..157.78 rows=12 width=88) (actual time=0.204..56.215 rows=15130 loops=1)
7             Output: events.pa_id, events.pr_id, events.r, row_number() OVER (?), events.t, events.c_id
8             ->  Index Only Scan using ix_events3 on public.events  (cost=0.42..157.51 rows=12 width=80) (actual time=0.184..28.688 rows=15130 loops=1)
9                   Output: events.c_id, events.pa_id, events.pr_id, events.t, events.r
10                  Index Cond: ((events.c_id = 5) AND (events.t >= '2017-01-03 00:00:00'::timestamp without time zone) AND (events.t < '2017-01-06 00:00:00'::timestamp without time zone))
11                  Heap Fetches: 15130
12    ->  HashAggregate  (cost=0.28..0.29 rows=1 width=44) (actual time=81.363..81.402 rows=30 loops=1)
13          Output: cte_rn.pr_id, cte_rn.r, count(*)
14          Group Key: cte_rn.pr_id, cte_rn.r
15          ->  CTE Scan on cte_rn  (cost=0.00..0.27 rows=1 width=36) (actual time=0.214..72.841 rows=11491 loops=1)
16                Output: cte_rn.pa_id, cte_rn.pr_id, cte_rn.r, cte_rn.rn
17                Filter: (cte_rn.rn = 1)
18                Rows Removed by Filter: 3639
19  Planning time: 0.452 ms
20  Execution time: 83.234 ms

Есть еще одна оптимизация, которую вы можете сделать, опираясь на внешние знания ваших данных.

Если вы можете гарантировать, что каждая пара pa_id, pr_id имеет значения для каждого, скажем, дня, то вы можете безопасно уменьшить заданный пользователем диапазон t до одного дня.

Это уменьшит количество строк, которые движок считывает и сортирует, если пользователь обычно задает диапазон t дольше 1 дня.

Если вы не можете предоставить такую ​​гарантию в своих данных для всех значений, но вы все еще знаете, что обычно все pa_id, pr_id находятся близко друг к другу (через t), и пользователь обычно предоставляет широкий диапазон для t, вы можете запустить предварительный запрос, чтобы сузить диапазон t для основного запроса.

Что-то вроде этого:

SELECT
    MIN(MaxT) AS StartT
    MAX(MaxT) AS EndT
FROM
    (
        SELECT
            pa_id
            ,pr_id
            ,MAX(t) AS MaxT
        FROM events
        WHERE
            c_id = 5
            AND t >= '2017-01-03Z00:00:00'
            AND t < '2017-01-06Z00:00:00'
        GROUP BY
            pa_id
            ,pr_id
    ) AS T

И затем используйте найденный StartT,EndT в основном запросе, надеясь, что новый диапазон будет намного уже, чем оригинал, определенный пользователем.

Вышеприведенный запрос не должен сортировать строки, поэтому он должен быть быстрым. Основной запрос должен сортировать строки, но сортировки будет меньше, поэтому общее время выполнения может быть лучше.

Ответ 5

Итак, я немного поработал и попытался переместить вашу группу и отдельные данные в свои собственные таблицы, чтобы мы могли использовать несколько табличных индексов. Обратите внимание, что это решение работает только в том случае, если у вас есть контроль над тем, как данные вставляются в базу данных, т.е. Вы можете изменить приложение источника данных. Если нет, то увы, это спорный вопрос.

На практике вместо того, чтобы сразу вставлять в таблицу events, вы должны сначала проверить, существуют ли реляционные date и prpa в соответствующих таблицах. Если нет, создайте их. Затем получите их id и используйте это для оператора insert в таблице events.

Прежде чем начать, я генерировал 10x увеличение производительности на query_c над query_a, и мой окончательный результат для перезаписанного query_a - это производительность в 4 раза. Если это не так хорошо, не стесняйтесь отключать.

Учитывая исходные запросы на выборку данных, которые вы указали в первом экземпляре, я вычислил следующие этапы:

query_a: 5228.518 ms
query_b: 5708.962 ms
query_c: 538.329 ms

Итак, примерно в 10 раз больше производительности, дайте или возьмите.

Я собираюсь изменить данные, сгенерированные в events, и это изменение занимает довольно много времени. Вам не нужно будет делать это на практике, так как ваш INSERT для таблиц будет уже рассмотрен.

Для моей оптимизации первым шагом является создание таблицы с датами, а затем переноса данных и привязки к ней в таблице events, например:

CREATE TABLE dates (
    id SERIAL,
    year_part INTEGER NOT NULL,
    month_part INTEGER NOT NULL,
    day_part INTEGER NOT NULL
);
-- Total runtime: 8.281 ms

INSERT INTO dates(year_part, month_part, day_part) SELECT DISTINCT
    EXTRACT(YEAR FROM t), EXTRACT(MONTH FROM t), EXTRACT(DAY FROM t)
FROM events;
-- Total runtime: 12802.900 ms

CREATE INDEX dates_ymd ON dates USING btree(year_part, month_part, day_part);
-- Total runtime: 13.750 ms

ALTER TABLE events ADD COLUMN date_id INTEGER;
-- Total runtime: 2.468ms

UPDATE events SET date_id = dates.id
FROM dates
WHERE EXTRACT(YEAR FROM t) = dates.year_part
AND EXTRACT(MONTH FROM t) = dates.month_part
AND EXTRACT(DAY FROM T) = dates.day_part
;
-- Total runtime: 388024.520 ms

Затем мы делаем то же самое, но с парой ключей (pr_id, pa_id), что не слишком сильно снижает мощность, но когда мы говорим о больших наборах, это может помочь в использовании памяти и замена и выключение:

CREATE TABLE prpa (
    id SERIAL,
    pr_id TEXT NOT NULL,
    pa_id TEXT NOT NULL
);
-- Total runtime: 5.451 ms

CREATE INDEX events_prpa ON events USING btree(pr_id, pa_id);
-- Total runtime: 218,908.894 ms

INSERT INTO prpa(pr_id, pa_id) SELECT DISTINCT pr_id, pa_id FROM events;
-- Total runtime: 5566.760 ms

CREATE INDEX prpa_idx ON prpa USING btree(pr_id, pa_id);
-- Total runtime: 84185.057 ms

ALTER TABLE events ADD COLUMN prpa_id INTEGER;
-- Total runtime: 2.067 ms

UPDATE events SET prpa_id = prpa.id
FROM prpa
WHERE events.pr_id = prpa.pr_id
AND events.pa_id = prpa.pa_id;
-- Total runtime: 757915.192

DROP INDEX events_prpa;
-- Total runtime: 1041.556 ms

Наконец, позвольте избавиться от старых индексов и ныне несуществующих столбцов, а затем очистите новые таблицы:

DROP INDEX events_idx;
-- Total runtime: 1139.508 ms

ALTER TABLE events
    DROP COLUMN pr_id,
    DROP COLUMN pa_id
;
-- Total runtime: 5.376 ms

VACUUM ANALYSE prpa;
-- Total runtime: 1030.142

VACUUM ANALYSE dates;
-- Total runtime: 6652.151

Итак, теперь мы имеем следующие таблицы:

events (c_id, r, t, prpa_id, date_id)
dates (id, year_part, month_part, day_part)
prpa (id, pr_id, pa_id)

Теперь перейдем к индексу, нажав t DESC до конца, где он принадлежит, что мы можем сделать теперь, потому что мы фильтруем результаты на dates до ORDER ing, что сокращает необходимость в t DESC, чтобы быть столь заметным в индексе:

CREATE INDEX events_idx_new ON events USING btree (c_id, date_id, prpa_id, t DESC);
-- Total runtime: 27697.795
VACUUM ANALYSE events;

Теперь мы переписываем запрос (используя таблицу для хранения промежуточных результатов - я нахожу, что это хорошо работает с большими наборами данных) и awaaaaaay мы идем!

DROP TABLE IF EXISTS temp_results;

SELECT DISTINCT ON (prpa_id)
    prpa_id,
    r
INTO TEMPORARY temp_results
FROM events
INNER JOIN dates
    ON dates.id = events.date_id
WHERE c_id = 5
AND dates.year_part BETWEEN 2017 AND 2017
AND dates.month_part BETWEEN 1 AND 1
AND dates.day_part BETWEEN 3 AND 5
ORDER BY prpa_id, t DESC;

SELECT
    prpa.pr_id,
    r,
    count(1) AS quantity
FROM temp_results
INNER JOIN prpa ON prpa.id = temp_results.prpa_id
GROUP BY
    1,
    2
ORDER BY 3, 2, 1 DESC;
-- Total runtime: 1233.281 ms

Итак, это не 10-кратное увеличение производительности, а 4-х, что все равно.

Это решение представляет собой комбинацию нескольких методов, которые я нашел хорошо работающими с большими наборами данных и диапазонами дат. Даже если это недостаточно для ваших целей, здесь могут быть некоторые драгоценные камни, которые вы можете переделать на протяжении всей своей карьеры.

EDIT:

EXPLAIN ANALYZE в запросе SELECT INTO:

Unique  (cost=171839.95..172360.53 rows=51332 width=16) (actual time=819.385..857.777 rows=117471 loops=1)
  ->  Sort  (cost=171839.95..172100.24 rows=104117 width=16) (actual time=819.382..836.924 rows=155202 loops=1)
        Sort Key: events.prpa_id, events.t
        Sort Method: external sort  Disk: 3944kB
        ->  Hash Join  (cost=14340.24..163162.92 rows=104117 width=16) (actual time=126.929..673.293 rows=155202 loops=1)
              Hash Cond: (events.date_id = dates.id)
              ->  Bitmap Heap Scan on events  (cost=14338.97..160168.28 rows=520585 width=20) (actual time=126.572..575.852 rows=516503 loops=1)
                    Recheck Cond: (c_id = 5)
                    Heap Blocks: exact=29610
                    ->  Bitmap Index Scan on events_idx2  (cost=0.00..14208.82 rows=520585 width=0) (actual time=118.769..118.769 rows=516503 loops=1)
                          Index Cond: (c_id = 5)
              ->  Hash  (cost=1.25..1.25 rows=2 width=4) (actual time=0.326..0.326 rows=3 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 1kB
                    ->  Seq Scan on dates  (cost=0.00..1.25 rows=2 width=4) (actual time=0.320..0.323 rows=3 loops=1)
                          Filter: ((year_part >= 2017) AND (year_part <= 2017) AND (month_part >= 1) AND (month_part <= 1) AND (day_part >= 3) AND (day_part <= 5))
                          Rows Removed by Filter: 7
Planning time: 3.091 ms
Execution time: 913.543 ms

EXPLAIN ANALYZE в запросе SELECT: (Примечание: мне пришлось изменить первый запрос, чтобы выбрать в фактическую таблицу, а не временную таблицу, чтобы получить план запроса для этого. AFAIK EXPLAIN ANALYZE работает только на одном запросе)

Sort  (cost=89590.66..89595.66 rows=2000 width=15) (actual time=1248.535..1248.537 rows=30 loops=1)
  Sort Key: (count(1)), temp_results.r, prpa.pr_id
  Sort Method: quicksort  Memory: 27kB
  ->  HashAggregate  (cost=89461.00..89481.00 rows=2000 width=15) (actual time=1248.460..1248.468 rows=30 loops=1)
        Group Key: prpa.pr_id, temp_results.r
        ->  Hash Join  (cost=73821.20..88626.40 rows=111280 width=15) (actual time=798.861..1213.494 rows=117471 loops=1)
              Hash Cond: (temp_results.prpa_id = prpa.id)
              ->  Seq Scan on temp_results  (cost=0.00..1632.80 rows=111280 width=8) (actual time=0.024..17.401 rows=117471 loops=1)
              ->  Hash  (cost=36958.31..36958.31 rows=2120631 width=15) (actual time=798.484..798.484 rows=2120631 loops=1)
                    Buckets: 16384  Batches: 32  Memory Usage: 3129kB
                    ->  Seq Scan on prpa  (cost=0.00..36958.31 rows=2120631 width=15) (actual time=0.126..350.664 rows=2120631 loops=1)
Planning time: 1.073 ms
Execution time: 1248.660 ms