From: Mark Lewis <mark.lewis@mir3.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Arnaud Lesauvage <thewild@freesurf.fr>
Cc: Ragnar <gnari@hive.is>
Cc: Jens Schipkowski <jens.schipkowski@apus.co.at>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow update with simple query
Date: Thu, 14 Dec 2006 08:34:55 -0800
Message-ID: <1166114095.27428.133.camel@archimedes> (raw)
In-Reply-To: <21233.1166113140@sss.pgh.pa.us>
References: <457FDB1E.6040801@freesurf.fr>
<op.tkhy4jpg81rjf6@xjens.apus.local>
<457FF0CD.4050606@freesurf.fr>
<op.tkh10qgw81rjf6@xjens.apus.local>
<4580025D.2040903@freesurf.fr>
<1166021340.6369.103.camel@localhost.localdomain>
<45801A1B.6070200@freesurf.fr>
<8399.1166028385@sss.pgh.pa.us>
<4581593E.8080401@freesurf.fr>
<20426.1166109494@sss.pgh.pa.us>
<45816C54.5010602@freesurf.fr>
<21233.1166113140@sss.pgh.pa.us>
Out of curiosity, how hard would it be to modify the output of EXPLAIN
ANALYZE when doing an insert/update to include the index update times
and/or non-FK constraint checking times and/or the table row update
times? Or any other numbers that might be useful in circumstances like
this. I'm wondering if it's possible to shed some light on the
remaining dark shadows of PG performance troubleshooting.
-- Mark Lewis
On Thu, 2006-12-14 at 11:19 -0500, Tom Lane wrote:
> Arnaud Lesauvage <thewild@freesurf.fr> writes:
> > Tom Lane a crit :
> >> It seems the time must be going into this trigger function. What
> >> does it do?
>
> > A lot of things ! Indeed, if it runs it will very badly hurt performances (table
> > lookups, string manipulation, etc...) !
> > But it should only be tringered on INSERTs, and I am doing an UPDATE !
>
> Doh, right, I obviously still need to ingest more caffeine this morning.
>
> I think the conclusion must be that there was just too much I/O to be
> done to update all the rows. Have you done any tuning of shared_buffers
> and so forth? I recall having seen cases where update performance went
> bad as soon as the upper levels of a large index no longer fit into
> shared_buffers ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
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-performance@postgresql.org
Cc: mark.lewis@mir3.com, tgl@sss.pgh.pa.us, thewild@freesurf.fr, gnari@hive.is, jens.schipkowski@apus.co.at
Subject: Re: Slow update with simple query
In-Reply-To: <1166114095.27428.133.camel@archimedes>
* 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