Hi Jeff, Thank you very much. >Your examples also have no selection criteria or stopping criteria (other >than when your outer driving script stops calling the function). That >means you might be deleting any arbitrary rows in the master table (not >just the oldest ones) and might proceed with these deletions until the >table is empty. Given that, why not just summarize the entire table and >then truncate the whole thing? Yeah, that's my aim. Go through every row and delete the entire table. The problem of summarizing first then truncate is I cant guarantee the database and system keep running during the summarizing as the customer sometimes stops the application and postgres manually. Plus there are new data goes into the table so I need to process chunk by chunk until it finishes. >You could try to rewrite this into a single SQL query, using a CTE with >"with t as (delete .... returning * )". The hardest part is that >PostgreSQL alas doesn't allow LIMIT on DELETE statements, so you have to go >through some ugly contortions with a sub-select, or a where clause on your >delete using, for example, a date to do the limiting, and have your script >increment the date on each invocation. I've not done this with >summarization, but have done it with partitioning where I had to move >tuples from the parent table to their new partition (or the reverse, moving >from an ill-conceived partition back to the parent) with a minimum of >disruption. worth to try, eg. delete the oldest 100 rows at a time. But wont the sorting take more time than just select from the fly? >Why is this not fast enough? So, it would take 4 days. How fast do you >need it to be? Works ok in my testing environment but not on the production machine. Select * from tableA limit 1; takes milliseconds to finish update summary table from the previous select result, takes milliseconds delete from tableA where primaryKey = ... takes 2.9 hours to finish. Regards, Haiming
View this message in context:
Re: Fast way to delete big table? Sent from the
PostgreSQL - general mailing list archive at Nabble.com.
