Re: Speeding up DELETEs on table with FKs ...

On Sun, 10 Oct 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)postgresql(dot)org> 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: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664