Оптимизировать запрос к большой таблице, выполняя generate_series()

Следующий запрос занимает более 7 минут в PostgreSQL 11.1:

SELECT 
    '2019-01-19' as date, 
    '2019-01-19'::date - generate_series(first_observed, last_observed, interval '1 day')::date as days_to_date, 
    ROUND(AVG(price)) as price,
    area_id
FROM 
    table_example
GROUP BY 
    days_to_date, area_id;

table_example содержит около 15 миллионов строк.
Есть ли способы его оптимизировать? Я уже добавил следующие индексы:

CREATE INDEX ON table_example (first_observed, last_observed);
CREATE INDEX ON table_example (area_id);

Это вывод из EXPLAIN (ANALYZE,BUFFERS):

GroupAggregate  (cost=3235559683.68..3377398628.68 rows=1418000 width=72) (actual time=334933.966..440096.869 rows=21688 loops=1)
  Group Key: (('2019-01-19'::date - ((generate_series((first_observed)::timestamp with time zone, (last_observed)::timestamp with time zone, '1 day'::interval)))::date)), area_id
  Buffers: local read=118167 dirtied=118167 written=117143, temp read=1634631 written=1635058
  ->  Sort  (cost=3235559683.68..3271009671.18 rows=14179995000 width=40) (actual time=334923.933..391690.184 rows=380203171 loops=1)
        Sort Key: (('2019-01-19'::date - ((generate_series((first_observed)::timestamp with time zone, (last_observed)::timestamp with time zone, '1 day'::interval)))::date)), area_id
        Sort Method: external merge  Disk: 9187584kB
        Buffers: local read=118167 dirtied=118167 written=117143, temp read=1634631 written=1635058
        ->  Result  (cost=0.00..390387079.39 rows=14179995000 width=40) (actual time=214.798..171717.941 rows=380203171 loops=1)
              Buffers: local read=118167 dirtied=118167 written=117143
              ->  ProjectSet  (cost=0.00..71337191.89 rows=14179995000 width=44) (actual time=214.796..102823.749 rows=380203171 loops=1)
                    Buffers: local read=118167 dirtied=118167 written=117143
                    ->  Seq Scan on table_example  (cost=0.00..259966.95 rows=14179995 width=44) (actual time=0.031..2449.511 rows=14179995 loops=1)
                          Buffers: local read=118167 dirtied=118167 written=117143
Planning Time: 0.409 ms
JIT:
  Functions: 18
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 5.034 ms, Inlining 13.010 ms, Optimization 121.440 ms, Emission 79.996 ms, Total 219.480 ms
Execution Time: 441133.410 ms

Вот как выглядит table_example:

column name        data type
'house_pk'         'integer'    
'date_in'          'date'   
'first_observed'   'date'   
'last_observed'    'date'   
'price'            'numeric'    
'area_id'          'integer'

Существует 60 различных идентификаторов областей.

Запрос выполняется на многоядерной машине (24 ядра) с 128 ГБ памяти. Однако возможно, что настройки не оптимальны.


postgresql sql generate-series postgresql-performance set-returning-functions
person Wessi    schedule 17.01.2019    source источник
comment
Это много данных, а в запросе нет фильтров. Я сомневаюсь, что вы сможете получить много улучшений.   -  person Gordon Linoff    schedule 17.01.2019
comment
Поможет определение таблицы — оператор CREATE TABLE с типами данных и ограничениями. Плюс вывод EXPLAIN (ANALYZE, BUFFERS). См.: stackoverflow.com/tags/postgresql-performance/info Вам нужны результаты за все дни и все области в однажды? У вас есть таблица area с 1 строкой на соответствующий area_id? Сколько различных area_id? Сколько дней между first_observed и last_observed - мин./макс./среднее?   -  person Erwin Brandstetter    schedule 18.01.2019
comment
@ErwinBrandstetter 1) Теперь я добавил информацию о EXPLAIN (ANALYZE, BUFFERS). 2) Мне нужны результаты за все дни сразу. Я мог бы сделать несколько запросов для областей, но это потребовало бы выполнения 60 запросов вместо одного. 3) Я не уверен, что вы имеете в виду. У меня есть таблица, содержащая area_ids с одной строкой на area_id. Это то, что ты имеешь в виду? 4) 60 идентификаторов областей, 5) мин. = 0, макс. = 335, среднее = 25,8   -  person Wessi    schedule 18.01.2019
comment
Полный оператор CREATE TABLE — это способ раскрытия информации о вашей таблице. Пример: stackoverflow.com/questions/9789736/ Различные клиенты (включая pgAdmin3 и pgAdmin4) отображают обратно спроектированный оператор.   -  person Erwin Brandstetter    schedule 18.01.2019

