X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 04691475AE4
	for <pgsql-sql@postgresql.org>; Fri, 28 Feb 2003 18:50:08 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 84250-02 for <pgsql-sql@postgresql.org>;
	Fri, 28 Feb 2003 18:50:05 -0500 (EST)
Received: from xeon.unixathome.org (bast.unixathome.org [66.11.174.150])
	by postgresql.org (Postfix) with ESMTP id 7000647616F
	for <pgsql-sql@postgresql.org>; Fri, 28 Feb 2003 16:58:41 -0500 (EST)
Received: by xeon.unixathome.org (Postfix, from userid 1000)
	id 8E3C33F4A; Fri, 28 Feb 2003 16:58:34 -0500 (EST)
Received: from localhost (localhost [127.0.0.1])
	by xeon.unixathome.org (Postfix) with ESMTP
	id 874473F49; Fri, 28 Feb 2003 16:58:34 -0500 (EST)
Date: Fri, 28 Feb 2003 16:58:34 -0500 (EST)
From: Dan Langille <dan@langille.org>
X-X-Sender: dan@xeon.unixathome.org
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: DELETE FROM  t WHERE EXISTS
In-Reply-To: <20030228123036.L2988-100000@megazone23.bigpanda.com>
Message-ID: <20030228165800.R29368@xeon.unixathome.org>
References: <20030228123036.L2988-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/628
X-Sequence-Number: 12026

On Fri, 28 Feb 2003, Stephan Szabo wrote:

>
> On Fri, 28 Feb 2003, Dan Langille wrote:
>
> > Hi folks,
> >
> > I wanted to delete "old" rows from a table.  These are the rows I
> > want to keep:
> >
> > SELECT *
> > FROM clp
> > ORDER BY commit_date
> > LIMIT 100
> >
> > So I tried this:
> >
> > DELETE FROM clp
> > WHERE NOT EXISTS (
> > SELECT *
> > FROM clp
> > ORDER BY commit_date
> > LIMIT 100);
> >
> > Uhh uhh, nothing deleted.  I don't understand why.
>
> As long as the inner select returns at least 1 result NOT EXISTS is
> going to return false (you haven't correlated the two queries at all).
>
> > OK, I can do this instead:
> >
> > DELETE from clp
> > where commit_log_id NOT in (
> > SELECT commit_log_id
> > FROM clp
> > ORDER BY commit_date
> > LIMIT 100);
> >
> > Can you think of a better way?
>
> Possibly something like:
> DELETE FROM clp
>  WHERE NOT EXISTS (
>   select * from (select * from clp order by commit_date limit 100) tmp
>   where tmp.commit_log_id = clp.commit_log_id
>  );
>
> But I haven't tried it for stupid errors, and am not sure that it'd end up
> being any better than NOT IN anyway.

Thank you.  Here's that plan:

Seq Scan on clp  (cost=0.00..544.87 rows=50 width=6) (actual
time=93.71..763.85 rows=400 loops=1)
   Filter: (NOT (subplan))
   SubPlan
     ->  Subquery Scan tmp  (cost=5.37..5.62 rows=100 width=12) (actual
time=1.51..1.51 rows=0 loops=500)
           Filter: (commit_log_id = $0)
           ->  Limit  (cost=5.37..5.62 rows=100 width=12) (actual
time=0.03..1.09 rows=90 loops=500)
                 ->  Sort  (cost=5.37..5.62 rows=101 width=12) (actual
time=0.02..0.38 rows=91 loops=500)
                       Sort Key: commit_date
                       ->  Seq Scan on clp  (cost=0.00..2.01 rows=101
width=12) (actual time=0.23..3.88 rows=500 loops=1)
 Total runtime: 768.14 msec