Skip site navigation (1) Skip section navigation (2)

Re: partition queries hitting all partitions even thoughcheck key is specified

From: Kenneth Marshall <ktm(at)rice(dot)edu>
To: Kevin Kempter <kevink(at)consistentstate(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: partition queries hitting all partitions even thoughcheck key is specified
Date: 2009-09-02 14:55:38
Message-ID: 20090902145538.GB22702@it.is.rice.edu (view raw or flat)
Thread:
Lists: 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> 

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2009-09-02 15:02:27
Subject: Re: partition queries hitting all partitions even though check key is specified
Previous:From: Kevin KempterDate: 2009-09-02 14:52:30
Subject: partition queries hitting all partitions even though check key is specified

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group