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

Check the caveats at
http://www.postgresql.org/docs/current/static/ddl-partitioning.html

"Constraint exclusion only works when the query's WHERE clause contains
constants. A parameterized query will not be optimized, since the planner
cannot know which partitions the parameter value might select at run time.
For the same reason, "stable" functions such as CURRENT_DATE must be
avoided."

I think this applies to both your query and the CHECK statement in the table
definition.

-Greg Jaman

On Wed, Sep 2, 2009 at 8:05 AM, Kevin Kempter <kevink(at)consistentstate(dot)com>wrote:

> On Wednesday 02 September 2009 09:02:27 Scott Marlowe wrote:
> > 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 ?
>
>
> Yes, same results:
>
> explain select * from pwreport.url_hits where "time" > extract('epoch' from
> timestamp '2009-08-12 00:00:00')::int4;
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------
> Result (cost=0.00..23785180.16 rows=817269615 width=432)
> -> Append (cost=0.00..23785180.16 rows=817269615 width=432)
> -> Seq Scan on url_hits (cost=0.00..12.12 rows=57 width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_12 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_11 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_10 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_09 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_08 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_07 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_06 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_05 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_04 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_03 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_02 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2011_01 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_12 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_11 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_10 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_09 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_08 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_07 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_06 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_05 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_04 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_03 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_02 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2010_01 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2009_12 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2009_11 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2009_10 url_hits (cost=0.00..12.12
> rows=57
> width=432)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2009_09 url_hits (cost=0.00..1856896.86
> rows=76384671 width=128)
> Filter: ("time" > 1250035200)
> -> Seq Scan on url_hits_2009_08 url_hits (cost=0.00..21927943.80
> rows=740883348 width=131)
> Filter: ("time" > 1250035200)
> (62 rows)
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>