Relyea, Mike wrote:
I am trying to optimize a delete query that's currently taking 4 hours
to run. My first step was to add some indexes on some of my FK's. That
sped up my select queries by as much as ~20x. From 3.5 sec to < 0.25
sec. They didn't help my delete query. I ran an explain analyze on the
delete query and the records got deleted after 4 hours but it never
showed me the query plan. I'm using 8.0.1 on WinXP accessing it with
pgAdminIII.
FWIW, the DB has a lot of FK's and some of them (the ones on my big
tables 1-10 million records) have ON DELETE CASCADE triggers.
Any suggestions for how to get the explain analyze output?
You should get *some* output. Unfortunately, I don't think it will show
you anything useful. The effort is almost certainly all going on the
FK's and you can't see through the trigger code to see what's happening
there.
If this is an occasional, manual type thing it can be quicker to:
1. start transaction
2. lock relevant tables
3. delete from dependant tables
4. delete from "main" table
5. commit
Of course, its practicality depends on how straightforward it is to word
the WHERE clause in step 3.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
