"Charles Duffy" <charles.duffy@gmail.com> writes:
> On 7/12/06, Qingqing Zhou <zhouqq@cs.toronto.edu> wrote:
>> the problem here is that 29247 doesn't look like a big number so I can't see
>> why your patch solved the problem, unless the qsort_comparetup() function of
>> the data type eats too many circles or the cpu is too slow.
> Well...when exhibiting the problem, execution stays inside qsort() for
> the entire 'delay period' - I don't think its off in some other recess
> which is also lacking in interrupt flag checking.
> As for the CPU - the machine is a 4 way Opteron, and otherwise
> performs well. It does seem odd that the in-memory sort takes as long
> as it does - the plan may suggest a reason.
Well, there's something awfully fishy here. Compare the two lower-level
sorts in your EXPLAIN output:
-> Sort (cost=2909.44..2944.94 rows=14201 width=320) (actual time=78196.698..78239.799 rows=29247 loops=1)
Sort Key: record, commr1, envr1, docin, creat, flati, flato, doc, docst, vlord, vl0, vl1, vl2, vl3, vl4, vl5,
vl6,vl7, vl8, vl9
-> Append (cost=0.00..1930.02 rows=14201 width=320) (actual time=0.052..396.577 rows=29247 loops=1)
-> Sort (cost=403.42..403.59 rows=71 width=320) (actual time=318.727..339.305 rows=10932 loops=1)
Sort Key: record, commr1, envr1, docin, creat, flati, flato, doc, docst, vlord, vl0, vl1, vl2, vl3, vl4, vl5,
vl6,vl7, vl8, vl9
-> Append (cost=78.88..401.23 rows=71 width=320) (actual time=5.197..192.868 rows=10932 loops=1)
The first one took 77843.222 msec to sort 29247 rows, the second took
146.437 msec to sort 10932 rows with what appears to be the same sort
key. One of these things is not like the other ...
What are the data types of the sort columns? Is there anything much
different in the statistics of the two subqueries --- for example,
maybe one produces a lot of output that only differs in the rightmost
sort columns, where the other has keys that always differ in a leading
column?
I forget if you are running 8.1 or not, but if you are, turning on
trace_sort might produce useful information.
regards, tom lane
