Re: WIP: Fast GiST index build

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