pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: andrew@pillette.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Unbearably slow cascading deletes
Date: Wed, 21 Jul 2004 09:37:52 +0800
Message-ID: <40FDC8F0.30102@familyhealth.com.au> (raw)
In-Reply-To: <200407201919.i6KJJBw08728@pillette.com>
References: <200407201919.i6KJJBw08728@pillette.com>

> I have (among other things) a parent table with 200 records and a child table with 20MM or more. I set up referential integrity on the FK with ON DELETE CASCADE.
> 
> It appears that when a DELETE is done on the parent table, the child table deletion is done with a sequential scan. I say this because it took over four minutes to delete a parent record THAT HAD NO CHILDREN. The DB is recently analyzed and SELECTs in the child table are done by the appropriate index on the FK.

Do you have an index on the foreign key field?

Chris




view thread (6+ messages)

Message-ID: <40FDC8F0.30102@familyhealth.com.au>
Permalink:  ../40FDC8F0.30102@familyhealth.com.au/
Also on:    postgresql.org/message-id/40FDC8F0.30102@familyhealth.com.au

 · 

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: chriskl@familyhealth.com.au, andrew@pillette.com
  Subject: Re: Unbearably slow cascading deletes
  In-Reply-To: <40FDC8F0.30102@familyhealth.com.au>

* 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