Re: gprof SELECT COUNT(*) results

On Thu, 2005-11-24 at 13:25 -0500, Qingqing Zhou wrote:
> I did some gprof on a simple "SELECT COUNT(*) FROM test" query on cvs tip.
>
> Linux josh.db 2.4.29-1 #2 Tue Jan 25 17:03:33 EST 2005 i686 unknown
> gcc: 2.96
> gprof: 2.13.90.0.2
> ./configure --without-readline
>
> There are 260k or so records in table test(i int), about 1500 pages. I
> give a shared_buffers to 3000, which is enough to hold all data pages.
> Other GUCs are by default. After some warmups (to make sure these pages
> are in the file system buffers), I do "SELECT COUNT(*)" for 10 times of
> each round, and I tested 3 rounds. The results are:
>
> - Round 1 -
> % cumulative self self total
> time seconds seconds calls s/call s/call name
> 16.67 0.27 0.27 2648542 0.00 0.00 LWLockAcquire
> 13.58 0.49 0.22 2648543 0.00 0.00 LWLockRelease
> 8.02 0.62 0.13 5266128 0.00 0.00 LockBuffer
> 8.02 0.75 0.13 2621456 0.00 0.00 heapgettup
>
> - Round 2 -
> % cumulative self self total
> time seconds seconds calls s/call s/call name
> 19.14 0.31 0.31 2648542 0.00 0.00 LWLockAcquire
> 13.58 0.53 0.22 2648543 0.00 0.00 LWLockRelease
> 11.11 0.71 0.18 2621456 0.00 0.00 heapgettup
> 6.79 0.82 0.11 5266128 0.00 0.00 LockBuffer
>
> - Round 3 -
> % cumulative self self total
> time seconds seconds calls s/call s/call name
> 17.12 0.25 0.25 2648542 0.00 0.00 LWLockAcquire
> 8.22 0.37 0.12 2648543 0.00 0.00 LWLockRelease
> 7.53 0.48 0.11 2621456 0.00 0.00 heapgettup
> 6.85 0.58 0.10 2621440 0.00 0.00 ExecEvalConst
>
> There are some variance in the results, so my question is:
> (1) Are these results faithful?
> (2) If so, does it indicate that LWLock needs some improvements?

Maybe, maybe not. The whole system is designed around high levels of
concurrent access. If you know for certain you don't ever need that then
other systems are probably the right choice. Concurrency has a cost and
a benefit. If you measure the cost, but not the benefit, it will seem
expensive.

Your results show you have 2.6m rows, not 260k rows. Yes?

Best Regards, Simon Riggs