pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Frost <jeff@frostconsultingllc.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: pl/pgsql function spikes CPU 100%
Date: Fri, 04 May 2007 00:15:20 -0400
Message-ID: <10129.1178252120@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.64.0705032021230.7932@discord.home.frostconsultingllc.com>
References: <Pine.LNX.4.64.0703160147310.11964@discord.home.frostconsultingllc.com>
	<6522.1174056565@sss.pgh.pa.us>
	<Pine.LNX.4.64.0703160820380.12217@discord.home.frostconsultingllc.com>
	<Pine.LNX.4.64.0703160827490.12217@discord.home.frostconsultingllc.com>
	<Pine.LNX.4.64.0704232306500.9190@discord.home.frostconsultingllc.com>
	<24506.1177396393@sss.pgh.pa.us>
	<Pine.LNX.4.64.0705032021230.7932@discord.home.frostconsultingllc.com>

Jeff Frost <jeff@frostconsultingllc.com> writes:
> Got my oprofile man page reading done.  Here's the general opreport:
> ...
> And the postgres one:
> samples  %        symbol name
> 3894022  12.6488  LWLockAcquire
> 3535497  11.4842  slot_deform_tuple
> 3030280   9.8431  LWLockRelease
> 2279699   7.4050  HeapTupleSatisfiesSnapshot
> 1782097   5.7887  ExecMakeFunctionResultNoSets
> 1209262   3.9280  ExecEvalScalarVar
> 1128461   3.6655  heap_release_fetch
> 916447    2.9768  LockBuffer
> 898437    2.9183  _bt_checkkeys
> 887904    2.8841  btgettuple
> 728377    2.3660  slot_getattr
> 648121    2.1053  hash_search
> 608352    1.9761  _bt_restscan
> 574699    1.8668  index_getnext
> 570999    1.8547  _bt_step
> 517225    1.6801  FunctionCall2
> 501845    1.6301  _bt_next
> 452142    1.4687  IndexNext
> 426455    1.3852  PinBuffer

It seems to basically be spending its time scanning tuples and applying
some function or other (probably a WHERE clause).  The btree references
suggest that the scan is an indexscan and not a brute-force seqscan, but
still I wonder if the problem isn't that the planner has switched to some
plan much less efficient than the one it was using before.  oprofile is
too low-level to give us much info about that, unfortunately.

Probably your next step should be to investigate the plans being used
for the queries in the slow functions.  See the archives for hints, eg
http://archives.postgresql.org/pgsql-performance/2006-06/msg00301.php

			regards, tom lane



view thread (10+ messages)

Message-ID: <10129.1178252120@sss.pgh.pa.us>
Permalink:  ../10129.1178252120@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/10129.1178252120@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jeff@frostconsultingllc.com
  Subject: Re: pl/pgsql function spikes CPU 100%
  In-Reply-To: <10129.1178252120@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox