Re: GiST index performance - Mailing list pgsql-performance

On Thu, 11 Jun 2009, Tom Lane wrote:
>> Oprofile scares me with the sheer number of options.
>
> You can ignore practically all of them; the defaults are pretty sane.

Thanks, that was helpful. Here is the top of opreport --long-filenames:

CPU: Core 2, speed 1998 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count
100000
CPU_CLK_UNHALT...|
   samples|      %|
------------------
   5114464 61.5404 /lib/libc-2.9.so
   1576829 18.9734 /changeable/pgsql_8.4_profiling/bin/postgres
         CPU_CLK_UNHALT...|
           samples|      %|
         ------------------
           1572346 99.7157 /changeable/pgsql_8.4_profiling/bin/postgres
              4482  0.2842 [vdso] (tgid:13593 range:0x7fff8dbff000-0x7fff8dc00000)
                 1 6.3e-05 [vdso] (tgid:13193 range:0x7fff8dbff000-0x7fff8dc00000)
    409534  4.9278 /no-vmlinux
    309990  3.7300 /changeable/pgsql_8.4_profiling/lib/btree_gist.so
    203684  2.4509 /changeable/pgsql_8.4_profiling/lib/bioseg.so

So it seems that btree_gist and bioseg are not using that much CPU at all,
compared to core postgres code. In fact, the majority of time seems to be
spent in libc. Unfortunately my libc doesn't have any debugging symbols.

Anyway, running opannotate seems to make it clear that time *is* spent in
the gistnext function, but almost all of that is in children of the
function. Lots of time is actually spent in fmgr_oldstyle though.

Here is the top of opreport -l:

CPU: Core 2, speed 1998 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count
100000
samples  %        image name               app name                 symbol name
5114464  61.5404  libc-2.9.so              libc-2.9.so              (no symbols)
496215    5.9708  postgres                 postgres                 gistnext
409534    4.9278  no-vmlinux               no-vmlinux               (no symbols)
404037    4.8616  postgres                 postgres                 fmgr_oldstyle
170079    2.0465  btree_gist.so            btree_gist.so            gbt_int4_consistent
160016    1.9254  postgres                 postgres                 gistdentryinit
153266    1.8442  nvidia_drv.so            nvidia_drv.so            (no symbols)
152463    1.8345  postgres                 postgres                 FunctionCall5
149374    1.7974  postgres                 postgres                 FunctionCall1
131112    1.5776  libxul.so                libxul.so                (no symbols)
120871    1.4544  postgres                 postgres                 .plt
94506     1.1372  bioseg.so                bioseg.so                bioseg_gist_consistent

I'm guessing my next step is to install a version of libc with debugging
symbols?

Matthew

--
 Some people, when confronted with a problem, think "I know, I'll use regular
 expressions." Now they have two problems.                  -- Jamie Zawinski