Плохая оптимизация / планирование запросов на основе окон Postgres (разбиение по (, группирование по?)) - ускорение в 1000 раз
Мы работаем с Postgres 9.3.5. (07/2014) У нас есть довольно сложное хранилище данных / настройка отчетности (ETL, материализованные представления, индексация, агрегирование, аналитические функции, ...).
То, что я обнаружил прямо сейчас, может быть трудно реализовать в оптимизаторе (?), Но это имеет огромное значение для производительности (только пример кода с огромным сходством с нашим запросом для уменьшения ненужной сложности):
create view foo as
select
sum(s.plan) over w_pyl as pyl_plan, -- money planned to spend in this pot/loc/year
sum(s.booked) over w_pyl as pyl_booked, -- money already booked in this pot/loc/year
-- money already booked in this pot/loc the years before (stored as sum already)
last_value(s.booked_prev_years) over w_pl as pl_booked_prev_years,
-- update 2014-10-08: maybe the following additional selected columns
-- may be implementation-/test-relevant since they could potentially be determined
-- by sorting within the partition:
min(s.id) over w_pyl,
max(s.id) over w_pyl,
-- ... anything could follow here ...
x.*,
s.*
from
pot_location_year x -- may be some materialized view or (cache/regular) table
left outer join spendings s
on (s.pot = x.pot and s.loc = x.loc and s.year = x.year)
window
w_pyl as (partition by x.pot, x.year, x.loc)
w_pl as (partition by x.pot, x.loc order by x.year)
У нас есть эти два соответствующих индекса:
pot_location_year_idx__p_y_l -- on pot, year, loc
pot_location_year_idx__p_l_y -- on pot, loc, year
Теперь мы запускаем объяснение для некоторого тестового запроса
explain select * from foo fetch first 100 rows only
Это показывает нам некоторые оченьплохая производительность, потому чтоPyl индекс используется, гденабор результатов должен быть излишне отсортирован дважды :-( (самый дальний от центраWindowAgg/Sort
пошаговые сортакурсировать потому что это необходимо для нашегоlast_value(..) as pl_booked_prev_years
):
Limit (cost=289687.87..289692.12 rows=100 width=512)
-> WindowAgg (cost=289687.87..292714.85 rows=93138 width=408)
-> Sort (cost=289687.87..289920.71 rows=93138 width=408)
Sort Key: x.pot, x.loc, x.year
-> WindowAgg (cost=1.25..282000.68 rows=93138 width=408)
-> Nested Loop Left Join (cost=1.25..278508.01 rows=93138 width=408)
Join Filter: ...
-> Nested Loop Left Join (cost=0.83..214569.60 rows=93138 width=392)
-> Index Scan using pot_location_year_idx__p_y_l on pot_location_year x (cost=0.42..11665.49 rows=93138 width=306)
-> Index Scan using ... (cost=0.41..2.17 rows=1 width=140)
Index Cond: ...
-> Index Scan using ... (cost=0.41..0.67 rows=1 width=126)
Index Cond: ...
Таким образом, очевидная проблема заключается в том, чтоПланировщик должен выбрать существующийкурсировать вместо индекса, чтобы не пришлось сортировать дважды.