Re: Delete query takes exorbitant amount of time - Mailing list pgsql-performance

From Karim A Nassar
Subject Re: Delete query takes exorbitant amount of time
Date
Msg-id Pine.SOL.4.21.0503281231580.1035-100000@coruscant.cet.nau.edu
Whole thread Raw
In response to Re: Delete query takes exorbitant amount of time  (Simon Riggs <simon@2ndquadrant.com>)
List pgsql-performance
> 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?

> IndexScan, value=1    elapsed= 29ms    cost=883881

190 * 29ms is much less than 40 minutes. What am I missing here?


> 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.


Continued thanks,
--
Karim Nassar
Department of Computer Science
Box 15600, College of Engineering and Natural Sciences
Northern Arizona University,  Flagstaff, Arizona 86011
Office: (928) 523-5868 -=- Mobile: (928) 699-9221





pgsql-performance by date:

Previous
From: Karim A Nassar
Date:
Subject: Re: Delete query takes exorbitant amount of time
Next
From: Karim A Nassar
Date:
Subject: Re: Delete query takes exorbitant amount of time