Message-ID: <489AAB08.9080409@archonet.com>
Date: Thu, 07 Aug 2008 08:58:00 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.16 (X11/20080707)
MIME-Version: 1.0
To: Volkan YAZICI <yazicivo@ttmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Unexpectedly Long DELETE Wait
References: <874p5xwe3g.fsf@alamut.mobiliz.com.tr>
In-Reply-To: <874p5xwe3g.fsf@alamut.mobiliz.com.tr>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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