[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
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
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
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
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:
