Can PostgreSQL 12 do partition pruning at execution time with subquery returning a list?

Question

I'm trying to take advantages of partitioning in one case:
I have table "events" which partitioned by list by field "dt_pk" which is foreign key to table "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;

I want to run select like this:

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;

But in this case partition pruning doesn't work. It's clear, I don't have constant for partition key. But from documentation I know that there is partition pruning at execution time, which works with value obtained from a subquery:

Partition pruning can be performed not only during the planning of a given query, but also during its execution. This is useful as it can allow more partitions to be pruned when clauses contain expressions whose values are not known at query planning time, for example, parameters defined in a PREPARE statement, using a value obtained from a subquery, or using a parameterized value on the inner side of a nested loop join.

So I rewrite my query like this and I expected partitionin pruning:

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
);

But explain for this select says:

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

So, we read all partitions. I even tried to the planner to use nested loop join, because I read in documentation "parameterized value on the inner side of a nested loop join", but it didn't work:

set enable_hashjoin to off;
set enable_mergejoin to off;

And again:

Nested Loop  (cost=0.00..1443.05 rows=20000 width=24) (actual time=9.160..25.252 rows=20000 loops=1)
  Join Filter: (e.dt_pk = d.id)
  Rows Removed by Join Filter: 30000
  ->  Append  (cost=0.00..642.00 rows=30000 width=24) (actual time=0.008..6.280 rows=30000 loops=1)
        ->  Seq Scan on events_1 e  (cost=0.00..164.00 rows=10000 width=24) (actual time=0.008..1.105 rows=10000 loops=1)
        ->  Seq Scan on events_2 e_1  (cost=0.00..164.00 rows=10000 width=24) (actual time=0.008..1.047 rows=10000 loops=1)
        ->  Seq Scan on events_3 e_2  (cost=0.00..164.00 rows=10000 width=24) (actual time=0.007..1.082 rows=10000 loops=1)
  ->  Materialize  (cost=0.00..1.05 rows=2 width=8) (actual time=0.000..0.000 rows=2 loops=30000)
        ->  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.202 ms
Execution Time: 26.516 ms

Then I noticed that in every example of "partition pruning at execution time" I see only = condition, not in. And it really works that way:

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.014..3.018 rows=10000 loops=1)
  InitPlan 1 (returns $0)
    ->  Seq Scan on dates  (cost=0.00..1.04 rows=1 width=8) (actual time=0.007..0.008 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.004..2.009 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.135 ms
Execution Time: 3.639 ms

And here is my final question: does partition pruning at execution time work only with subquery returning one item, or there is a way to get advantages of partition pruning with subquery returning a list?

And why doesn't it work with nested loop join, did I understand something wrong in words:

This includes values from subqueries and values from execution-time parameters such as those from parameterized nested loop joins.

Or "parameterized nested loop joins" is something different from regular nested loop joins?

Answer 1

There is no partition pruning in your nested loop join because the partitioned table is on the outer side, which is always scanned completely. The inner side is scanned with the join key from the outer side as parameter (hence parameterized scan), so if the partitioned table were on the inner side of the nested loop join, partition pruning could happen.

Partition pruning with IN lists can take place if the list vales are known at plan time:

EXPLAIN (COSTS OFF)
SELECT * FROM test.events WHERE dt_pk IN (1, 2);

                    QUERY PLAN                     
---------------------------------------------------
 Append
   ->  Seq Scan on events_1
         Filter: (dt_pk = ANY ('{1,2}'::bigint[]))
   ->  Seq Scan on events_2
         Filter: (dt_pk = ANY ('{1,2}'::bigint[]))
(5 rows)

But no attempts are made to flatten a subquery, and PostgreSQL doesn't use partition pruning, even if you force the partitioned table to be on the inner side (enable_material = off, enable_hashjoin = off, enable_mergejoin = off):

EXPLAIN (ANALYZE)
SELECT * FROM test.events WHERE dt_pk IN (SELECT 1 UNION SELECT 2);

                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.06..2034.09 rows=20000 width=24) (actual time=0.057..15.523 rows=20000 loops=1)
   Join Filter: (events_1.dt_pk = (1))
   Rows Removed by Join Filter: 40000
   ->  Unique  (cost=0.06..0.07 rows=2 width=4) (actual time=0.026..0.029 rows=2 loops=1)
         ->  Sort  (cost=0.06..0.07 rows=2 width=4) (actual time=0.024..0.025 rows=2 loops=1)
               Sort Key: (1)
               Sort Method: quicksort  Memory: 25kB
               ->  Append  (cost=0.00..0.05 rows=2 width=4) (actual time=0.006..0.009 rows=2 loops=1)
                     ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)
                     ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)
   ->  Append  (cost=0.00..642.00 rows=30000 width=24) (actual time=0.012..4.334 rows=30000 loops=2)
         ->  Seq Scan on events_1  (cost=0.00..164.00 rows=10000 width=24) (actual time=0.011..1.057 rows=10000 loops=2)
         ->  Seq Scan on events_2  (cost=0.00..164.00 rows=10000 width=24) (actual time=0.004..0.641 rows=10000 loops=2)
         ->  Seq Scan on events_3  (cost=0.00..164.00 rows=10000 width=24) (actual time=0.002..0.594 rows=10000 loops=2)
 Planning Time: 0.531 ms
 Execution Time: 16.567 ms
(16 rows)

I am not certain, but it may be because the tables are so small. You might want to try with bigger tables.