pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtable partitioning and select max(id)
5+ messages / 3 participants
[nested] [flat]
* table partitioning and select max(id)
@ 2011-02-04 23:24 Tobias Brox <tobixen@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Tobias Brox @ 2011-02-04 23:24 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table partitioning and select max(id)
@ 2011-02-05 03:38 Ken Cox <kenstir@gmail.com>
parent: Tobias Brox <tobixen@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Ken Cox @ 2011-02-05 03:38 UTC (permalink / raw)
To: Tobias Brox <tobixen@gmail.com>; +Cc: pgsql-performance
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@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
-Ken
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table partitioning and select max(id)
@ 2011-02-05 05:03 Greg Smith <greg@2ndquadrant.com>
parent: Tobias Brox <tobixen@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Greg Smith @ 2011-02-05 05:03 UTC (permalink / raw)
To: Tobias Brox <tobixen@gmail.com>; +Cc: pgsql-performance
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=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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table partitioning and select max(id)
@ 2011-02-05 08:16 Tobias Brox <tobixen@gmail.com>
parent: Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tobias Brox @ 2011-02-05 08:16 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: pgsql-performance
[Greg Smith]
> 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)
Huh ... that sounds a bit like pg 8.0 to me ;-) I remember on 7.x one
had to write "select id from table order by id desc limit 1" to force
through a quick index scan. This was fixed in 8.0 IIRC. I did test
"select id from table order by id desc limit 1" on my parent table
yesterday, it would still do the seq-scan. Even adding a
where-restriction to make sure only one partition was queried I still
got the seq-scan.
> 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.
In this particular case, "wait for 9.1" seems to be the best option :-)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: table partitioning and select max(id)
@ 2011-02-05 08:49 Greg Smith <greg@2ndquadrant.com>
parent: Tobias Brox <tobixen@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Greg Smith @ 2011-02-05 08:49 UTC (permalink / raw)
To: Tobias Brox <tobixen@gmail.com>; +Cc: pgsql-performance
Tobias Brox wrote:
> I did test "select id from table order by id desc limit 1" on my parent table
> yesterday, it would still do the seq-scan. Even adding a
> where-restriction to make sure only one partition was queried I still
> got the seq-scan.
>
Right; you actually have to direct the query toward the specific
partition by name, nothing run against the parent table will work. The
new logic for 9.1 essentially splits the query into this alternate form,
runs it against every partition individually, then combines the
results. If you can afford to wait for 9.1, that is certainly the easy
path here. It just works out of the box in that version.
--
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-02-05 08:49 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-02-04 23:24 table partitioning and select max(id) Tobias Brox <tobixen@gmail.com>
2011-02-05 03:38 ` Ken Cox <kenstir@gmail.com>
2011-02-05 05:03 ` Greg Smith <greg@2ndquadrant.com>
2011-02-05 08:16 ` Tobias Brox <tobixen@gmail.com>
2011-02-05 08:49 ` Greg Smith <greg@2ndquadrant.com>
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