Partitioning table - explain said that all partition tables are scanned - Mailing list pgsql-novice

Hello,

I've made one table named master and for it I've made 12 partition tables
for each month of current year. Also I've made 12 rules for insert
operations into master table. All it's okie till explain select on table
master with where condition on check constrainted column. I've made SET
constraint_exclusion=on. What did I do wrong?

DDL-s
Master table:
create table master(masterid integer default nextval('seq_master'), nume
varchar(25) not null, timpul timestamp not null, constraint pk_master
primary key (masterid), constraint uq1_master unique (nume));

Partition tables:
create table master_012006(constraint ck1_master012006 check (timpul >=
DATE '2006-01-01' AND timpul < DATE '2006-02-01') ) inherits (master);
...
create table master_122006(constraint ck1_master122006 check (timpul >=
DATE '2006-12-01' AND timpul < DATE '2007-01-01') ) inherits (master);

Indexes:
create index idx1_master_012006__timpul on master_012006 (timpul);
...
create index idx1_master_122006__timpul on master_122006 (timpul);

Rules:
create rule rul_master__insert__012006 as on insert to master where
(timpul >= DATE '2006-01-01' AND timpul < DATE '2006-02-01') do instead
insert into master_012006 values(new.masterid, new.nume, new.timpul);
...
create rule rul_master__insert__122006 as on insert to master where
(timpul >= DATE '2006-12-01' AND timpul < DATE '2007-01-01') do instead
insert into master_122006 values(new.masterid, new.nume, new.timpul);

Analizing tables:
analyze master;
analyze master_012006;
...
analyze master_122006;

I've inserted rows into master for '2006-01-02' and '2006-12-31',

Then
SET constraint_exclusion = on;

But EXPLAIN said:
explain select count(*) from master where timpul = DATE '2006-01-01';
                                    QUERY PLAN
---------------------------------------------------------------------------------
  Aggregate  (cost=209.64..209.65 rows=1 width=0)
    ->  Append  (cost=0.00..209.53 rows=43 width=0)
          ->  Seq Scan on master  (cost=0.00..20.65 rows=4 width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_012006 master  (cost=0.00..1.01 rows=1
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_022006 master  (cost=0.00..20.65 rows=4
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_032006 master  (cost=0.00..20.65 rows=4
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_042006 master  (cost=0.00..1.00 rows=1
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_052006 master  (cost=0.00..20.65 rows=4
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_062006 master  (cost=0.00..20.65 rows=4
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_072006 master  (cost=0.00..20.65 rows=4
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_082006 master  (cost=0.00..20.65 rows=4
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_092006 master  (cost=0.00..20.65 rows=4
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_102006 master  (cost=0.00..20.65 rows=4
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_112006 master  (cost=0.00..20.65 rows=4
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
          ->  Seq Scan on master_122006 master  (cost=0.00..1.01 rows=1
width=0)
                Filter: ((timpul)::date = '2006-01-01'::date)
(28 rows)
Why all partinions tables are scanned? What is wrong?


Thx in advance.
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/