Обсуждение: partition queries hitting all partitions even though check key is specified

Поиск
Список
Период
Сортировка

partition queries hitting all partitions even though check key is specified

От
Kevin Kempter
Дата:
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...

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

От
Kenneth Marshall
Дата:
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
>

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

От
Scott Marlowe
Дата:
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 ?

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

От
Kevin Kempter
Дата:
On Wednesday 02 September 2009 09:02:27 Scott Marlowe wrote:
> 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 ?


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)

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

От
Kevin Kempter
Дата:
On Wednesday 02 September 2009 08:55:38 Kenneth Marshall wrote:
> 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...

can you point me to the thread, or what the subject line was?

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

От
bricklen
Дата:
On Wed, Sep 2, 2009 at 8:05 AM, Kevin Kempter <kevink@consistentstate.com> wrote:

> > 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


Does the plan change if you use a hard-coded timestamp in your query?

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

От
Greg Jaman
Дата:
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@consistentstate.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@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 ?


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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

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

От
Tom Lane
Дата:
Kevin Kempter <kevink@consistentstate.com> writes:
> I cant figure out why we're scanning all of our partitions.

The example works as expected for me:

regression=# 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);
CREATE TABLE
regression=# 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 TABLE
regression=# 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 TABLE
regression=# explain select * from url_hits where "time" <
date_part('epoch'::text, '2009-08-12'::timestamp without time zone)::integer;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Result  (cost=0.00..82.50 rows=1401 width=24)
   ->  Append  (cost=0.00..82.50 rows=1401 width=24)
         ->  Seq Scan on url_hits  (cost=0.00..27.50 rows=467 width=24)
               Filter: ("time" < 1250049600)
         ->  Seq Scan on url_hits_2011_12 url_hits  (cost=0.00..27.50 rows=467 width=24)
               Filter: ("time" < 1250049600)
         ->  Seq Scan on url_hits_2009_08 url_hits  (cost=0.00..27.50 rows=467 width=24)
               Filter: ("time" < 1250049600)
(8 rows)

regression=# set constraint_exclusion TO 1;
SET
regression=# explain select * from url_hits where "time" <
date_part('epoch'::text, '2009-08-12'::timestamp without time zone)::integer;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Result  (cost=0.00..55.00 rows=934 width=24)
   ->  Append  (cost=0.00..55.00 rows=934 width=24)
         ->  Seq Scan on url_hits  (cost=0.00..27.50 rows=467 width=24)
               Filter: ("time" < 1250049600)
         ->  Seq Scan on url_hits_2009_08 url_hits  (cost=0.00..27.50 rows=467 width=24)
               Filter: ("time" < 1250049600)
(6 rows)


You sure you remembered those fiddly little casts everywhere?
(Frankly, declaring "time" as integer and not timestamp here strikes
me as utter lunacy.)  What PG version are you using?

            regards, tom lane

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

От
Greg Stark
Дата:
On Wed, Sep 2, 2009 at 4:05 PM, Kevin Kempter<kevink@consistentstate.com> wrote:
> explain select * from pwreport.url_hits where "time" > extract('epoch' from
> timestamp '2009-08-12 00:00:00')::int4;
>

Hm. Actually I would have thought this would work. You're using
"timestamp" which defaults to without timezone and
date_part(text,timestamp) is marked immutable. So the condition in the
whree clause is being inlined at plan time so it's just a simple
comparison against an integer. That does appear to be successfully
happening.

I think what's happening is that the constraints are not being inlined
and the planner is not inlining them before comparing them to the
where clause. I wonder if this worked in the past or not.

You could make things work by defining your constraints to use the
integer results of those expressions explicitly. You could even do
write a simple perl script (or insert favourite scripting language) to
generate the constraint definitions from timestamps if you wanted.

--
greg
http://mit.edu/~gsstark/resume.pdf

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

