For months I have to make changes on templates linked to many hosts (>100 in this case) very carefully.
Every trigger change took a very long time and put a lot of load on the database.

Before creating the index the deletion of one trigger from ~120 linked hosts took 203437.026 ms
After creating the index the same transaction took 108.783 ms

Please an index similar to:

– CREATE INDEX triggers_3 ON triggers USING btree (templateid);

All credit for analyzing and solving this issue (within minutes) goes to depesz who I encountered at #postgresql IRC channel.