Re: not exactly a bug report, but surprising behaviour

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > I'm watching this query I'm working on now drive the cpu to 100% for 6+ hours
> > with virtually no I/O. And I think it's the best I can do. All the times is
> > being spent moving bits around from one place to another.
>
> It would be interesting to see a gprof profile of that.

Ok, I have a copy of postgres built with -pg now. I'm running grpof on the
gmon.out file but all I'm seeing is this:

% cumulative self self total
time seconds seconds calls ms/call ms/call name
100.00 0.01 0.01 1 10.00 10.00 InitFreeSpaceMap
0.00 0.01 0.00 12296 0.00 0.00 LWLockAssign
0.00 0.01 0.00 323 0.00 0.00 GUC_yylex
0.00 0.01 0.00 123 0.00 0.00 ShmemAlloc
0.00 0.01 0.00 123 0.00 0.00 tas
0.00 0.01 0.00 79 0.00 0.00 AllocSetFreeIndex
...

I assume this is only the postmaster initialization as I've run a smaller
version of the job which took 8 minutes. Is there an easy trick to getting
gmon.out output from a specific backend process?

> > It occurs to me that it's possible postgres is doing this already at a lower
> > level of abstraction.
>
> Yes, tuplesort.c has heard of pushing pointers around rather than
> copying records. I wonder though whether data is being pushed out to
> kernel disk buffers and then back in again --- do you have sort_mem
> set large enough?

Yes, this is the job I asked about sort_mem for. I have it up to 64M and no
pgsql_tmp files are being created.

> Another likely theory is that the interface layers needed to access
> datatype-specific comparison routines are chewing the cycles. Need facts not
> speculation to know where the bottleneck is...

--
greg