Может ли Postgres 12 выполнять устранение секций в процессе выполнения запроса с подзапросами, возвращающими список значений?
Это исправленная и дополненная в соответствиями с комментариями копия вопроса, заданного на англоязычном StackOverflow, там я не получил ясного ответа, может быть кто-то в русскоязычном секторе сможет мне помочь.
Я пытаюсь воспользоваться преимуществом партиционирования в одном случае:
У меня есть таблица "events", которая партиционированна по списку (list) по полю "dt_pk", которое ссылается на таблицу "dates".
-- Schema
drop schema if exists test cascade;
create schema test;
-- Tables
create table if not exists test.dates (
id bigint primary key,
dt date not null
);
create sequence test.seq_events_id;
create table if not exists test.events
(
id bigint not null,
dt_pk bigint not null,
content_int bigint,
foreign key (dt_pk) references test.dates(id) on delete cascade,
primary key (dt_pk, id)
)
partition by list (dt_pk);
-- Partitions
create table test.events_1 partition of test.events for values in (1);
create table test.events_2 partition of test.events for values in (2);
create table test.events_3 partition of test.events for values in (3);
-- Fill tables
insert into test.dates (id, dt)
select id, dt
from (
select 1 id, '2020-01-01'::date as dt
union all
select 2 id, '2020-01-02'::date as dt
union all
select 3 id, '2020-01-03'::date as dt
) t;
do $$
declare
dts record;
begin
for dts in (
select id
from test.dates
) loop
for k in 1..10000 loop
insert into test.events (id, dt_pk, content_int)
values (nextval('test.seq_events_id'), dts.id, random_between(1, 1000000));
end loop;
commit;
end loop;
end;
$$;
vacuum analyze test.dates, test.events;
Я хочу получить данные вот по такому запросу:
select *
from test.events e
join test.dates d on e.dt_pk = d.id
where d.dt between '2020-01-02'::date and '2020-01-03'::date;
Но в этом случае устранение секций не работает. Понятно почему оно не работает, в запросе нет константы на ключ партиционирования. Но из документации я знаю что существует устранение секций в процессе выполнения запроса, которое должно работать со значениями, полученными из подзапросов.
Устранение секций может производиться не только при планировании конкретного запроса, но и в процессе его выполнения. Благодаря этому может быть устранено больше секций, когда условные выражения содержат значения, неизвестные во время планирования, например параметры, определённые оператором PREPARE, значения, получаемые из подзапросов, или параметризованные значения во внутренней стороне соединения с вложенным циклом.
Тогда я переписал свой запрос таким образом и ожидал получить устранение секций:
select *
from test.events e
where e.dt_pk in (
select d.id
from test.dates d
where d.dt between '2020-01-02'::date and '2020-01-03'::date
);
Но explain для этого запроса показывает:
Hash Join (cost=1.07..833.07 rows=20000 width=24) (actual time=3.581..15.989 rows=20000 loops=1)
Hash Cond: (e.dt_pk = d.id)
-> Append (cost=0.00..642.00 rows=30000 width=24) (actual time=0.005..6.361 rows=30000 loops=1)
-> Seq Scan on events_1 e (cost=0.00..164.00 rows=10000 width=24) (actual time=0.005..1.104 rows=10000 loops=1)
-> Seq Scan on events_2 e_1 (cost=0.00..164.00 rows=10000 width=24) (actual time=0.005..1.127 rows=10000 loops=1)
-> Seq Scan on events_3 e_2 (cost=0.00..164.00 rows=10000 width=24) (actual time=0.008..1.097 rows=10000 loops=1)
-> Hash (cost=1.04..1.04 rows=2 width=8) (actual time=0.006..0.006 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on dates d (cost=0.00..1.04 rows=2 width=8) (actual time=0.004..0.004 rows=2 loops=1)
Filter: ((dt >= '2020-01-02'::date) AND (dt <= '2020-01-03'::date))
Rows Removed by Filter: 1
Planning Time: 0.206 ms
Execution Time: 17.237 ms
То есть, мы читаем все партиции. Я даже пробовал заставить планировщик использовать соединение вложенными циклами (nested loop join), потому что я прочитал в документации "параметризованные значения во внутренней стороне соединения с вложенным циклом", но это не работает:
set enable_hashjoin to off;
set enable_mergejoin to off;
set enable_material to off;
И опять:
Nested Loop (cost=0.00..2035.05 rows=20000 width=24) (actual time=3.776..25.389 rows=20000 loops=1)
Join Filter: (e.dt_pk = d.id)
Rows Removed by Join Filter: 40000
-> Seq Scan on dates d (cost=0.00..1.04 rows=2 width=8) (actual time=0.007..0.010 rows=2 loops=1)
Filter: ((dt >= '2020-01-02'::date) AND (dt <= '2020-01-03'::date))
Rows Removed by Filter: 1
-> Append (cost=0.00..642.00 rows=30000 width=24) (actual time=0.003..6.412 rows=30000 loops=2)
-> Seq Scan on events_1 e (cost=0.00..164.00 rows=10000 width=24) (actual time=0.002..1.038 rows=10000 loops=2)
-> Seq Scan on events_2 e_1 (cost=0.00..164.00 rows=10000 width=24) (actual time=0.004..1.094 rows=10000 loops=2)
-> Seq Scan on events_3 e_2 (cost=0.00..164.00 rows=10000 width=24) (actual time=0.007..1.085 rows=10000 loops=2)
Planning Time: 0.188 ms
Execution Time: 26.680 ms
Затем я заметил что во всех примерах "устранения секция во время выполнения" я вижу только условие =, но не in.
И таким образом это действительно работает:
explain (analyze) select * from test.events e where e.dt_pk = (select id from test.dates where id = 2);
Append (cost=1.04..718.04 rows=30000 width=24) (actual time=0.018..3.312 rows=10000 loops=1)
InitPlan 1 (returns $0)
-> Seq Scan on dates (cost=0.00..1.04 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)
Filter: (id = 2)
Rows Removed by Filter: 2
-> Seq Scan on events_1 e (cost=0.00..189.00 rows=10000 width=24) (never executed)
Filter: (dt_pk = $0)
-> Seq Scan on events_2 e_1 (cost=0.00..189.00 rows=10000 width=24) (actual time=0.005..2.206 rows=10000 loops=1)
Filter: (dt_pk = $0)
-> Seq Scan on events_3 e_2 (cost=0.00..189.00 rows=10000 width=24) (never executed)
Filter: (dt_pk = $0)
Planning Time: 0.172 ms
Execution Time: 3.978 ms
И здесь время для главного вопроса, усечение секций во время выполнения работает только с подзапросами, возвращающими одно значение, или есть какой-то способ получить усечение секций с подзапросом, возвращающим список?
И почему это не работает в случае соединения с вложенными циклами? Может быть я что-то неправильно понял в словах:
В частности это могут быть значения из подзапросов и значения параметров времени выполнения, например из параметризованных соединений с вложенными циклами.
Или "параметризованные соединения вложенными циклами" это не обычные соединения вложенными циклами и есть какое-то отличие?