partitioning max() sql not using index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux