On Thu, 07 Aug 2008, Richard Huxton <dev@xxxxxxxxxxxx> writes: > Volkan YAZICI wrote: >> DELETE FROM mugpsreglog >> WHERE NOT EXISTS (SELECT 1 >> FROM mueventlog >> WHERE mueventlog.eventlogid = mugpsreglog.eventlogid); >> >> Seq Scan on mugpsreglog (cost=0.00..57184031821394.73 rows=6590986 width=6) >> Filter: (NOT (subplan)) >> SubPlan >> -> Seq Scan on mueventlog (cost=0.00..4338048.00 rows=1 width=0) >> Filter: (eventlogid = $0) > > Ouch - look at the estimated cost on that! > >> And there isn't any constraints (FK/PK), triggers, indexes, etc. on any >> of the tables. (We're in the phase of a migration, many DELETE commands >> similar to above gets executed to relax constraints will be introduced.) > > Well there you go. Add an index on eventlogid for mugpsreglog. Hrm... Adding an INDEX on "eventlogid" column of "mueventlog" table solved the problem. Anyway, thanks for your kindly help. > Alternatively, if you increased your work_mem that might help. Try SET > work_mem='64MB' (or even higher) before running the explain and see if it tries > a materialize. For situations like this where you're doing big one-off queries > you can afford to increase resource limits. None of 64MB, 128MB, 256MB and 512MB settings make a change in the query plan. Regards.
