Re: DELETE FROM t WHERE EXISTS - Mailing list pgsql-sql

From Dan Langille
Subject Re: DELETE FROM t WHERE EXISTS
Date
Msg-id 20030228165055.J29368@xeon.unixathome.org
Whole thread Raw
In response to Re: DELETE FROM t WHERE EXISTS  ("Tomasz Myrta" <jasiek@klaster.net>)
List pgsql-sql
On Fri, 28 Feb 2003, Tomasz Myrta 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.
> >
> > 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?

> delete from clp where commit_date < (select commit_date from clp order
> by commit_date limit 1 offset 100); Regards, Tomasz Myrta

Thank you.  That does it well.  And it also revealed an error in my
original SQL:  a mising "desc".  Here's the plan FYI:

Seq Scan on clp  (cost=0.00..2.26 rows=34 width=6) (actual
time=8.55..11.92 rows=399 loops=1)  Filter: (commit_date < $0)  InitPlan    ->  Limit  (cost=5.62..5.62 rows=1 width=8)
(actualtime=7.58..7.60
 
rows=1 loops=1)          ->  Sort  (cost=5.37..5.62 rows=101 width=8) (actual
time=6.75..7.17 rows=102 loops=1)                Sort Key: commit_date                ->  Seq Scan on clp
(cost=0.00..2.01rows=101 width=8)
 
(actual time=0.26..3.93 rows=500 loops=1)Total runtime: 15.86 msec



pgsql-sql by date:

Previous
From: Dan Langille
Date:
Subject: Re: DELETE FROM t WHERE EXISTS
Next
From: "Montrone, Marc -- 7183"
Date:
Subject: Optimizing