Re: Fast way to delete big table?

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 finishupdate
summary table from the previous select result, takes millisecondsdelete from
tableA where primaryKey = ... takes 2.9 hours to finish. Regards,Haiming

--
View this message in context: http://postgresql.nabble.com/Fast-way-to-delete-big-table-tp5903787p5903918.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.