Re: partition queries hitting all partitions even though check key is specified

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 ?

In response to

Responses

Browse pgsql-performance by date

  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