2

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?

2
  • "there is no trigger associated with the current and foreign key tables" -- how did you verify this assumption? – mustaccio Jun 17 at 13:39
  • So do you have your answer? – Erwin Brandstetter Jul 11 at 23:26
4

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:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.