From: Simon Riggs <simon@2ndquadrant.com>
To: Karim A Nassar <Karim.Nassar@NAU.EDU>
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: Mon, 28 Mar 2005 22:07:25 +0100
Message-ID: <1112044045.11750.919.camel@localhost.localdomain> (raw)
In-Reply-To: <Pine.SOL.4.21.0503281231580.1035-100000@coruscant.cet.nau.edu>
References: <Pine.SOL.4.21.0503281231580.1035-100000@coruscant.cet.nau.edu>
On Mon, 2005-03-28 at 13:03 -0700, Karim A Nassar wrote:
> > Well, based upon the evidence so far, the Optimizer got it right:
>
> Agreed. So, this means that the answer to my original question is "that
> delete gonna take a long time"?
>
> Seems that there is still something wrong. From what I can tell from
> everyones questions, the FK constraint on measurement is causing multiple
> seq scans for each value deleted from int_sensor_meas_type. However, when
> deleting a single value, the FK check should use the index, so my ~190
> deletes *should* be fast, no?
No.
> > IndexScan, value=1 elapsed= 29ms cost=883881
>
> 190 * 29ms is much less than 40 minutes. What am I missing here?
It all depends upon your data.
There are *no* values in *your* table that take 29ms to delete...
> > Karim,
> > Please do:
> >
> > select id_int_sensor_meas_type, count(*)
> > from measurement
> > group by id_int_sensor_meas_type
> > order by count(*) desc;
>
> id_int_sensor_meas_type | count
> -------------------------+--------
> 31 | 509478
> 30 | 509478
> 206 | 509478
> 205 | 509478
> 204 | 509478
> 40 | 509478
> 39 | 509478
> 197 | 509478
> 35 | 509478
> 34 | 509478
> 33 | 509478
> 32 | 509478
> 41 | 509477
>
> This sample dataset has 13 measurements from a weather station over 3
> years, hence the even distribution.
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.
Thats it.
If you have more values when measurement is bigger, the delete will
eventually switch plans (if you reconnect) and use the index. But not
yet.
There's a few ways to (re)design around it, but the distribution of your
data is not *currently* conducive to the using an index.
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, Karim.Nassar@NAU.EDU, 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: <1112044045.11750.919.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