pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dave Cramer <pg@fastcrypt.com>
To: Alvaro Herrera <alvherre@dcc.uchile.cl>
Cc: David Gagnon <dgagnon@siunik.com>
Cc: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance problem on delete from for 10k rows.  May
Date: Wed, 16 Mar 2005 09:56:43 -0500
Message-ID: <4238492B.2000801@fastcrypt.com> (raw)
In-Reply-To: <20050316143511.GC21935@dcc.uchile.cl>
References: <42375281.9090102@siunik.com>
	<20050315233834.GA68209@winnie.fuhr.org>
	<4238322F.10302@siunik.com>
	<20050316143511.GC21935@dcc.uchile.cl>

Really? Postgres is generating these queries ???

Dave

Alvaro Herrera wrote:

>On Wed, Mar 16, 2005 at 08:18:39AM -0500, David Gagnon wrote:
>
>David,
>
>  
>
>>I rerun the example with the debug info turned on in postgresl. As you 
>>can see all dependent tables (that as foreign key on table IC) are 
>>emptied before the DELETE FROM IC statement is issued.  For what I 
>>understand the performance problem seem to came from those selects that 
>>point back to IC ( LOG:  statement: SELECT 1 FROM ONLY "public"."ic" x 
>>WHERE "icnum" = $1 FOR UPDATE OF x).  There are 6 of them.  I don't know 
>>where they are comming from.
>>    
>>
>
>I think they come from the FK checking code.  Try to run a VACUUM on the
>IC table just before you delete from the other tables; that should make
>the checking almost instantaneous (assuming the vacuuming actually
>empties the table, which would depend on other transactions).
>
>It would be better to be able to use TRUNCATE to do this, but in 8.0 you
>can't if the tables have FKs.  8.1 is better on that regard ...
>
>  
>

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561




view thread (12+ messages)  latest in thread

Message-ID: <4238492B.2000801@fastcrypt.com>
Permalink:  ../4238492B.2000801@fastcrypt.com/
Also on:    postgresql.org/message-id/4238492B.2000801@fastcrypt.com

 · 

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-performance@postgresql.org
  Cc: pg@fastcrypt.com, alvherre@dcc.uchile.cl, dgagnon@siunik.com, mike@fuhr.org
  Subject: Re: Performance problem on delete from for 10k rows.  May
  In-Reply-To: <4238492B.2000801@fastcrypt.com>

* 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