From: Greg Smith <greg@2ndquadrant.com>
To: Tobias Brox <tobixen@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: table partitioning and select max(id)
Date: Sat, 05 Feb 2011 00:03:08 -0500
Message-ID: <4D4CDA0C.20000@2ndquadrant.com> (raw)
In-Reply-To: <AANLkTi=pzBtH4i_WYrbR9JEyq+49vPPVJf-q08z_Bhu=@mail.gmail.com>
References: <AANLkTi=pzBtH4i_WYrbR9JEyq+49vPPVJf-q08z_Bhu=@mail.gmail.com>
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.phphttp://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=034967bdcbb0c7be61d0500955226e1234e...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, tobixen@gmail.com
Subject: Re: table partitioning and select max(id)
In-Reply-To: <4D4CDA0C.20000@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox