Re: Planner issue on sorting joining of two tables with limit

On Fri, May 7, 2010 at 11:35 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
>>>> I just don't find why it is coincidence. I think that such plan
>>>> will always produce result ordered by two columns, because such
>>>> nested index scan always produce this result.
>
>> Assuming a nested index scan, or any particular plan, is unwise.
>
> I think he's proposing that the planner should recognize that a plan
> of this type produces a result sorted by the additional index columns.
> I'm not convinced either that the sortedness property really holds,
> or that it would be worth the extra planning effort to check for;
> but it's not a fundamentally misguided proposal.

I took a slightly different point - a nested loop will be ordered by
the ordering of the outer side and then, within that, the ordering of
the inner side, presuming (not quite sure how to phrase this) that the
outer side is "unique enough" with respect to the ordering. I'm not
too sure whether there's anything useful we can do with this
information in a reasonable number of CPU cycles, but it is something
I've noticed before while reading the code.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company