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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html