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

Re: Delete query takes exorbitant amount of time

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Karim A Nassar <Karim(dot)Nassar(at)NAU(dot)EDU>,Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>,pgsql-performance(at)postgresql(dot)org
Subject: Re: Delete query takes exorbitant amount of time
Date: 2005-03-29 13:50:42
Message-ID: 20050329054714.V50612@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, 29 Mar 2005, Simon Riggs wrote:

> On Tue, 2005-03-29 at 01:48 -0700, Karim A Nassar wrote:
> > > Each value has 1/13th of the table, which is too many rows per value to
> > > make an IndexScan an efficient way of deleting rows from the table.
> >
> > But, the original question was that the delete that was taking a long time
> > was on a different table. I tried to delete 150 rows from a table with 750
> > rows, which is FK referenced from this large table. If I understand
> > correctly, Tom suggested that the length of time was due to a sequential
> > scan being done on the large table for each value being deleted from the
> > small one.
>
> > 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?
>
> My understanding was that you were doing a DELETE on the smaller table
> and that this was doing a DELETE on the measurement table because you
> had the FK defined as ON DELETE CASCADE. You are right - only a single
> row is sufficient to RESTRICT the DELETE. But for an ON UPDATE/DELETE
> action of CASCADE then you will want to touch all rows referenced, so a
> SeqScan is a perfectly valid consequence of such actions.
> I think now that you are using the default action, rather than
> specifically requesting CASCADE?
>
> Stephan, Tom:
> The SQL generated for RI checking by the RI triggers currently applies a
> limit at execution time, not at prepare time. i.e. there is no LIMIT
> clause in the SQL.
>
> We know whether the check will be limit 1 or limit 0 at prepare time, so
> why not add a LIMIT clause to the SQL so it changes the plan, not just
> the number of rows returned when the check query executes?

Because IIRC, FOR UPDATE and LIMIT at least historically didn't play
nicely together, so you could sometimes get a result where if the first
row was locked, the FOR UPDATE would wait on it, but if it was deleted by
the other transaction you could get 0 rows back in the trigger.


In response to

Responses

pgsql-performance by date

Next:From: Stephan SzaboDate: 2005-03-29 14:20:25
Subject: Re: Delete query takes exorbitant amount of time
Previous:From: Dave CramerDate: 2005-03-29 12:17:05
Subject: Re: How to improve db performance with $7K?

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