pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Enabling constraint_exclusion does not avoid scanning all child partitions
3+ messages / 2 participants
[nested] [flat]

* Enabling constraint_exclusion does not avoid scanning all child partitions
@ 2006-12-03 22:21  Fayza Sultan <fayza.sultan@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Fayza Sultan @ 2006-12-03 22:21 UTC (permalink / raw)
  To: 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:




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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Enabling constraint_exclusion does not avoid scanning all child partitions
@ 2006-12-03 22:25  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Fayza Sultan <fayza.sultan@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2006-12-03 22:25 UTC (permalink / raw)
  To: Fayza Sultan <fayza.sultan@gmail.com>; +Cc: pgsql-performance

"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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Enabling constraint_exclusion does not avoid scanning all child partitions
@ 2006-12-03 22:37  Fayza Sultan <fayza.sultan@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Fayza Sultan @ 2006-12-03 22:37 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Your point solved my problem.

Thank you

-Fayza


On 12/4/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "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
>

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-12-03 22:37 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-03 22:21 Enabling constraint_exclusion does not avoid scanning all child partitions Fayza Sultan <fayza.sultan@gmail.com>
2006-12-03 22:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-03 22:37   ` Fayza Sultan <fayza.sultan@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox