PostgreSQL constraint exclusion not working with subquery SELECT IN

Question

When using partitioning in PostgreSQL, where master is the master partition table

CREATE TABLE master
(
  _id numeric,
  name character varying
);

and having two 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 a table for some ids (1,3) in this example

CREATE TABLE _ids
(
_id numeric NOT NULL,
CONSTRAINT ids_pkey PRIMARY KEY (_id)
)

the statement

EXPLAIN SELECT * FROM master WHERE _id IN (SELECT * FROM _ids)

yields a seq scan of both partitions regardless whether _ids contains elements from 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 I instead use something like

SELECT * FROM master WHERE _id IN (1,3) 

I get the 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 = ANY ('{1,3}'::numeric[]))
  ->  Bitmap Heap Scan on partition_1  (cost=8.59..17.40 rows=4 width=13)
        Recheck Cond: (_id = ANY ('{1,3}'::numeric[]))
        ->  Bitmap Index Scan on partition_1_id_idx  (cost=0.00..8.58 rows=4 width=0)
              Index Cond: (_id = ANY ('{1,3}'::numeric[]))

How do I get PostgreSQL to use constraint exclusion correctly ?

Note: I have constraint_exclusion set to partition

Answer 1

The partitions are chosen when Postgres compiles that query. When you use constants, then the engine knows where the data lies. When you use a subquery, then Postgres doesn't know.

Hence, using the subquery prevents the engine from taking advantage of the partitions.