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

On Sun, 10 Oct 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@postgresql.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;
QUERYPLAN
 

-----------------------------------------------------------------------------------------------------------------------------------
IndexScan 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
 
---------------------------------------------------------------------------------------------------------------------
MergeJoin  (cost=0.00..29829.28 rows=250567 width=37)   Merge Cond: ("outer".raw_avl_id = "inner".raw_avl_id)   ->
IndexScan using ind_raw_avl_id on detail bda  (cost=0.00..8456.34 rows=250567 width=12)   ->  Index Scan using
pk_detail_rawon 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@hub.org           Yahoo!: yscrappy              ICQ: 7615664