Skip site navigation (1) Skip section navigation (2)

Re: [PERFORMANCE] slow small delete on large table

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Ed L(dot)" <pgsql(at)bluepolka(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORMANCE] slow small delete on large table
Date: 2004-02-24 05:23:58
Message-ID: 18315.1077600238@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Ed L." <pgsql(at)bluepolka(dot)net> writes:
> If I could say it the way I think for a simple example, it'd be
> like this:

> 	delete from mytable
> 	where posteddatetime < now() - '90 days'
> 	limit 100;

> Of course, that's not legal 7.3.4 syntax.

Assuming you have a primary key on the table, consider this:

CREATE TEMP TABLE doomed AS
  SELECT key FROM mytable WHERE posteddatetime < now() - '90 days'
  LIMIT 100;

DELETE FROM mytable WHERE key = doomed.key;

DROP TABLE doomed;

Depending on the size of mytable, you might need an "ANALYZE doomed"
in there, but I'm suspecting not.  A quick experiment suggests that
you'll get a plan with an inner indexscan on mytable.key, which is
exactly what you need.

See also Chris Browne's excellent suggestions nearby, if you are willing
to make larger readjustments in your thinking...

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Eric JainDate: 2004-02-24 12:07:10
Subject: Re: Slow join using network address function
Previous:From: Kevin BrownDate: 2004-02-24 03:56:02
Subject: Re: Column correlation drifts, index ignored again

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group