Re: partition queries hitting all partitions even though check key is specified - Mailing list pgsql-performance
| From | Kenneth Marshall |
|---|---|
| Subject | Re: partition queries hitting all partitions even though check key is specified |
| Date | |
| Msg-id | 20090902145538.GB22702@it.is.rice.edu Whole thread Raw |
| In response to | partition queries hitting all partitions even though check key is specified (Kevin Kempter <kevink@consistentstate.com>) |
| Responses |
Re: partition queries hitting all partitions even though check key is specified
|
| List | pgsql-performance |
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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
pgsql-performance by date: