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 ?
