From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Volkan YAZICI <yazicivo(at)ttmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Unexpectedly Long DELETE Wait |
Date: | 2008-08-07 07:58:00 |
Message-ID: | 489AAB08.9080409@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2008-08-07 08:33:57 | Re: Plz Heeeelp! performance settings |
Previous Message | Giorgio Valoti | 2008-08-07 07:50:04 | Query Plan choice with timestamps |