Можно ли ответить на запросы в представлении, прежде чем полностью материализовать представление?
Короче говоря: Distinct,Min,Max на левой стороне левого соединения должны быть подотчетны без объединения.
Я использую тип массива SQL (в Postgres 9.3) для сжатия нескольких строк данных в одну строку, а затем представление для возврата неопубликованного нормализованного представления. Я делаю это, чтобы сэкономить на индексных затратах, а также заставить Postgres сжимать данные в массиве.
Вещи работают довольно хорошо, но некоторые запросы, на которые можно было бы ответить, не отменяя и не материализуя / не разбирая представление, довольно дороги, потому что они откладываются до тех пор, пока представление не материализуется. Есть ли способ решить это?
Вот основная таблица:
CREATE TABLE mt_count_by_day
(
run_id integer NOT NULL,
type character varying(64) NOT NULL,
start_day date NOT NULL,
end_day date NOT NULL,
counts bigint[] NOT NULL,
CONSTRAINT mt_count_by_day_pkey PRIMARY KEY (run_id, type),
)
Индекс 'type' только для хорошей меры:
CREATE INDEX runinfo_mt_count_by_day_type_idx on runinfo.mt_count_by_day (type);
Вот представление, которое использует generate_series и unnest
CREATE OR REPLACE VIEW runinfo.v_mt_count_by_day AS
SELECT mt_count_by_day.run_id,
mt_count_by_day.type,
mt_count_by_day.brand,
generate_series(mt_count_by_day.start_day::timestamp without time zone, mt_count_by_day.end_day - '1 day'::interval, '1 day'::interval) AS row_date,
unnest(mt_count_by_day.counts) AS row_count
FROM runinfo.mt_count_by_day;
Что делать, если я хочу сделать разные в столбце "тип"?
explain analyze select distinct(type) from mt_count_by_day;
"HashAggregate (cost=9566.81..9577.28 rows=1047 width=19) (actual time=171.653..172.019 rows=1221 loops=1)"
" -> Seq Scan on mt_count_by_day (cost=0.00..9318.25 rows=99425 width=19) (actual time=0.089..99.110 rows=99425 loops=1)"
"Total runtime: 172.338 ms"
Что произойдет, если я сделаю то же самое на виде?
explain analyze select distinct(type) from v_mt_count_by_day;
"HashAggregate (cost=1749752.88..1749763.34 rows=1047 width=19) (actual time=58586.934..58587.191 rows=1221 loops=1)"
" -> Subquery Scan on v_mt_count_by_day (cost=0.00..1501190.38 rows=99425000 width=19) (actual time=0.114..37134.349 rows=68299959 loops=1)"
" -> Seq Scan on mt_count_by_day (cost=0.00..506940.38 rows=99425000 width=597) (actual time=0.113..24907.147 rows=68299959 loops=1)"
"Total runtime: 58587.474 ms"
Есть ли способ заставить postgres признать, что он может решить эту проблему, не взорвав представление?
Здесь мы можем видеть для сравнения, что мы подсчитываем количество строк, соответствующих критериям в таблице и представлении. Все работает как положено. Postgres фильтрует строки перед тем, как материализовать представление. Не совсем то же самое, но это свойство делает наши данные более управляемыми.
explain analyze select count(*) from mt_count_by_day where type = ’SOCIAL_GOOGLE'
"Aggregate (cost=157.01..157.02 rows=1 width=0) (actual time=0.538..0.538 rows=1 loops=1)"
" -> Bitmap Heap Scan on mt_count_by_day (cost=4.73..156.91 rows=40 width=0) (actual time=0.139..0.509 rows=122 loops=1)"
" Recheck Cond: ((type)::text = 'SOCIAL_GOOGLE'::text)"
" -> Bitmap Index Scan on runinfo_mt_count_by_day_type_idx (cost=0.00..4.72 rows=40 width=0) (actual time=0.098..0.098 rows=122 loops=1)"
" Index Cond: ((type)::text = 'SOCIAL_GOOGLE'::text)"
"Total runtime: 0.625 ms"
explain analyze select count(*) from v_mt_count_by_day where type = 'SOCIAL_GOOGLE'
"Aggregate (cost=857.11..857.12 rows=1 width=0) (actual time=6.827..6.827 rows=1 loops=1)"
" -> Bitmap Heap Scan on mt_count_by_day (cost=4.73..357.11 rows=40000 width=597) (actual time=0.124..5.294 rows=15916 loops=1)"
" Recheck Cond: ((type)::text = 'SOCIAL_GOOGLE'::text)"
" -> Bitmap Index Scan on runinfo_mt_count_by_day_type_idx (cost=0.00..4.72 rows=40 width=0) (actual time=0.082..0.082 rows=122 loops=1)"
" Index Cond: ((type)::text = 'SOCIAL_GOOGLE'::text)"
"Total runtime: 6.885 ms"
Вот код, необходимый для воспроизведения этого:
CREATE TABLE base_table
(
run_id integer NOT NULL,
type integer NOT NULL,
start_day date NOT NULL,
end_day date NOT NULL,
counts bigint[] NOT NULL
CONSTRAINT match_check CHECK (end_day > start_day AND (end_day - start_day) = array_length(counts, 1)),
CONSTRAINT base_table_pkey PRIMARY KEY (run_id, type)
);
--Just because...
CREATE INDEX base_type_idx on base_table (type);
CREATE OR REPLACE VIEW v_foo AS
SELECT m.run_id,
m.type,
t.row_date::date,
t.row_count
FROM base_table m
LEFT JOIN LATERAL ROWS FROM (
unnest(m.counts),
generate_series(m.start_day, m.end_day-1, interval '1d')
) t(row_count, row_date) ON true;
insert into base_table
select a.run_id, a.type, '20120101'::date as start_day, '20120401'::date as end_day, b.counts from (SELECT N AS run_id, L as type
FROM
generate_series(1, 10000) N
CROSS JOIN
generate_series(1, 7) L
ORDER BY N, L) a, (SELECT array_agg(generate_series)::bigint[] as counts FROM generate_series(1, 91) ) b
И результаты на 9.4.1:
объяснить анализ выбрать отдельный тип из base_table;
"HashAggregate (cost=6750.00..6750.03 rows=3 width=4) (actual time=51.939..51.940 rows=3 loops=1)"
" Group Key: type"
" -> Seq Scan on base_table (cost=0.00..6600.00 rows=60000 width=4) (actual time=0.030..33.655 rows=60000 loops=1)"
"Planning time: 0.086 ms"
"Execution time: 51.975 ms"
объяснить анализ выбрать отдельный тип из v_foo;
"HashAggregate (cost=1356600.01..1356600.04 rows=3 width=4) (actual time=9215.630..9215.630 rows=3 loops=1)"
" Group Key: m.type"
" -> Nested Loop Left Join (cost=0.01..1206600.01 rows=60000000 width=4) (actual time=0.112..7834.094 rows=5460000 loops=1)"
" -> Seq Scan on base_table m (cost=0.00..6600.00 rows=60000 width=764) (actual time=0.009..42.694 rows=60000 loops=1)"
" -> Function Scan on t (cost=0.01..10.01 rows=1000 width=0) (actual time=0.091..0.111 rows=91 loops=60000)"
"Planning time: 0.132 ms"
"Execution time: 9215.686 ms"
1 ответ
Обычно планировщик запросов Postgres делает "встроенные" представления для оптимизации всего запроса. По документации:
Одно из применений системы переписывания заключается в реализации представлений. Всякий раз, когда делается запрос к представлению (то есть к виртуальной таблице), система перезаписи переписывает запрос пользователя в запрос, который вместо этого обращается к базовым таблицам, указанным в определении представления.
Но я не думаю, что Postgres достаточно умен, чтобы прийти к выводу, что он может достичь того же результата из базовой таблицы, не разбирая строки.
Вы можете попробовать этот альтернативный запрос с LATERAL присоединиться. Это чище:
CREATE OR REPLACE VIEW runinfo.v_mt_count_by_day AS
SELECT m.run_id, m.type, m.brand
, m.start_day + c.rn - 1 AS row_date
, c.row_count
FROM runinfo.mt_count_by_day m
LEFT JOIN LATERAL unnest(m.counts) WITH ORDINALITY c(row_count, rn) ON true;
Это также дает понять, что один из (end_day, start_day) является излишним.
С помощью LEFT JOIN потому что это может позволить планировщику запросов игнорировать соединение из вашего запроса:
SELECT DISTINCT type FROM v_mt_count_by_day;
Остальное (с CROSS JOIN или же INNER JOIN) он должен оценить объединение, чтобы увидеть, удаляются ли строки из первой таблицы.
Кстати, это:
SELECT DISTINCT type ...
не:
SELECT DISTINCT(type) ...Обратите внимание, что это возвращает date вместо отметки времени в вашем оригинале. Легче, и я думаю, это то, что ты хочешь в любом случае?
Требуется Postgres 9.3+ Подробности:
ROWS FROM в Postgres 9,4+
Чтобы безопасно взорвать обе колонки параллельно:
CREATE OR REPLACE VIEW runinfo.v_mt_count_by_day AS
SELECT m.run_id, m.type, m.brand
t.row_date::date, t.row_count
FROM runinfo.mt_count_by_day m
LEFT JOIN LATERAL ROWS FROM (
unnest(m.counts)
, generate_series(m.start_day, m.end_day, interval '1d')
) t(row_count, row_date) ON true;
Основное преимущество: это не приведет к отклонению в декартово произведение, если два SRF не вернут одинаковое количество строк. Вместо этого значения NULL будут дополнены.
Опять же, я не могу сказать, поможет ли это планировщику запросов с более быстрым планом для DISTINCT type без тестирования.