Postgres constraint exclusion for parameterised, prepared query

Question

As of Postgres 9.2, constraint exclusion can now be performed on constraints that use parameterised values (see 5.9.6 Caveats).

However my guess is that this would not apply to a prepared statement with a parameterized constraint, as query planning is done at PREPARE time. Or has something clever been done to make this work?

Can anyone confirm either way?

score 1 · Accepted Answer · answered Apr 27 '17 at 21:29

prepared statement does not change planning here, look at example:

so=> create table so81(i int);
CREATE TABLE
so=> create table so82(check (i > 2)) inherits (so81);
CREATE TABLE
so=> create table so83(check (i > 0 and i<=2)) inherits (so81);
CREATE TABLE

so=> insert into so82 select 3;
INSERT 0 1
so=> select * from only so81;
 i
---
(0 rows)

so=> select * from so81;
 i
---
 3
(1 row)

so=> SET constraint_exclusion = on;
SET
so=> prepare so83 (int) as select 1 from so81 where i = $1;
ERROR:  prepared statement "so83" already exists
so=> prepare so84 (int) as select 1 from so81 where i = $1;
PREPARE
so=> explain analyze execute so83(3);
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..42.02 rows=14 width=4) (actual time=0.037..0.039 rows=1 loops=1)
   ->  Append  (cost=0.00..41.88 rows=14 width=0) (actual time=0.037..0.039 rows=1 loops=1)
         ->  Seq Scan on so81  (cost=0.00..0.00 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1)
               Filter: (i = 3)
         ->  Seq Scan on so82  (cost=0.00..41.88 rows=13 width=0) (actual time=0.030..0.031 rows=1 loops=1)
               Filter: (i = 3)
 Execution time: 0.061 ms
(7 rows)

so=> SET constraint_exclusion = off;
SET
so=> explain analyze execute so83(3);
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..84.02 rows=27 width=4) (actual time=0.030..0.033 rows=1 loops=1)
   ->  Append  (cost=0.00..83.75 rows=27 width=0) (actual time=0.029..0.032 rows=1 loops=1)
         ->  Seq Scan on so81  (cost=0.00..0.00 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)
               Filter: (i = 3)
         ->  Seq Scan on so82  (cost=0.00..41.88 rows=13 width=0) (actual time=0.025..0.026 rows=1 loops=1)
               Filter: (i = 3)
         ->  Seq Scan on so83  (cost=0.00..41.88 rows=13 width=0) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (i = 3)
 Execution time: 0.126 ms
(9 rows)

Awesome answer ... thanks. Wasn't aware you could use explain that way. — John Christofolakos, Apr 27 '17 at 21:38

Postgres constraint exclusion for parameterised, prepared query

1 Answers1