Re: So, is COUNT(*) fast now?

On Fri, Oct 21, 2011 at 2:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think HeapTupleSatisfiesMVCC is probably being skipped anyway in
>> this case, since all the heap pages should be PD_ALL_VISIBLE.
>
> Proves my point ;-) ... you're comparing a code path that's been beat on
> for *years* with one that just got written.

I know. I wrote a chunk of it. :-) My point is just that it'd be
nice to make it better.

Anyhow, here's the scoop. On my desktop machine running F14, running
SELECT sum(1) FROM pgbench_accounts in a tight loop, 60 s worth of
oprofile data:

176830 13.0801 postgres postgres ExecProject
170028 12.5770 postgres postgres
IndexOnlyNext
96631 7.1478 postgres postgres
visibilitymap_test
86019 6.3628 postgres postgres
advance_aggregates
74366 5.5009 postgres postgres ExecScan
72428 5.3575 postgres postgres
ExecClearTuple
68483 5.0657 postgres postgres btgettuple
60614 4.4836 postgres postgres
advance_transition_function
59680 4.4145 postgres postgres ExecProcNode
52295 3.8683 postgres postgres
_bt_checkkeys
52078 3.8522 libc-2.12.90.so libc-2.12.90.so
__memcpy_sse2
49548 3.6651 postgres postgres
index_getnext_tid
48265 3.5702 postgres postgres
ExecEvalConst
42989 3.1799 postgres postgres _bt_next
40544 2.9990 postgres postgres _bt_readpage
35162 2.6009 no-vmlinux no-vmlinux /no-vmlinux
33639 2.4883 postgres postgres
MemoryContextReset

And without index-only scans. but everything in shared_buffers:

169515 18.4261 postgres postgres ExecProject
94827 10.3076 postgres postgres
heapgettup_pagemode
84850 9.2231 postgres postgres
advance_aggregates
57998 6.3043 postgres postgres
advance_transition_function
55638 6.0478 postgres postgres
ExecEvalConst
53684 5.8354 postgres postgres heapgetpage
51411 5.5883 postgres postgres ExecScan
48387 5.2596 postgres postgres ExecProcNode
44129 4.7968 postgres postgres
ExecStoreTuple
30759 3.3435 postgres postgres heap_getnext
25923 2.8178 postgres postgres SeqNext
24145 2.6245 postgres postgres
CheckForSerializableConflictOut
23155 2.5169 postgres postgres ExecAgg
18864 2.0505 postgres postgres
heap_page_prune_opt
18784 2.0418 no-vmlinux no-vmlinux /no-vmlinux

The index-only scan takes about 385 ms, while the non-index-only
version takes about 284 ms.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company