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

On Wed, Sep 2, 2009 at 8:52 AM, Kevin Kempter<kevink@consistentstate.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 ?