On 30.08.2011 12:08, Heikki Linnakangas wrote:
> What's going on here? This data set was large enough to not fit in RAM,
> the table was about 8.5 GB in size (and I think the index is even larger
> than that), and the box has 4GB of RAM. Does the buffering only help
> with even larger indexes that exceed the cache size even more?
The tests are still running, so I decided to try oprofile. The build is
in the final emptying phase, according to the log, and has been for over
half an hour now. Oprofile output looks very interesting:
samples % image name symbol name
228590 30.3045 postgres pg_qsort
200558 26.5882 postgres gistBuffersFreeBlocksCmp
49397 6.5486 postgres gistchoose
45563 6.0403 postgres gist_box_penalty
31425 4.1661 postgres AllocSetAlloc
24182 3.2058 postgres FunctionCall3Coll
22671 3.0055 postgres rt_box_union
20147 2.6709 postgres gistpenalty
17007 2.2546 postgres DirectFunctionCall2Coll
15790 2.0933 no-vmlinux /no-vmlinux
14148 1.8756 postgres XLogInsert
10612 1.4068 postgres gistdentryinit
10542 1.3976 postgres MemoryContextAlloc
9466 1.2549 postgres FunctionCall1Coll
9190 1.2183 postgres gist_box_decompress
6681 0.8857 postgres med3
4941 0.6550 libc-2.12.so isnanf
So, over 50% of the CPU time is spent in choosing a block from the
temporary files. That should be pretty easy to improve..
-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com