Alexander Korotkov <aekorotkov(at)gmail(dot)com> wrote:
> Alexander Korotkov <aekorotkov(at)gmail(dot)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.
In response to
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2010-05-07 15:35:43|
|Subject: Re: Planner issue on sorting joining of two tables with limit |
|Previous:||From: Mark Stosberg||Date: 2010-05-07 14:10:31|
|Subject: Re: debugging handle exhaustion and 15 min/ 5mil row delete|