Postgres Pro
Downloads
Home   >   mailing lists

Thread: partitioning max() sql not using index

partitioning max() sql not using index

From
Kevin Kempter
Date:
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...



Re: partitioning max() sql not using index

From
Heikki Linnakangas
Date:
Kevin Kempter wrote:
> 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:

The planner isn't smart enough to create the plan you're expecting.
There was discussion and even a patch posted recently about that:

http://archives.postgresql.org/pgsql-hackers/2009-07/msg01115.php

It seems the thread petered out, but the concept seems sane.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

Re: partitioning max() sql not using index

From
"Kenneth Cox"
Date:
In case you aren't comfortable running unreleased planner patches from
pgsql-hackers, a workaround was discussed on this list recently:

http://archives.postgresql.org/pgsql-performance/2009-09/msg00036.php

On Wed, 09 Sep 2009 06:05:22 -0400, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:

> Kevin Kempter wrote:
>> 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:
>
> The planner isn't smart enough to create the plan you're expecting.
> There was discussion and even a patch posted recently about that:
>
> http://archives.postgresql.org/pgsql-hackers/2009-07/msg01115.php
>
> It seems the thread petered out, but the concept seems sane.
>



--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Re: partitioning max() sql not using index

From
Kevin Kempter
Date:
On Wednesday 09 September 2009 07:56:53 Kenneth Cox wrote:
> In case you aren't comfortable running unreleased planner patches from
> pgsql-hackers, a workaround was discussed on this list recently:
>
> http://archives.postgresql.org/pgsql-performance/2009-09/msg00036.php
>
> On Wed, 09 Sep 2009 06:05:22 -0400, Heikki Linnakangas
>
> <heikki.linnakangas@enterprisedb.com> wrote:
> > Kevin Kempter wrote:
> >> 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:
> >
> > The planner isn't smart enough to create the plan you're expecting.
> > There was discussion and even a patch posted recently about that:
> >
> > http://archives.postgresql.org/pgsql-hackers/2009-07/msg01115.php
> >
> > It seems the thread petered out, but the concept seems sane.

Excellent! thanks this is quite helpful