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

The planner does not yet work as efficiently as it could
with child tables. Check the recent mail archives for a
long discussion of the same.

Regards,
Ken

On Wed, Sep 02, 2009 at 08:52:30AM -0600, Kevin Kempter 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;
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------
> Result (cost=0.00..23766294.06 rows=816492723 width=432)
> -> Append (cost=0.00..23766294.06 rows=816492723 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..1838010.76
> rows=75607779 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)
>
>
>
> explain select * from pwreport.url_hits where "time" > 1220227200::int4;
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------
> Result (cost=0.00..23775893.12 rows=965053504 width=432)
> -> Append (cost=0.00..23775893.12 rows=965053504 width=432)
> -> Seq Scan on url_hits (cost=0.00..12.12 rows=57 width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_12 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_11 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_10 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_09 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_08 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_07 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_06 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_05 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_04 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_03 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_02 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2011_01 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_12 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_11 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_10 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_09 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_08 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_07 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_06 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_05 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_04 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_03 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_02 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2010_01 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_12 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_11 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_10 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_09 url_hits (cost=0.00..1847476.45
> rows=75997156 width=128)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_07 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_06 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_05 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_04 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_03 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_02 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_01 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2008_12 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2008_11 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2008_10 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2008_09 url_hits (cost=0.00..12.12 rows=57
> width=432)
> Filter: ("time" > 1220227200)
> -> Seq Scan on url_hits_2009_08 url_hits (cost=0.00..21927943.80
> rows=889054125 width=131)
> Filter: ("time" > 1220227200)
> (84 rows)
>
>
>
> Anyone have any thoughts why we're scanning all partitions?
>
> We do have constraint_exclusion on:
>
> # show constraint_exclusion;
> constraint_exclusion
> ----------------------
> on
> (1 row)
>
>
> Thanks in advance...
>
> --
> 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
>