On Sun, 10 Oct 2004, Tom Lane wrote:
"Marc G. Fournier" <[EMAIL PROTECTED]> writes:I posted to -sql the other day about an atrociously slow DELETE on a table that has two FKs to a 'parent' table ... if the # of records in the table that match the condition is 1, its fast ... in the sample I'm working with, there are 1639 records in the table ...
"parent" table? A DELETE doesn't check FKs in the table it's deleting. What it checks are FKs in other tables that reference items in the deletion table. You sure you are worrying about the right set of FKs?
'k, now that I've seen the error of my ways *groan* I've gone back through, and checked for what is referencing that table, and there is only one place that is, and it does have an INDEX:
explain analyze select * from table where raw_id = 20722;
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Index Scan using ind_raw_id on table (cost=0.00..3.09 rows=1 width=122) (actual time=0.33..0.33 rows=0 loops=1) Index Cond: (raw_id = 20722::numeric) Total runtime: 0.37 msec (3 rows)
and raw_id is the primary key in the table that I'm trying to run the delete on, and an EXPLAIN ANALYZE for that one shows slightly slower, but similar results (it a much bigger table) ...
And, doing a join of the two tables based on raw_id shows that the indices are being used:
explain select bdar.detail_id from detail bda,detail_raw bdar where bdar.raw_avl_id = bda.raw_avl_id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Merge Join (cost=0.00..29829.28 rows=250567 width=37)
Merge Cond: ("outer".raw_avl_id = "inner".raw_avl_id)
-> Index Scan using ind_raw_avl_id on detail bda (cost=0.00..8456.34 rows=250567
width=12)
-> Index Scan using pk_detail_raw on detail_raw bdar (cost=0.00..16941.06
rows=269349 width=25)
(4 rows)Now, the DELETE query that I'm trying to run is to delete 9997 rows from the table, so that means 9997 checks to detail as well, to make sure raw_id isn't being used, correct?
Am I in the right ballpark now with this? Or am I still totally lost?
---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
