partition queries hitting all partitions even though check key is specified

Поиск

Список

Период

Сортировка

От	Kevin Kempter
Тема	partition queries hitting all partitions even though check key is specified
Дата	2 сентября 2009 г. 14:52:43
Msg-id	200909020852.30405.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 (Scott Marlowe <scott.marlowe@gmail.com>) Re: partition queries hitting all partitions even though check key is specified (Tom Lane <tgl@sss.pgh.pa.us>)
Список	pgsql-performance

Дерево обсуждения

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

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

Предыдущее

От: Hrishikesh (हृषीकेश मेहेंदळे)
Дата: 01 сентября 2009 г., 22:12:04
Сообщение: Re: Performance issues with large amounts of time-series data

Следующее

От: Kenneth Marshall
Дата: 02 сентября 2009 г., 14:56:06
Сообщение: Re: partition queries hitting all partitions even though check key is specified

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

partition queries hitting all partitions even though check key is specified

Предыдущее

Следующее