Ответы (1)


arrow_upward
1
arrow_downward

При обработке всей таблицы индексы обычно бесполезны (за возможным исключением сканирования только индекса, если строки таблицы намного шире индекса).

И при обработке всей таблицы я не вижу большого пространства для оптимизации производительности самого запроса. Одна незначительная вещь:

SELECT d.the_date
     , generate_series(d.the_date - last_observed
                     , d.the_date - first_observed) AS days_to_date
     , round(avg(price)) AS price
     , area_id
FROM   table_example
     , (SELECT date '2019-01-19') AS d(the_date)
GROUP  BY days_to_date, area_id;

Предположим, что first_observed и last_observed равны date NOT NULL и всегда < date '2019-01-19'. В противном случае вам нужно бросить / сделать больше.

Таким образом, у вас есть только два вычитания, а затем generate_series() работает с целыми числами (быстрее всего).

Добавлен мини-подзапрос только для удобства, чтобы указать дату только один раз. В подготовленном операторе или функции вы можете использовать параметр и не нуждаться в этом:

     , (SELECT date '2019-01-19') AS d(the_date)

Кроме этого, если EXPLAIN (ANALYZE, BUFFERS) упоминает "Диск" (пример: Sort Method: external merge Disk: 3240kB), то (временно) более высокое значение для work_mem должно помочь. Видеть:

Если вы не можете позволить себе больше оперативной памяти, а этапы агрегирования и/или сортировки по-прежнему передаются на диск, может помочь разделяй и властвуй с помощью запроса, например, с использованием соединения LATERAL:

SELECT d.the_date, f.*, a.area_id
FROM   area a
     , (SELECT date '2019-01-19') AS d(the_date)
     , LATERAL (
   SELECT generate_series(d.the_date - last_observed
                        , d.the_date - first_observed) AS days_to_date
        , round(avg(price)) AS price
   FROM   table_example
   WHERE  area_id = a.area_id
   GROUP  BY 1
   ) f;

Предполагая таблицу area, очевидно.

person Erwin Brandstetter    schedule 18.01.2019
comment
Удивительная разница. От 6 минут до 8 секунд. Для другого чтения этого ответа обратите внимание, что результаты двух способов использования generate_series не идентичны. Быстрый способ создать строку только там, где есть изменение цены. Для моего варианта использования это на самом деле лучше, но могут быть случаи, когда вам нужна строка для каждого дня подряд. - person Wessi; 18.01.2019
comment
При втором взгляде я понимаю, что first_observed и last_observed нужно поменять местами в моих двух запросах, если ожидается, что last_observed будет позднее, чем first_observed. Поэтому я смущен, почему это работает для вас. Здесь 3 запросов (2 в моем ответе, 1 в вопросе), какой занимает 6 минут, а какой 8 секунд? (Вижу в вашем вопросе 7,4 мин.) И: where there is a change in price - что это значит? - person Erwin Brandstetter; 18.01.2019
comment
Ты прав. Это сработало в одном конкретном пограничном случае, но в целом не работает. Их нужно перевернуть, и большая часть прироста производительности исчезнет. Мои комментарии не касались запроса с использованием LATERAL. - person Wessi; 20.01.2019
comment
Так что скорее от 7,4 до 4,9 минут, что по-прежнему является большим преимуществом. - person Wessi; 20.01.2019
comment
@Wessi: я перевернул даты, чтобы исправить запрос соответствующим образом. И да, все еще удивительный выигрыш для небольшой магии оптимизации. Чтобы сделать это быстрее, обратите внимание на то, что я написал о work_mem - может не сильно измениться (в зависимости от вашего оборудования), но обычно значительно быстрее, чтобы избежать сброса на диск. - person Erwin Brandstetter; 21.01.2019
comment
да, это довольно удивительно. Большое спасибо. Я попытаюсь посмотреть, может ли он быть еще быстрее с более высоким work_mem. - person Wessi; 21.01.2019