От
Kevin Kempter
Дата:
On Wednesday 02 September 2009 09:19:20 Tom Lane wrote:
> Kevin Kempter <kevink@consistentstate.com> writes:
> > I cant figure out why we're scanning all of our partitions.
>
> The example works as expected for me:
>
> regression=# 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);
> CREATE TABLE
> regression=# 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 TABLE
> regression=# 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 TABLE
> regression=# explain select * from url_hits where "time" <
> date_part('epoch'::text, '2009-08-12'::timestamp without time
> zone)::integer; QUERY PLAN
> ---------------------------------------------------------------------------
>-------------- Result  (cost=0.00..82.50 rows=1401 width=24)
>    ->  Append  (cost=0.00..82.50 rows=1401 width=24)
>          ->  Seq Scan on url_hits  (cost=0.00..27.50 rows=467 width=24)
>                Filter: ("time" < 1250049600)
>          ->  Seq Scan on url_hits_2011_12 url_hits  (cost=0.00..27.50
> rows=467 width=24) Filter: ("time" < 1250049600)
>          ->  Seq Scan on url_hits_2009_08 url_hits  (cost=0.00..27.50
> rows=467 width=24) Filter: ("time" < 1250049600)
> (8 rows)
>
> regression=# set constraint_exclusion TO 1;
> SET
> regression=# explain select * from url_hits where "time" <
> date_part('epoch'::text, '2009-08-12'::timestamp without time
> zone)::integer; QUERY PLAN
> ---------------------------------------------------------------------------
>-------------- Result  (cost=0.00..55.00 rows=934 width=24)
>    ->  Append  (cost=0.00..55.00 rows=934 width=24)
>          ->  Seq Scan on url_hits  (cost=0.00..27.50 rows=467 width=24)
>                Filter: ("time" < 1250049600)
>          ->  Seq Scan on url_hits_2009_08 url_hits  (cost=0.00..27.50
> rows=467 width=24) Filter: ("time" < 1250049600)
> (6 rows)
>
>
> You sure you remembered those fiddly little casts everywhere?
> (Frankly, declaring "time" as integer and not timestamp here strikes
> me as utter lunacy.)  What PG version are you using?
>
>             regards, tom lane


I actually inherited the whole "time" scenario - agreed, its crazy.

In any case I ran the exact same query as you and it still scans most (but not
all) partitions. Were on version


pwreport=# set constraint_exclusion TO 1;SET
                                   
pwreport=#
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..9677473.91 rows=148258840 width=432)
   ->  Append  (cost=0.00..9677473.91 rows=148258840 width=432)
         ->  Seq Scan on url_hits  (cost=0.00..12.12 rows=57 width=432)
               Filter: ("time" < 1250035200)
         ->  Seq Scan on url_hits_2009_07 url_hits  (cost=0.00..12.12 rows=57
width=432)
               Filter: ("time" < 1250035200)
         ->  Seq Scan on url_hits_2009_06 url_hits  (cost=0.00..12.12 rows=57
width=432)
               Filter: ("time" < 1250035200)
         ->  Seq Scan on url_hits_2009_05 url_hits  (cost=0.00..12.12 rows=57
width=432)
               Filter: ("time" < 1250035200)
         ->  Seq Scan on url_hits_2009_04 url_hits  (cost=0.00..12.12 rows=57
width=432)
               Filter: ("time" < 1250035200)
         ->  Seq Scan on url_hits_2009_03 url_hits  (cost=0.00..12.12 rows=57
width=432)
               Filter: ("time" < 1250035200)
         ->  Seq Scan on url_hits_2009_02 url_hits  (cost=0.00..12.12 rows=57
width=432)
               Filter: ("time" < 1250035200)
         ->  Seq Scan on url_hits_2009_01 url_hits  (cost=0.00..12.12 rows=57
width=432)
               Filter: ("time" < 1250035200)
         ->  Seq Scan on url_hits_2008_12 url_hits  (cost=0.00..12.12 rows=57
width=432)
               Filter: ("time" < 1250035200)
         ->  Seq Scan on url_hits_2008_11 url_hits  (cost=0.00..12.12 rows=57
width=432)
               Filter: ("time" < 1250035200)
         ->  Seq Scan on url_hits_2008_10 url_hits  (cost=0.00..12.12 rows=57
width=432)
               Filter: ("time" < 1250035200)
         ->  Seq Scan on url_hits_2008_09 url_hits  (cost=0.00..12.12 rows=57
width=432)
               Filter: ("time" < 1250035200)
         ->  Index Scan using url_hits_2009_08_time_index on url_hits_2009_08
url_hits  (cost=0.00..9677328.41 rows=148258156 width=131)
               Index Cond: ("time" < 1250035200)
(28 rows)

>     id integer NOT NULL,
>     content_type_id integer,
>     file_extension_id integer,
>     "time" integer,
>     bytes integer NOT NULL,
>     path_id integer);


Also, we do have indexes on the child table, will this change things?

\d url_hits_2009_08
                                     Table "url_hits_2009_08"
      Column       |         Type          |
Modifiers
-------------------+-----------------------+----------------------------------------------------------------
 id                | integer               | not null default
nextval('url_hits_id_seq'::regclass)
 direction         | proxy_direction_enum  | not null
 content_type_id   | integer               |
 file_extension_id | integer               |
 time              | integer               |
 bytes             | integer               | not null
 path_id           | integer               |
Indexes:
    "url_hits_2009_08_pk" PRIMARY KEY, btree (id)
    "url_hits_2009_08_time_index" btree ("time")
