I am trying to optimize queries on one of the large table we have by
partitioning it. To test it I created a sample script. When I use Explain
Analyze on one of the queries the query planer shows sequence scan on all
the child partitions instead of only one child containing the required data.
I am using PostgreSQL 8.1.5 on i686-pc-mingw32.



Here is my sample script:




CREATE TABLE parent (
monthdate date NOT NULL,
id int4 NOT NULL,
CONSTRAINT parent_idx PRIMARY KEY (monthdate,id )
);

CREATE TABLE child1
(
CONSTRAINT child1_idx PRIMARY KEY (monthdate,id),
CONSTRAINT child1_chk CHECK (monthdate >= '2006-01-01 00:00:00'::timestamp
without time zone AND monthdate < '2006-02-01 00:00:00'::timestamp without
time zone)
)INHERITS (parent)
WITHOUT OIDS;

CREATE TABLE child2
(
CONSTRAINT child2_idx PRIMARY KEY (monthdate,id),
CONSTRAINT child2_chk CHECK (monthdate >= '2006-02-01 00:00:00'::timestamp
without time zone AND monthdate < '2006-03-01 00:00:00'::timestamp without
time zone)
)INHERITS (parent)
WITHOUT OIDS;

CREATE TABLE child3
(
CONSTRAINT child3_idx PRIMARY KEY (monthdate,id),
CONSTRAINT child3_chk CHECK (monthdate >= '2006-03-01 00:00:00'::timestamp
without time zone AND monthdate < '2006-04-01 00:00:00'::timestamp without
time zone)
)INHERITS (parent)
WITHOUT OIDS;

CREATE RULE child1rule AS
ON INSERT TO parent WHERE
( monthdate >= DATE '2006-01-01' AND monthdate < DATE '2006-02-01' )
DO INSTEAD
INSERT INTO child1 VALUES ( NEW.monthdate,NEW.id);
CREATE RULE child2rule AS
ON INSERT TO parent WHERE
( monthdate >= DATE '2006-02-01' AND monthdate < DATE '2006-03-01' )
DO INSTEAD
INSERT INTO child2 VALUES ( NEW.monthdate,NEW.id);

CREATE RULE child3rule AS
ON INSERT TO parent WHERE
( monthdate >= DATE '2006-03-01' AND monthdate < DATE '2006-04-01' )
DO INSTEAD
INSERT INTO child3 VALUES ( NEW.monthdate,NEW.id);

insert into parent values('2006-01-02',12);
insert into parent values('2006-02-02',13);
insert into parent values('2006-03-02',14);

SET constraint_exclusion = on;
SHOW constraint_exclusion;

EXPLAIN ANALYZE select monthdate, id from parent where monthdate =
'2006-03-11' and id = 13

"Result (cost=0.00..7.87 rows=4 width=8) (actual time=0.063..0.063 rows=0
loops=1)"
" -> Append (cost=0.00..7.87 rows=4 width=8) (actual
time=0.055..0.055rows=0 loops=1)"
" -> Index Scan using parent_idx on parent (cost=0.00..4.83 rows=1
width=8) (actual time=0.013..0.013 rows=0 loops=1)"
" Index Cond: ((monthdate = '2006-03-11'::date) AND (id = 13))"
" -> Seq Scan on child1 parent (cost=0.00..1.01 rows=1 width=8)
(actual time=0.012..0.012 rows=0 loops=1)"
" Filter: ((monthdate = '2006-03-11'::date) AND (id = 13))"
" -> Seq Scan on child2 parent (cost=0.00..1.01 rows=1 width=8)
(actual time=0.005..0.005 rows=0 loops=1)"
" Filter: ((monthdate = '2006-03-11'::date) AND (id = 13))"
" -> Seq Scan on child3 parent (cost=0.00..1.01 rows=1 width=8)
(actual time=0.005..0.005 rows=0 loops=1)"
" Filter: ((monthdate = '2006-03-11'::date) AND (id = 13))"
"Total runtime: 0.225 ms"



I am interested to now what I am doing wrong in above scenario because of
which planner is not optimizing this simple query. Any insight will be
appreciated



Thank you,



- Fayza

"Fayza Sultan" <fayza.sultan@gmail.com> writes:
> CREATE TABLE parent (
> monthdate date NOT NULL,
> id int4 NOT NULL,
> CONSTRAINT parent_idx PRIMARY KEY (monthdate,id )
> );

> CREATE TABLE child1
> (
> CONSTRAINT child1_idx PRIMARY KEY (monthdate,id),
> CONSTRAINT child1_chk CHECK (monthdate >= '2006-01-01 00:00:00'::timestamp
> without time zone AND monthdate < '2006-02-01 00:00:00'::timestamp without
> time zone)

monthdate is date, not timestamp. See the caveat in the documentation
about avoiding cross-type comparisons when formulating constraints for
constraint exclusion to use.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend