From: | Volkan YAZICI <yazicivo(at)ttmail(dot)com> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Unexpectedly Long DELETE Wait |
Date: | 2008-08-07 14:36:28 |
Message-ID: | 877iasvqmr.fsf@alamut.mobiliz.com.tr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, 07 Aug 2008, Richard Huxton <dev(at)archonet(dot)com> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2008-08-07 15:05:30 | Re: Plz Heeeelp! performance settings |
Previous Message | ries van Twisk | 2008-08-07 14:30:48 | Another index related question.... |