Re: table partitioning and select max(id)

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

This problem was fixed in the upcoming 9.1:

http://archives.postgresql.org/pgsql-committers/2010-11/msg00028.php
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=034967bdcbb0c7be61d0500955226e1234ec5f04

Here's the comment from that describing the main technique used to fix it:

"This module tries to replace MIN/MAX aggregate functions by subqueries
of the form

(SELECT col FROM tab WHERE ... ORDER BY col ASC/DESC LIMIT 1)

Given a suitable index on tab.col, this can be much faster than the
generic scan-all-the-rows aggregation plan. We can handle multiple
MIN/MAX aggregates by generating multiple subqueries, and their
orderings can be different. However, if the query contains any
non-optimizable aggregates, there's no point since we'll have to scan
all the rows anyway."

Unfortunately that change ends a series of 6 commits of optimizer
refactoring in this area, so it's not the case that you just apply this
one commit as a bug-fix to a 9.0 system. I have a project in process to
do the full backport needed I might be able to share with you if that
works out, and you're willing to run with a customer patched server
process. Using one of the user-space ideas Ken suggested may very well
be easier for you. I'm stuck with an app I can't rewrite to do that.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books