Re: [PERFORM] A Better External Sort?

[removed -performance, not subscribed]

On Sat, Oct 01, 2005 at 01:42:32PM -0400, Ron Peacetree wrote:
> You have not said anything about what HW, OS version, and pg version
> used here, but even at that can't you see that something Smells Wrong?

Somewhat old machine running 7.3 on Linux 2.4. Not exactly speed
daemons but it's still true that the whole process would be CPU bound
*even* if the O/S could idle while it's waiting. PostgreSQL used a
*whole CPU* which is its limit. My point is that trying to reduce I/O
by increasing CPU usage is not going to be benficial, we need CPU usage
down also.

Anyway, to bring some real info I just profiled PostgreSQL 8.1beta
doing an index create on a 2960296 row table (3 columns, table size
317MB).

The number 1 bottleneck with 41% of user time is comparetup_index. It
was called 95,369,361 times (about 2*ln(N)*N). It used 3 tapes. Another
15% of time went to tuplesort_heap_siftup.

The thing is, I can't see anything in comparetup_index() that could
take much time. The actual comparisons are accounted elsewhere
(inlineApplySortFunction) which amounted to <10% of total time. Since
nocache_index_getattr doesn't feature I can't imagine index_getattr
being a big bottleneck. Any ideas what's going on here?

Other interesting features:
- ~4 memory allocations per tuple, nearly all of which were explicitly
freed
- Things I though would be expensive, like: heapgettup and
myFunctionCall2 didn't really count for much.

Have a nice weekend,

% cumulative self self total
time seconds seconds calls s/call s/call name
43.63 277.81 277.81 95370055 0.00 0.00 comparetup_index
16.24 381.24 103.43 5920592 0.00 0.00 tuplesort_heap_siftup
3.76 405.17 23.93 95370055 0.00 0.00 inlineApplySortFunction
3.18 425.42 20.26 95370056 0.00 0.00 btint4cmp
2.82 443.37 17.95 11856219 0.00 0.00 AllocSetAlloc
2.52 459.44 16.07 95370055 0.00 0.00 myFunctionCall2
1.71 470.35 10.91 2960305 0.00 0.00 heapgettup
1.26 478.38 8.03 11841204 0.00 0.00 GetMemoryChunkSpace
1.14 485.67 7.29 5920592 0.00 0.00 tuplesort_heap_insert
1.11 492.71 7.04 2960310 0.00 0.00 index_form_tuple
1.09 499.67 6.96 11855105 0.00 0.00 AllocSetFree
0.97 505.83 6.17 23711355 0.00 0.00 AllocSetFreeIndex
0.84 511.19 5.36 5920596 0.00 0.00 LogicalTapeWrite
0.84 516.51 5.33 2960314 0.00 0.00 slot_deform_tuple
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.