Volkan YAZICI wrote:
Hi,
Below command has been running since ~700 minutes in one of our
PostgreSQL servers.
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.
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.
--
Richard Huxton
Archonet Ltd
