Sql - Delete Bulk Records - Delete Bulk

Question

I have a table with about 4 million records.

What is the best method to delete all of them to get the best time?
Indexing helps something, since it is only a reference to find data?

OBS

`DELETE FROM tb_name;` // Demorou em torno de 45min

Answer 1

Use TRUNCATE to delete all records.

TRUNCATE table;

It is an optimized method for complete deletion of records. If you want to propagate the effect to tables that reference a primary key in the table suffering TRUNCATE, use the parameter CASCADE;

TRUNCATE table CASCADE;

Source:

http://www.postgresql.org/docs/9.1/static/sql-truncate.html

Answer 2

Truncate has better performace than a delete without Where because the first does not log the deleted lines while the second does it for each one.

TRUNCATE table nome can be translated by the bank as, deleted the records, truncate is DML.

DELETE FROM nome, is translated as, I logged record 1, deleted record 1 ... Until N. delete is a DML