but see below when the predicate is explicitly applied to the main table with partition.
postgres=# \d+ prt1
Partitioned table "public.prt1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | not null | | plain | |
b | integer | | | | plain | |
c | character varying | | | | extended | |
Partition key: RANGE (a)
Partitions: prt1_p1 FOR VALUES FROM (0) TO (250),
prt1_p2 FOR VALUES FROM (250) TO (500),
prt1_p3 FOR VALUES FROM (500) TO (600)
(failed reverse-i-search)`': ^C
postgres=# \d+ b
Table "public.b"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
id | integer | | not null | | plain | |
Indexes:
"b_id_idx" btree (id)
Access method: heap
postgres=# table b;
id
-----
200
400
(2 rows)
-- basically if the table is joined and predicate can be applied to the outer table which has constraints matching,
partition pruning takes place.
I do not know the theory, or even what i did is correct, but just FYI.
postgres=# explain analyze select prt1.* from prt1 where a in ( select id from b where id in (1, 100, 200) );
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=1.05..9.36 rows=2 width=13) (actual time=0.034..0.074 rows=1 loops=1)
Hash Cond: (prt1.a =
b.id)
-> Append (cost=0.00..7.50 rows=300 width=13) (actual time=0.006..0.043 rows=300 loops=1)
-> Seq Scan on prt1_p1 prt1_1 (cost=0.00..2.25 rows=125 width=13) (actual time=0.005..0.013 rows=125 loops=1)
-> Seq Scan on prt1_p2 prt1_2 (cost=0.00..2.25 rows=125 width=13) (actual time=0.003..0.009 rows=125 loops=1)
-> Seq Scan on prt1_p3 prt1_3 (cost=0.00..1.50 rows=50 width=13) (actual time=0.002..0.004 rows=50 loops=1)
-> Hash (cost=1.03..1.03 rows=2 width=4) (actual time=0.005..0.005 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on b (cost=0.00..1.03 rows=2 width=4) (actual time=0.003..0.003 rows=1 loops=1)
Filter: (id = ANY ('{1,100,200}'::integer[]))
Rows Removed by Filter: 1
Planning Time: 0.181 ms
Execution Time: 0.089 ms
(13 rows)
postgres=# explain analyze select prt1.* from prt1 where a in ( select id from b where
b.id = prt1.a) and a in (1, 100, 200);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using iprt1_p1_a on prt1_p1 prt1 (cost=0.14..14.03 rows=2 width=13) (actual time=0.024..0.025 rows=1 loops=1)
Index Cond: (a = ANY ('{1,100,200}'::integer[]))
Filter: (SubPlan 1)
Rows Removed by Filter: 1
SubPlan 1
-> Seq Scan on b (cost=0.00..1.02 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=2)
Filter: (id = prt1.a)
Rows Removed by Filter: 1
Planning Time: 0.120 ms
Execution Time: 0.041 ms
(10 rows)
postgres=# explain analyze select prt1.* from prt1 where exists ( select 1 from b where
b.id = prt1.a) and a in (1, 100, 200);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=1.04..3.79 rows=2 width=13) (actual time=0.024..0.028 rows=1 loops=1)
Hash Cond: (prt1.a =
b.id)
-> Seq Scan on prt1_p1 prt1 (cost=0.00..2.72 rows=3 width=13) (actual time=0.011..0.017 rows=2 loops=1)
Filter: (a = ANY ('{1,100,200}'::integer[]))
Rows Removed by Filter: 123
-> Hash (cost=1.02..1.02 rows=2 width=4) (actual time=0.004..0.004 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on b (cost=0.00..1.02 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=1)
Planning Time: 0.192 ms
Execution Time: 0.043 ms
(10 rows)
postgres=# explain analyze select prt1.* from prt1 inner join b on prt1.a =
b.id where a in (1, 100, 200);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.04..3.79 rows=2 width=13) (actual time=0.024..0.028 rows=1 loops=1)
Hash Cond: (prt1.a =
b.id)
-> Seq Scan on prt1_p1 prt1 (cost=0.00..2.72 rows=3 width=13) (actual time=0.012..0.018 rows=2 loops=1)
Filter: (a = ANY ('{1,100,200}'::integer[]))
Rows Removed by Filter: 123
-> Hash (cost=1.02..1.02 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on b (cost=0.00..1.02 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=1)
Planning Time: 0.181 ms
Execution Time: 0.043 ms
(10 rows)
