Re: [HACKERS] [pgadmin-hackers] Feature request: limited deletions
Hi all, On Thu, 2010-04-08 at 07:45 -0400, Robert Haas wrote: > >> 2010/4/8 Thom Brown : > >> > So you could write: > >> > > >> > DELETE FROM massive_table WHERE id < 4000 LIMIT 1; > I've certainly worked around the lack of this syntax more than once. > And I bet it's not even that hard to implement. The fact that it's not implemented has nothing to do with it's complexity (in fact it is probably just a matter of enabling it) - you'll have a hard time to convince some old-time hackers on this list that the non-determinism inherent in this kind of query is acceptable ;-) There is a workaround to do it, which works quite good in fact: delete from massive_table where ctid = any(array(select ctid from massive_table WHERE id < 4000 LIMIT 1)); Just run an explain on it and you'll see it won't get any better, but beware that it might be less optimal than you think, as you will be likely sequential scanning the table for each chunk unless you put some selective where conditions on it too - and then you'll still scan the whole deleted part and not just the next chunk - the deleted records won't go out of the way magically, you need to vacuum, and that's probably a problem too on a big table. So most likely it will help you less than you think on a massive table, the run time per chunk will increase with each chunk unless you're able to vacuum efficiently. In any case you need to balance the chunk size with the scanned portion of the table so you get a reasonable run time per chunk, and not too much overhead of the whole chunking process... Cheers, Csaba. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgadmin-hackers] Feature request: limited deletions
On Thu, Apr 8, 2010 at 7:05 AM, Thom Brown wrote: > On 8 April 2010 11:55, Ian Barwick wrote: >> >> 2010/4/8 Thom Brown : >> > I couldn't find any discussion on this, but the request is quite >> > straightforward. Implement a LIMIT on DELETE statements like SELECT >> > statements. >> > >> > So you could write: >> > >> > DELETE FROM massive_table WHERE id < 4000 LIMIT 1; >> > >> > This would allow deletions in smaller batches rather than waiting >> > potentially hours for the server to mark all those rows as deleted and >> > commit it as one massive transaction. >> >> Is this a PgAdmin-specific question? If it is, apologies I am missing >> the context. >> >> If not, this is totally the wrong list, but why not use a subquery to >> control what is deleted? > > Erm... my mistake, I thought this was on the generic hackers list. Moving > it over in this reply. I've certainly worked around the lack of this syntax more than once. And I bet it's not even that hard to implement. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgadmin-hackers] Feature request: limited deletions
On 8 April 2010 11:55, Ian Barwick wrote: > 2010/4/8 Thom Brown : > > I couldn't find any discussion on this, but the request is quite > > straightforward. Implement a LIMIT on DELETE statements like SELECT > > statements. > > > > So you could write: > > > > DELETE FROM massive_table WHERE id < 4000 LIMIT 1; > > > > This would allow deletions in smaller batches rather than waiting > > potentially hours for the server to mark all those rows as deleted and > > commit it as one massive transaction. > > Is this a PgAdmin-specific question? If it is, apologies I am missing > the context. > > If not, this is totally the wrong list, but why not use a subquery to > control what is deleted? > > > Ian Barwick > Erm... my mistake, I thought this was on the generic hackers list. Moving it over in this reply. Thom
