Re: Explain query on table with partition tables

"Pazargic Antonel Ernest" <antonel(dot)pazargic(at)gmail(dot)com> writes:
> I've made a master table named master and twelve partitions tables. I've
> made all constraint on column "timpul" in partition tables and all
> neccesary rules on insert operation. I've comment out constraint_exclusion
> and put true for that variable into postgresql.conf. I've restarted server.

> I run EXPLAIN PLAN for SELECT * FROM MASTER WHERE timpul = '2006-01-01'
> and doesn't look that scan only one corect partition table (as I read from
> docs). It looks like:

It works for me (tiny example attached). You might want to try "show
constraint_exclusion" just to verify you turned it on correctly.

regards, tom lane

regression=# create table master (timpul timestamp);
CREATE TABLE
regression=# create table t1 (check (timpul >= '2005-01-01' and timpul < '2006-01-01')) inherits (master);
CREATE TABLE
regression=# create table t2 (check (timpul >= '2006-01-01' and timpul < '2007-01-01')) inherits (master);
CREATE TABLE
regression=# explain select * from master where timpul = '2005-10-01';
QUERY PLAN
-------------------------------------------------------------------------------------
Result (cost=0.00..102.75 rows=30 width=8)
-> Append (cost=0.00..102.75 rows=30 width=8)
-> Seq Scan on master (cost=0.00..34.25 rows=10 width=8)
Filter: (timpul = '2005-10-01 00:00:00'::timestamp without time zone)
-> Seq Scan on t1 master (cost=0.00..34.25 rows=10 width=8)
Filter: (timpul = '2005-10-01 00:00:00'::timestamp without time zone)
-> Seq Scan on t2 master (cost=0.00..34.25 rows=10 width=8)
Filter: (timpul = '2005-10-01 00:00:00'::timestamp without time zone)
(8 rows)

regression=# set constraint_exclusion to 1;
SET
regression=# explain select * from master where timpul = '2005-10-01';
QUERY PLAN
-------------------------------------------------------------------------------------
Result (cost=0.00..68.50 rows=20 width=8)
-> Append (cost=0.00..68.50 rows=20 width=8)
-> Seq Scan on master (cost=0.00..34.25 rows=10 width=8)
Filter: (timpul = '2005-10-01 00:00:00'::timestamp without time zone)
-> Seq Scan on t1 master (cost=0.00..34.25 rows=10 width=8)
Filter: (timpul = '2005-10-01 00:00:00'::timestamp without time zone)
(6 rows)

regression=#