Re: gprof SELECT COUNT(*) results

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Qingqing Zhou <zhouqq(at)cs(dot)toronto(dot)edu>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: gprof SELECT COUNT(*) results
Date: 2005-11-25 04:48:33
Message-ID: 18712.1132894113@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Qingqing Zhou <zhouqq(at)cs(dot)toronto(dot)edu> writes:
> Yeah, understood. What I can't understand that in this case why it costs
> so much -- without concurrency, the LWLock code path just invloves
> spinlock_lock/unlock and serveral simple instructions?

I don't see those costing nearly as much as your results show
... perhaps there's something platform-specific at work?
What I see, down to the 1% level, is

Each sample counts as 0.01 seconds.
% cumulative self self total
time seconds seconds calls ms/call ms/call name
37.98 13.91 13.91 _mcount
6.53 16.30 2.39 5242900 0.00 0.00 heapgettup
3.33 17.52 1.22 10542596 0.00 0.00 LockBuffer
3.30 18.73 1.21 5242880 0.00 0.00 advance_transition_function
2.68 19.71 0.98 5242880 0.00 0.00 IncrBufferRefCount
2.46 20.61 0.90 5385174 0.00 0.00 LWLockRelease
2.38 21.48 0.87 5271273 0.00 0.00 ReleaseAndReadBuffer
2.35 22.34 0.86 5385174 0.00 0.00 LWLockAcquire
2.18 23.14 0.80 5242938 0.00 0.00 ReleaseBuffer
2.10 23.91 0.77 5242900 0.00 0.00 ExecStoreTuple
1.97 24.63 0.72 noshlibs
1.91 25.33 0.70 5242900 0.00 0.00 SeqNext
1.72 25.96 0.63 5271294 0.00 0.00 ResourceOwnerRememberBuffer
1.72 26.59 0.63 5242900 0.00 0.00 heap_getnext
1.72 27.22 0.63 5242880 0.00 0.00 advance_aggregates
1.69 27.84 0.62 5242940 0.00 0.00 ExecProcNode
1.64 28.44 0.60 $$dyncall
1.61 29.03 0.59 5242900 0.00 0.00 MemoryContextReset
1.53 29.59 0.56 5242880 0.00 0.00 HeapTupleSatisfiesSnapshot
1.45 30.12 0.53 5242880 0.00 0.00 int8inc
1.37 30.62 0.50 5243140 0.00 0.00 ExecClearTuple
1.17 31.05 0.43 5242880 0.00 0.00 ExecEvalExprSwitchContext
1.15 31.47 0.42 5271294 0.00 0.00 ResourceOwnerForgetBuffer
1.12 31.88 0.41 SeqNext
1.09 32.28 0.40 5271294 0.00 0.00 ResourceOwnerEnlargeBuffers
1.09 32.68 0.40 5242900 0.00 0.00 ExecScan
1.04 33.06 0.38 5242900 0.00 0.00 ExecSeqScan

(This is for 20, not 10, iterations of your example, but otherwise it's
the same test case.)

I've since gotten rid of the IncrBufferRefCount, ReleaseBuffer,
and ResourceOwnerXXX entries by eliminating some inefficiency in
ExecStoreTuple, so that puts the buffer lock stuff further up,
but it's still not all that critical by my numbers.

> What's more, we can see that for each row, a LWLock pair is invoked. So on
> a more aggressive thought, can we change it to page level?

Yeah, I was wondering the same. It'd be possible to rewrite the seqscan
stuff so that we do the visibility tests for all the tuples on a given
page at once, taking the buffer content lock just once, and saving aside
the valid tuple IDs to return later. This should definitely be faster
when all the tuples actually get fetched. It might be a bit slower for
a LIMIT query, but I'm not sure if we care that much. The only other
objection I can think of is that if there are any broken tuples on a
page, this approach would likely make it impossible to fetch any of the
non-broken ones :-(

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Qingqing Zhou 2005-11-25 04:58:57 Re: gprof SELECT COUNT(*) results
Previous Message Qingqing Zhou 2005-11-25 04:02:37 Re: gprof SELECT COUNT(*) results