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

Alexander Korotkov  wrote:
> Alexander Korotkov  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