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

score 4 · Accepted Answer · edited Jan 05 '22 at 06:18

4

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.

edited Jan 05 '22 at 06:18

Nimantha

6,405
6
28
69

answered Aug 08 '16 at 11:05

Gordon Linoff

1,242,037
58
646
786

AFAIK, this is perfectly fine as an answer. Even the docs doesn't clarify this more, aside from the note about the query planner: *With constraint exclusion enabled, the planner will examine the constraints of each partition and try to prove that the partition need not be scanned because it could not contain any rows meeting the query's WHERE clause. When the planner can prove this, it excludes the partition from the query plan.* (which is basically what you wrote: it cannot prove anything if that depends on other columns/sub-queries/etc.). – pozs Aug 08 '16 at 11:30
Any Idea how to perform a better query to make use of the constraint? Maybe some form of a nested query? – baumjamin Aug 08 '16 at 12:31

PostgreSQL constraint exclusion not working with subquery SELECT IN

1 Answers1