From: Bruno Wolff III <bruno@wolff.to>
To: Karim A Nassar <Karim.Nassar@NAU.EDU>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete query takes exorbitant amount of time
Date: Sun, 3 Apr 2005 07:18:20 -0500
Message-ID: <20050403121820.GB27923@wolff.to> (raw)
In-Reply-To: <Pine.SOL.4.21.0503290120550.5642-100000@coruscant.cet.nau.edu>
References: <1112044045.11750.919.camel@localhost.localdomain>
<Pine.SOL.4.21.0503290120550.5642-100000@coruscant.cet.nau.edu>
On Tue, Mar 29, 2005 at 01:48:48 -0700,
Karim A Nassar <Karim.Nassar@NAU.EDU> wrote:
>
> For this FK check, there only need be one referring id to invalidate the
> delete. ISTM that for any delete with a FK reference, the index could
> always be used to search for a single value in the referring table
> (excepting very small tables). Why then must a sequential scan be
> performed in this case, and/or in general?
First the index needs to exist. It isn't created automatically because not
everyone wants such an index. Second, you need to have analyzed the
referencing table so that the planner will know it is big enough that
using an indexed search is worthwhile. The planner is getting better
about dealing with size changes without reanalyzing, but it seems there
are still some gotchas in 8.0.
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: bruno@wolff.to, Karim.Nassar@NAU.EDU, simon@2ndquadrant.com, sszabo@megazone.bigpanda.com, tgl@sss.pgh.pa.us, chriskl@familyhealth.com.au
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <20050403121820.GB27923@wolff.to>
* 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