Обсуждение: Explain analyze gives no info
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? Mike
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
Richard Huxton <dev@archonet.com> writes: > Relyea, Mike wrote: >> 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. Yeah. CVS-tip EXPLAIN ANALYZE will at least tell you how much time was chewed by each trigger, but it still sees each trigger as a black box. However, I don't think we need to guess very hard about what's going on here. Obviously, one of the ON DELETE foreign key triggers is scanning the referencing table with a seqscan, which probably means that either there's no index at all on the referencing column, or the referencing column is of a different datatype than the master. Look through the tables that reference this one and check the indexes and datatypes. regards, tom lane