Re: Incorrect behavior with CE and ORDER BY

Tom Lane ha scritto:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Limit (50)
>> Sort (key: pse_lastlogin)
>> Result
>> Append
>> Limit (50)
>> SeqScan tbl_profile_search
>> Limit (50)
>> Indexscan tbl_profile_search_interest_1
>> Limit (50)
>> IndexScan on the index mentioned above
>
> is wrong because there's no guarantee that the first 50 elements of a
> seqscan will be anything special. You could imagine dealing with that
> by sorting the seqscan results and limiting to 50, or by not
> sorting/limiting that data at all but letting the upper sort see all the
> seqscan entries. Offhand I think either of those could win depending on
> how many elements the seqscan will yield. Also, it might be interesting
> to consider inventing a "merge" plan node type that takes N
> already-sorted inputs and produces a sorted output stream. Then we'd
> need to trade off this approach versus doing the top-level sort, which
> could cope with some of its inputs not being pre-sorted.
>
> This seems to have some aspects in common with the recent discussion
> about how to optimize min/max aggregates across an appendrel set.

The plan proposed by Alvaro reminds me of:

http://archives.postgresql.org/pgsql-performance/2005-09/msg00047.php

My proposal was in fact (Alvaro's plan + first Tom's suggested change):

Limit (50)
Sort (key: pse_lastlogin)
Result
Append
Limit (50)
Sort (key: pse_lastlogin)
SeqScan tbl_profile_search
Limit (50)
Indexscan tbl_profile_search_interest_1
Limit (50)
IndexScan on the index mentioned above

The plan was generated rewriting the query to use explicit subselect and
forcing the planner to order by and limit for each subquery.

I've tried a few times to write a patch to handle it, but I wasn't able
to do it because of my lack of internals knowledge and spare time.

Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com