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

Поиск
Список
Период
Сортировка
От Kevin Kempter
Тема Re: partition queries hitting all partitions even though check key is specified
Дата
Msg-id 200909020905.57003.kevink@consistentstate.com
обсуждение исходный текст
Ответ на Re: partition queries hitting all partitions even though check key is specified  (Kenneth Marshall <ktm@rice.edu>)
Ответы Re: partition queries hitting all partitions even though check key is specified  (bricklen <bricklen@gmail.com>)
Список pgsql-performance
Дерево обсуждения 
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?

В списке pgsql-performance по дате отправления:

Предыдущее
От: Kevin Kempter
Дата:
Сообщение: Re: partition queries hitting all partitions even though check key is specified
Следующее
От: bricklen
Дата:
Сообщение: Re: partition queries hitting all partitions even though check key is specified