CODEWITHSUNDEEP
postgresqlpartitioning
John Christofolakos
John Christofolakos

Reputation: 66

Postgres constraint exclusion for parameterised, prepared query

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?

Upvotes: 1

Views: 258

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51629

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)

Upvotes: 1

Related Questions