Message-ID: <4D4CDA0C.20000@2ndquadrant.com>
Date: Sat, 05 Feb 2011 00:03:08 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Tobias Brox <tobixen@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: table partitioning and select max(id)
References: <AANLkTi=pzBtH4i_WYrbR9JEyq+49vPPVJf-q08z_Bhu=@mail.gmail.com>
In-Reply-To: <AANLkTi=pzBtH4i_WYrbR9JEyq+49vPPVJf-q08z_Bhu=@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books