Skip site navigation (1) Skip section navigation (2)

Re: [pgsql-performance] Deletes from tables with foreign keys taking

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Adam Siegel <adam(at)sycamorehq(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: [pgsql-performance] Deletes from tables with foreign keys taking
Date: 2002-10-01 18:27:56
Message-ID: 20021001112632.F96255-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
On Tue, 1 Oct 2002, Adam Siegel wrote:

> I have a table that has about 200 rows in it.  I have 2 other tables
> that have about 300,000 rows each that reference the first table
> through a foriegn key.  I run a process that rebuilds these tables.
> First I delete the rows in the large tables (takes about 30 seconds),
> then I delete the the rows in the first table (takes about 5 minutes
> !!!).  Each of these are done in separate transactions.
>
> If I do a vacuum analyze on each of the large tables just after the
> delete then deleting the rows from the first table takes just a second
> or two.  My guess is that postgres is still check the foriegn keys
> from the first table to the others even though the records are deleted
> in the larger tables.  The vacuum cleans up the deleted records, so it
> goes faster.  Am I wrong.  Any ideas?

That seems reasonable.  It's still going to be doing some action on those
tables and it's going to have to scan the tables in some case.  It's wierd
that it's taking that long to do it in any case however, what does the
schema for the tables look like?




In response to

pgsql-performance by date

Next:From: Adam SiegelDate: 2002-10-01 18:28:04
Subject: Deletes from tables with foreign keys taking too long
Previous:From: Andrew SullivanDate: 2002-09-28 19:13:18
Subject: Re: INDEX

pgsql-general by date

Next:From: Adam SiegelDate: 2002-10-01 18:28:04
Subject: Deletes from tables with foreign keys taking too long
Previous:From: rocoDate: 2002-10-01 18:08:52
Subject: Fwd: Fwd: FATAL 1: Database dialup does not exist in pg_database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group