Hi all I have a large table (>2billion rows) that's partitioned by date based
on an epoch int value. We're running a select max(id) where id is the PK. I
have a PK index on each of the partitions, no indexes at all on the base
table.
If I hit a partition table directly I get an index scan as expected:
explain select max(id) from pwreport.bigtab_2009_09;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.06..0.07 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..0.06 rows=1 width=8)
-> Index Scan Backward using bigtab_2009_09_pk on bigtab_2009_09
(cost=0.00..12403809.95 rows=205659919 width=8)
Filter: (id IS NOT NULL)
(5 rows)
However if I hit the base table I get a sequential scan on every partition as
opposed to index scans:
explain select max(id) from pwreport.bigtab;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=27214318.67..27214318.68 rows=1 width=8)
-> Append (cost=0.00..24477298.53 rows=1094808053 width=8)
-> Seq Scan on bigtab (cost=0.00..11.70 rows=170 width=8)
-> Seq Scan on bigtab_2011_12 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2011_11 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2011_10 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2011_09 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2011_08 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2011_07 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2011_06 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2011_05 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2011_04 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2011_03 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2011_02 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2011_01 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_12 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_11 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_10 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_09 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_08 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_07 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_06 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_05 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_04 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_03 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_02 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2010_01 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2009_12 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2009_11 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2009_10 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2009_09 bigtab (cost=0.00..4599227.19
rows=205659919 width=8)
-> Seq Scan on bigtab_2009_07 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2009_06 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2009_05 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2009_04 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2009_03 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2009_02 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2009_01 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2008_12 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2008_11 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2008_10 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2008_09 bigtab (cost=0.00..11.70 rows=170
width=8)
-> Seq Scan on bigtab_2009_08 bigtab (cost=0.00..19877615.04
rows=889141504 width=8)
(43 rows)
Thoughts?
Thanks in advance...
