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