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