sql - PostgreSQL constraint exclusion not working with subquery SELECT IN -

when using partitioning in postgresql, master master partition table

create table master (   _id numeric,   name character varying ); 

and having 2 sub tables

partition_1

create table partition_1 (   -- _id numeric,   -- name character varying,   constraint partition_1_check check (_id < 1000) )     inherits (master); create index partition_1_id_idx   on partition_1   using btree   (_id); 

partition_2

create table partition_2 (   -- _id numeric,   -- name character varying,   constraint partition_2_check check (_id >= 1000) )     inherits (master); create index partition_2_id_idx   on partition_2   using btree   (_id); 

and table ids (1,3) in example

create table _ids ( _id numeric not null, constraint ids_pkey primary key (_id) ) 

the statement

explain select * master _id in (select * _ids) 

yields seq scan of both partitions regardless whether _ids contains elements partition_1/2 or not.

hash semi join  (cost=39.48..141.14 rows=2621 width=14)   hash cond: (master._id = _ids._id)   ->  append  (cost=0.00..62.00 rows=4001 width=14)         ->  seq scan on master  (cost=0.00..0.00 rows=1 width=14)         ->  seq scan on partition_1  (cost=0.00..30.98 rows=1998 width=13)         ->  seq scan on partition_2  (cost=0.00..31.02 rows=2002 width=15)   ->  hash  (cost=23.10..23.10 rows=1310 width=32)         ->  seq scan on _ids  (cost=0.00..23.10 rows=1310 width=32) 

if instead use like

select * master _id in (1,3)  

i desired result:

append  (cost=0.00..17.40 rows=5 width=13)   ->  seq scan on master  (cost=0.00..0.00 rows=1 width=14)         filter: (_id = ('{1,3}'::numeric[]))   ->  bitmap heap scan on partition_1  (cost=8.59..17.40 rows=4 width=13)         recheck cond: (_id = ('{1,3}'::numeric[]))         ->  bitmap index scan on partition_1_id_idx  (cost=0.00..8.58 rows=4 width=0)               index cond: (_id = ('{1,3}'::numeric[])) 

how postgresql use constraint exclusion correctly ?

note: have constraint_exclusion set partition

this long comment.

the partitions chosen when postgres compiles query. when use constants, engine knows data lies. when use subquery, postgres doesn't know.

hence, using subquery prevents engine taking advantage of partitions.