Re: table partitioning and select max(id)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux