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

Alexander Korotkov <aekorotkov@gmail.com> wrote:
> Alexander Korotkov <aekorotkov@gmail.com> wrote:

>>> Well, no, because that plan wouldn't produce the specified
>>> ordering; or at least it would be a lucky coincidence if it did.
>>> It's only sorting on t1.value.
>>>
>> 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.
New data or just the "luck of the draw" on your next ANALYZE could
result in a totally different plan which wouldn't produce the same
ordering unless specified.

> I found my mistake. My supposition is working only if value column
> in t1 table is unique. But if I replace the index by unique one
> then plan is the same.

Yeah, maybe, for the moment.  When you have ten times the quantity
of data, a completely different plan may be chosen.  If you want a
particular order, ask for it.  The planner will even take the
requested ordering into account when choosing a plan, so the cutoff
for switching to an in-memory hash table or a bitmap index scan
might shift a bit based on the calculated cost of sorting data.

-Kevin