From: PFC <lists@peufeu.com>
To: postgres@leangen.net
To: pgsql-performance@postgresql.org
Subject: Re: Delete operation VERY slow...
Date: Fri, 16 Jun 2006 13:23:15 +0200
Message-ID: <op.ta8kk1aqcigqcu@apollo13> (raw)
In-Reply-To: <1150445542.4812.34.camel@sonoda.bioscene.co.jp>
References: <1150441126.4812.23.camel@sonoda.bioscene.co.jp>
<674d1f8a0606160022p1b042676gba28553bac5ab5ea@mail.gmail.com>
<1150445542.4812.34.camel@sonoda.bioscene.co.jp>
> Wow! That was almost instantaneous. I can't believe the difference.
>
> The only inconvenience is that I need to remove all the foreign key
> constraints before truncating, then put them back after. But I suppose
> it is a small price to pay for this incredible optimization.
In that case, your DELETE might have been slowed down by foreign key
checks.
Suppose you have tables A and B, and table A has a column "b_id
REFERENCES B(id)"
When you delete from B postgres has to lookup in A which rows reference
the deleted rows in order to do the ON DELETE action you specified in the
constraint.
If you do not have an index on b_id, this can be quite slow... so you
should check if your foreign key relations that need indexes have them.
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: lists@peufeu.com, postgres@leangen.net
Subject: Re: Delete operation VERY slow...
In-Reply-To: <op.ta8kk1aqcigqcu@apollo13>
* 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