From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Kevin Kempter <kevink(at)consistentstate(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: partition queries hitting all partitions even though check key is specified |
Date: | 2009-09-02 15:02:27 |
Message-ID: | dcc563d10909020802h524163besce7217af8466a7cd@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Sep 2, 2009 at 8:52 AM, Kevin Kempter<kevink(at)consistentstate(dot)com> wrote:
> Hi all;
>
> I cant figure out why we're scanning all of our partitions.
>
> We setup our tables like this:
>
>
> Base Table:
>
> CREATE TABLE url_hits (
> id integer NOT NULL,
> content_type_id integer,
> file_extension_id integer,
> "time" integer,
> bytes integer NOT NULL,
> path_id integer,
> protocol public.protocol_enum
> );
>
> Partitions:
> create table url_hits_2011_12 (
> check (
> "time" >= extract ('epoch' from timestamp '2011-12-01
> 00:00:00')::int4
> and "time" <= extract ('epoch' from timestamp '2011-12-31
> 23:59:59')::int4
> )
> ) INHERITS (url_hits);
>
>
> CREATE RULE url_hits_2011_12_insert as
> ON INSERT TO url_hits
> where
> ( "time" >= extract ('epoch' from timestamp '2011-12-01 00:00:00')::int4
> and "time" <= extract ('epoch' from timestamp '2011-12-31
> 23:59:59')::int4 )
> DO INSTEAD
> INSERT INTO url_hits_2011_12 VALUES (NEW.*) ;
>
> ...
>
> create table url_hits_2009_08 (
> check (
> "time" >= extract ('epoch' from timestamp '2009-08-01
> 00:00:00')::int4
> and "time" <= extract ('epoch' from timestamp '2009-08-31
> 23:59:59')::int4
> )
> ) INHERITS (url_hits);
>
>
> CREATE RULE url_hits_2009_08_insert as
> ON INSERT TO url_hits
> where
> ( "time" >= extract ('epoch' from timestamp '2009-08-01 00:00:00')::int4
> and "time" <= extract ('epoch' from timestamp '2009-08-31
> 23:59:59')::int4 )
> DO INSTEAD
> INSERT INTO url_hits_2009_08 VALUES (NEW.*) ;
>
> ...
>
> the explain plan shows most any query scans/hits all partitions even if we
> specify the partition key:
>
> explain select * from pwreport.url_hits where "time" >
> date_part('epoch'::text, '2009-08-12'::timestamp without time zone)::integer;
Have you tried using extract here instead of date_part ?
From | Date | Subject | |
---|---|---|---|
Next Message | astro77 | 2009-09-02 15:04:04 | Slow select times on select with xpath |
Previous Message | Kenneth Marshall | 2009-09-02 14:55:38 | Re: partition queries hitting all partitions even though check key is specified |