Hi.
On 17 Listopad 2011, 19:03, Siva Palanisamy wrote:
> Hi All,
>
> I'm using PostgreSQL 8.1.4. I've 3 tables: one being the core (table1),
That's a bit old - update to 8.1.23 (or to a never version, if possible).
> others are dependents (table2,table3). I inserted 70000 records in table1
> and appropriate related records in other 2 tables. As I'd used CASCADE, I
> could able to delete the related records using DELETE FROM table1; It
> works fine when the records are minimal in my current PostgreSQL version.
> When I've a huge volume of records, it tries to delete all but there is no
> sign of deletion progress for many hours! Whereas, bulk import, does in
> few minutes. I wish to do bulk-delete in reasonable minutes. I tried
> TRUNCATE also. Like, TRUNCATE table3, table2,table1; No change in
> performance though. It just takes more time, and no sign of completion!
> From the net, I got few options, like, deleting all constraints and then
> recreating the same would be fine. But, no query seems to be successfully
> run over 'table1' when it's loaded more data!
> Please recommend me the best solutions to delete all the records in
> minutes.
"TRUNCATE table1 CASCADE" should be quite fast. Have you analyzed the
tables after loading the data? That might be one cause.
Post explain analyze of the queries, or at least explain if it takes very
long to finish. And post some basic system stats collected when running
them (iostat, vmstat).
Tomas
