From: Volkan YAZICI <yazicivo@ttmail.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Unexpectedly Long DELETE Wait
References: <874p5xwe3g.fsf@alamut.mobiliz.com.tr>
	<489AAB08.9080409@archonet.com>
Date: Thu, 07 Aug 2008 17:36:28 +0300
In-Reply-To: <489AAB08.9080409@archonet.com> (Richard Huxton's message of
	"Thu, 07 Aug 2008 08:58:00 +0100")
Message-ID: <877iasvqmr.fsf@alamut.mobiliz.com.tr>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.0.60 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

On Thu, 07 Aug 2008, Richard Huxton <dev@archonet.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.