Re: table partitioning and select max(id)

This is a known limitation of partitioning. One solution is to use a
recursive stored proc, which can use indexes. Such a solution is
discussed here:
http://archives.postgresql.org/pgsql-performance/2009-09/msg00036.php

Regards,
Ken

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

On Fri, Feb 4, 2011 at 6:24 PM, Tobias Brox <tobixen(at)gmail(dot)com> wrote:
> I implemented table partitioning, and it caused havoc with a "select
> max(id)" on the parent table - the query plan has changed from a
> lightningly fast backwards index scan to a deadly seq scan.  Both
> partitions are set up with primary key index and draws new IDs from
> the same sequence ... "select max(id)" on both partitions are fast.
> Are there any tricks I can do to speed up this query?  I can't add the
> ID to the table constraints, we may still get in "old" data causing
> rows with fresh IDs to get into the old table.
>
> (I decided to keep this short rather than include lots of details -
> but at least worth mentioning that we're using PG9)
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
-Ken