Избежание сортировки внешнего диска для совокупного запроса

Question

У нас есть таблица, которая содержит необработанные аналитические данные (например, Google Analytics и аналогичные) для просмотра наших видео. Он содержит числа, такие как необработанные просмотры, загрузки, загрузки и т. Д. Каждое видео идентифицируется по video_id.

Данные записываются за день, но поскольку нам нужно извлекать несколько метрик, каждый день может содержать несколько записей для определенного video_id. Пример:

date       | video_id | country | source   | downloads | etc...
----------------------------------------------------------------
2014-01-02 |        1 |      us | facebook |        10 |
2014-01-02 |        1 |      dk | facebook |        13 |
2014-01-02 |        1 |      dk | admin    |        20 |

У меня есть запрос, в котором мне нужно получить сводные данные для всех видео, в которых есть новые данные после определенной даты. Чтобы получить идентификаторы видео, я делаю этот запрос: SELECT video_id FROM table WHERE date >= '2014-01-01' GROUP BY photo_id (в качестве альтернативы я мог бы сделать DISTINCT(video_id) без GROUP BY, производительность идентична).

Когда у меня есть эти идентификаторы, мне нужны общие совокупные данные (за все время). В совокупности это превращается в следующий запрос:

SELECT
    video_id,
    SUM(downloads),
    SUM(loads),
    <more SUMs),
FROM
    table
WHERE
    video_id IN (SELECT video_id FROM table WHERE date >= '2014-01-01' GROUP BY video_id)
GROUP BY
    video_id

Всего около 10 столбцов (5-10 в зависимости от запроса). EXPLAIN ANALYZE дает следующее:

GroupAggregate  (cost=2370840.59..2475948.90 rows=42537 width=72) (actual time=153790.362..162668.962 rows=87661 loops=1)
  ->  Sort  (cost=2370840.59..2378295.16 rows=2981826 width=72) (actual time=153790.329..155833.770 rows=3285001 loops=1)
        Sort Key: table.video_id
        Sort Method: external merge  Disk: 263528kB
        ->  Hash Join  (cost=57066.94..1683266.53 rows=2981826 width=72) (actual time=740.210..143814.921 rows=3285001 loops=1)
              Hash Cond: (table.video_id = table.video_id)
              ->  Seq Scan on table  (cost=0.00..1550549.52 rows=5963652 width=72) (actual time=1.768..47613.953 rows=5963652 loops=1)
              ->  Hash  (cost=56924.17..56924.17 rows=11422 width=8) (actual time=734.881..734.881 rows=87661 loops=1)
                    Buckets: 2048  Batches: 4 (originally 1)  Memory Usage: 1025kB
                    ->  HashAggregate  (cost=56695.73..56809.95 rows=11422 width=8) (actual time=693.769..715.665 rows=87661 loops=1)
                          ->  Index Only Scan using table_recent_ids on table  (cost=0.00..52692.41 rows=1601328 width=8) (actual time=1.279..314.249 rows=1614339 loops=1)
                                Index Cond: (date >= '2014-01-01'::date)
                                Heap Fetches: 0
Total runtime: 162693.367 ms

Как видите, он использует (довольно большую) сортировку слиянием с внешним диском и занимает много времени. Я не уверен в том, почему сортировки инициируются в первую очередь, и я ищу способ избежать этого или, по крайней мере, минимизировать его. Я знаю возрастающую work_mem может облегчить слияние внешних дисков, но в этом случае оно кажется чрезмерным, и наличие work_mem выше 500 МБ кажется плохой идеей.

Таблица имеет два (соответствующих) индекса: один на video_id один и другой на (date, video_id),

РЕДАКТИРОВАТЬ: обновленный запрос после запуска ANALYZE table,

Answer 1

Отредактировано, чтобы соответствовать пересмотренному плану запроса.

Вы получаете сортировку, потому что Postgres нужно отсортировать строки результатов, чтобы сгруппировать их.

Этот запрос выглядит так, как будто бы он действительно выиграл от индекса table(video_id, date)или даже просто указатель на table(video_id), Наличие такого индекса, скорее всего, позволит избежать необходимости сортировки.

Отредактировано (#2), чтобы предложить

Вы могли бы также рассмотреть тестирование альтернативного запроса, такого как этот:

SELECT
    video_id,
    MAX(date) as latest_date,
    <SUMs>
FROM
    table
GROUP BY
    video_id
HAVING
    latest_date >= '2014-01-01'

Это позволяет избежать какого-либо соединения или подзапроса, и учитывая индекс table(video_id [, other columns]) можно надеяться, что такого рода также удастся избежать. Он будет вычислять суммы по всей базовой таблице перед тем, как отфильтровать ненужные группы, но эта операция - O (n), тогда как сортировка - O (m log m). Таким образом, если критерий даты не очень избирателен, проверка его по факту может быть улучшением.