> 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
