Re: partitioning max() sql not using index

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