From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Karim A Nassar <Karim.Nassar@NAU.EDU>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete query takes exorbitant amount of time
Date: Tue, 29 Mar 2005 16:24:40 +0100
Message-ID: <1112109880.11750.977.camel@localhost.localdomain> (raw)
In-Reply-To: <21616.1112108201@sss.pgh.pa.us>
References: <Pine.SOL.4.21.0503290120550.5642-100000@coruscant.cet.nau.edu>
<1112095776.11750.958.camel@localhost.localdomain>
<20050329054714.V50612@megazone.bigpanda.com>
<20050329061837.A51620@megazone.bigpanda.com>
<21616.1112108201@sss.pgh.pa.us>
On Tue, 2005-03-29 at 09:56 -0500, Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > If there were some way to pass a "limit" into SPI_prepare that was treated
> > similarly to a LIMIT clause for planning purposes but didn't actually
> > change the output plan to only return that number of rows, we could use
> > that.
>
> Hmm ... the planner does have the ability to do that sort of thing (we
> use it for cursors). SPI_prepare doesn't expose the capability.
> Perhaps adding a SPI_prepare variant that does expose it would be the
> quickest route to a solution.
>
> I get a headache every time I look at the RI triggers ;-). Do they
> always know at the time of preparing a plan which way it will be used?
If action is NO ACTION or RESTRICT then
we need to SELECT at most 1 row that matches the criteria
which means we can use LIMIT 1
If action is CASCADE, SET NULL, SET DEFAULT then
we need to UPDATE or DELETE all rows that match the criteria
which means we musnt use LIMIT and need to use FOR UPDATE
We know that at CONSTRAINT creation time, which always occurs before
plan preparation time.
Best Regards, Simon Riggs
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: simon@2ndquadrant.com, tgl@sss.pgh.pa.us, sszabo@megazone.bigpanda.com, Karim.Nassar@NAU.EDU, chriskl@familyhealth.com.au
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <1112109880.11750.977.camel@localhost.localdomain>
* 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