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.18688.kevink@consistentstate.com обсуждение исходный текст |
Ответ на | 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
(Greg Jaman <gjaman@gmail.com>)
Re: partition queries hitting all partitions even though check key is specified (Greg Stark <gsstark@mit.edu>) Re: partition queries hitting all partitions even though check key is specified (Josh Berkus <josh@agliodbs.com>) |
Список | pgsql-performance |
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)
В списке pgsql-performance по дате отправления:
Предыдущее
От: Scott MarloweДата:
Сообщение: 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