Re: Odd sorting behaviour

Steinar,

> - The "subquery scan o12" phase outputs 1186 rows, yet 83792 are sorted.
Where
> do the other ~82000 rows come from? And why would it take ~100ms to sort
the
> rows at all? (In earlier tests, this was _one full second_ but somehow
that
> seems to have improved, yet without really improving the overall query
time.

I'm puzzled by the "83792" rows as well. I've a feeling that Explain Analyze
is failing to output a step.

> - Why does it use uid_index for an index scan on the table, when it
obviously
> has no filter on it (since it returns all the rows)?

In order to support the merge join. It should be a bit faster to do the sort
using the index than the actual table. Also, because you pass the <> 0
condition.

> Furthermore, why would
> this take half a second? (The machine is a 950MHz machine with SCSI
disks.)

I don't see half a second here.

> - Also, the outer sort (the sorting of the 58792 rows from the merge join)
> is slow. :-)

I don't see a sort after the merge join. Which version are we talking about?
I'm looking at the 7.4 version because that outputs more detail.

Most of your time is spent in that merge join. Why don't you try doubling
sort_mem temporarily to see how it does? Or even raising shared_buffers?

--
-Josh Berkus
Aglio Database Solutions
San Francisco