partitioning max() sql not using index

From: Kevin Kempter <kevink(at)consistentstate(dot)com>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: partitioning max() sql not using index
Date: 2009-09-08 20:53:18
Message-ID: 200909081453.18931.kevink@consistentstate.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Eugene Morozov 2009-09-09 05:34:37 Re: Forcing postgresql to use an index
Previous Message David Fetter 2009-09-08 18:17:11 Re: [sfpug] Statistics and PostgreSQL: Streaming Webcast tonight