Enabling constraint_exclusion does not avoid scanning all child partitions - Mailing list pgsql-performance
| From | Fayza Sultan |
|---|---|
| Subject | Enabling constraint_exclusion does not avoid scanning all child partitions |
| Date | |
| Msg-id | 38443e510612031421y3d8f8f05x8befb7adc166717d@mail.gmail.com Whole thread Raw |
| Responses |
Re: Enabling constraint_exclusion does not avoid scanning all child partitions
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-performance |
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:
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);
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
" -> Append (cost=0.00..7.87 rows=4 width=8) (actual time=0.055..0.055 rows=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
pgsql-performance by date: