Postgres的约束排除参数化的，准备好的查询

Question

在Postgres 9.2时，现在可以对使用参数化值的约束执行约束排除(请参阅5.9.6警告(。

但是，我的猜测是，这不适用于具有参数化约束的准备好的语句，因为在准备时间进行查询计划。还是做了一些聪明的事情来完成这项工作？

任何人都可以确认哪种方式？

Answer 1

准备好的语句不会在此处更改计划，请查看示例：

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)