Disabling triggers makes DELETE fast, but the table has no triggers?

Question

I am deleting 130k out of 750k rows from a PostgreSQL table.

The first time, it took 8 hours to complete the delete query.

Second time, I added an index to the table, and rebuilt that index. Now it took 3 hours to complete the delete query

Third time, I added below line:

alter table contact disable trigger ALL; 
delete from contact where ....; 
alter table contact enable trigger ALL;

It took less than a second to delete the rows. Even though there is no trigger associated with the current and foreign key tables.

What might be the reason for this fast query performance even though no triggers associated with the table? Is there any other type of triggers at the database level?

Answer 1

A side effect of this command is that you take an exclusive lock on the table:

ALTER TABLE DISABLE trigger ALL;

The manual on the DISABLE TRIGGER clause:

This command acquires a SHARE ROW EXCLUSIVE lock.

About SHARE ROW EXCLUSIVE:

[...] This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time.

This can prevent locking contention with concurrent transactions during the big DELETE - which can explain the unreasonably long time you reported all by itself.

The effect is independent from any triggers on the table actually existing. But you do, in fact, have triggers. You mention foreign key tables, and FK constraints are implemented with special triggers internally. See:

• How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?

Since you specified ALL (as opposed to USER) those are included.
The manual again:

One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints).

Bold emphasis mine.

Disabling FK checks saves time, of course. (Especially if that triggers a cascade of additional triggers, under concurrent load ...) But it can also break referential integrity and functionality like CASCADE options. So this is reserved to superusers and the manual warns against it. Be sure to read up.

Can't say which of the two effects contributed more.