Check constraints:
    "url_hits_2009_08_time_check" CHECK ("time" >= date_part('epoch'::text,
'2009-08-01 00:00:00'::timestamp without time zone)::integer AND "time" <=
date_part('epoch'::text, '2009-08-31 23:59:59'::timestamp without time
zone)::integer)
Inherits: url_hits
Tablespace: "pwreport_1000"

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

От
"Joshua D. Drake"
Дата:
On Wed, 2009-09-02 at 09:39 -0600, Kevin Kempter wrote:

> >
> > You sure you remembered those fiddly little casts everywhere?
> > (Frankly, declaring "time" as integer and not timestamp here strikes
> > me as utter lunacy.)  What PG version are you using?
> >
> >             regards, tom lane
>

As far as I know constraint exclusion doesn't work with date_part or
extract().

The following caveats apply to constraint exclusion:

      * 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.

http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html

Or did I miss something?

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

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

От
Tom Lane
Дата:
"Joshua D. Drake" <jd@commandprompt.com> writes:
> As far as I know constraint exclusion doesn't work with date_part or
> extract().

Uh, you clipped the example in my message showing that it does,
at least in the particular case Kevin showed us.

There are some variants of date_part that aren't immutable, but timestamp
without tz isn't one of them.

Still, I agree that not depending on it would be better.

            regards, tom lane

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

От
Tom Lane
Дата:
Kevin Kempter <kevink@consistentstate.com> writes:
> In any case I ran the exact same query as you and it still scans most (but not
> all) partitions.

AFAICT it's scanning the right partitions in this example.  What's
different in the case where it scans all?

> Were on version

This seems to have got truncated ...

            regards, tom lane

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

От
Scott Carey
Дата:

On 9/2/09 8:59 AM, "Joshua D. Drake" <jd@commandprompt.com> wrote:

> On Wed, 2009-09-02 at 09:39 -0600, Kevin Kempter wrote:
>
>>>
>>> You sure you remembered those fiddly little casts everywhere?
>>> (Frankly, declaring "time" as integer and not timestamp here strikes
>>> me as utter lunacy.)  What PG version are you using?
>>>
>>>                     regards, tom lane
>>
>
> As far as I know constraint exclusion doesn't work with date_part or
> extract().
>
> The following caveats apply to constraint exclusion:
>
>       * 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.
>
> http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html
>
> Or did I miss something?

I've only ever seen it work for constants.  Partitioning by date works fine
as far as I know no matter how you set the constraint rule up (functions are
fine here, but slower).  But the query itself has to submit a constant in
the WHERE clause.  Prepared statements and parameterization on the query
won't work either.
For dates, literals like 'yesterday' work, but function equivalents don't.
Basically if the planner interprets the where condition on the column as a
constant (even if resolving that constant calls a function, such as
'yesterday') it will work.  Otherwise, it won't.


>
> Joshua D. Drake
>
> --
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
> Consulting, Training, Support, Custom Development, Engineering
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

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

От
"Joshua D. Drake"
Дата:
On Wed, 2009-09-02 at 09:39 -0600, Kevin Kempter wrote:

> >
> > You sure you remembered those fiddly little casts everywhere?
> > (Frankly, declaring "time" as integer and not timestamp here strikes
> > me as utter lunacy.)  What PG version are you using?
> >
> >             regards, tom lane
>

As far as I know constraint exclusion doesn't work with date_part or
extract().

The following caveats apply to constraint exclusion:

      * 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.

http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html

Or did I miss something?

Joshua D. Drake

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering

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

От
Josh Berkus
Дата:
On 9/2/09 10:05 AM, Kevin Kempter wrote:
> On Wednesday 02 September 2009 09:02:27 Scott Marlowe wrote:
>> 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.

I don't think extract() is immutable, which would pretty much invalidate
your check constraints as far as CE is concerned.

I suggest feeding the actual numeric values to the check constraints.

--
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

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

От
"Joshua D. Drake"
Дата:
On Mon, 2010-07-12 at 22:01 -0500, Josh Berkus wrote:
> On 9/2/09 10:05 AM, Kevin Kempter wrote:
> > On Wednesday 02 September 2009 09:02:27 Scott Marlowe wrote:
> >> 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.
>
> I don't think extract() is immutable, which would pretty much invalidate
> your check constraints as far as CE is concerned.

Correct.

Joshua D. Drake


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering

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

От
"Joshua D. Drake"
Дата:
On Mon, 2010-07-12 at 22:01 -0500, Josh Berkus wrote:
> On 9/2/09 10:05 AM, Kevin Kempter wrote:
> > On Wednesday 02 September 2009 09:02:27 Scott Marlowe wrote:
> >> 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.
>
> I don't think extract() is immutable, which would pretty much invalidate
> your check constraints as far as CE is concerned.

Correct.

Joshua D. Drake


--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering