On Monday February 23 2004 10:23, Tom Lane wrote: > "Ed L." <[EMAIL PROTECTED]> 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.
I didn't mention I'd written a trigger to do delete N rows on each new insert (with a delay governor preventing deletion avalanches). The approach looks a little heavy to be done from within a trigger with the response time I need, but I'll try it. Cantchajust toss in that "limit N" functionality to delete clauses? How hard could that be? ;) > See also Chris Browne's excellent suggestions nearby, if you are willing > to make larger readjustments in your thinking... I did a search for articles by Chris Browne, didn't see one that appeared relevant. What is the thread subject to which you refer? ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
