Re: partitioning max() sql not using index

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