Foreign key triggers - Mailing list pgsql-general

Hi,

I was trying to clean a database by deleting records of some of its tables. But in our model we have a table that is heavily referenced, that is, many tables reference this particular table by foreign key constraints. We don't have foreign key indexes, so executing a delete from mytable takes a lot of time. What was strange is that all referencing tables were already empty, and I executed an ANALYZE VERBOSE before the delete. I expected the delete to be relatively fast under these conditions. In order to get more information, I did the same on another database, cleaning manually all referencing tables, executing ANALYZE and then executing an EXPLAIN ANALYZE DELETE. What surprised me was to find that the database was executing triggers I never created! Why is this? Are foreign keys implemented as some form of triggers? Here is the result of the explain analyze:

fitaccionydesarrollo=> explain analyze delete from tsolicitudid;

                                                      QUERY PLAN                                                      

----------------------------------------------------------------------------------------------------------------------

 Delete on tsolicitudid  (cost=0.00..420.77 rows=25677 width=6) (actual time=152.113..152.113 rows=0 loops=1)

   ->  Seq Scan on tsolicitudid  (cost=0.00..420.77 rows=25677 width=6) (actual time=0.027..5.222 rows=25677 loops=1)

 Trigger for constraint fkarchcresol: time=868.947 calls=25677

 Trigger for constraint fkautevesol: time=826.215 calls=25677

 Trigger for constraint fkautflujosol: time=3374.971 calls=25677

 Trigger for constraint fkauttransol: time=902.524 calls=25677

 Trigger for constraint fkcamdivsol: time=802.560 calls=25677

 Trigger for constraint fkctasol: time=1540379.291 calls=25677

 Trigger for constraint fkevacrecritsol: time=2074.104 calls=25677

 Trigger for constraint fkevacredetsol: time=924.946 calls=25677

 Trigger for constraint fkevacrenocritsol: time=921.916 calls=25677

 Trigger for constraint fkevacresol: time=914.022 calls=25677

 Trigger for constraint fkjurbalgensol: time=920.506 calls=25677

 Trigger for constraint fkjurcomersol: time=837.405 calls=25677

 Trigger for constraint fkjurflujcajasol: time=822.599 calls=25677

 Trigger for constraint fkjurprosol: time=1173.142 calls=25677

 Trigger for constraint fknatinfadisol: time=830.009 calls=25677

 Trigger for constraint fknatingegrsol: time=829.959 calls=25677

 Trigger for constraint fknatpresol: time=4508.163 calls=25677

 Trigger for constraint fkperinfcresol: time=1018.662 calls=25677

 Trigger for constraint fkpernatactsol: time=853.324 calls=25677

 Trigger for constraint fkperrefcomsol: time=820.353 calls=25677

 Trigger for constraint fkperrefpersol: time=814.241 calls=25677

 Trigger for constraint fksimcresol: time=821.240 calls=25677

 Trigger for constraint fksolbacktoback: time=905.754 calls=25677

 Trigger for constraint fksolcattas: time=1043.349 calls=25677

 Trigger for constraint fksolcol: time=1123.774 calls=25677

 Trigger for constraint fksolcomen: time=944.160 calls=25677

 Trigger for constraint fksolcomext: time=921.940 calls=25677

 Trigger for constraint fksolcondgir: time=1027.143 calls=25677

 Trigger for constraint fksolconting: time=917.689 calls=25677

 Trigger for constraint fksolcredvis: time=914.352 calls=25677

 Trigger for constraint fksolctasol: time=927.796 calls=25677

 Trigger for constraint fksolctaxren: time=903.657 calls=25677

 Trigger for constraint fksolcuosolid: time=1144.000 calls=25677

 Trigger for constraint fksoldocsolid: time=947.123 calls=25677

 Trigger for constraint fksolembarque: time=948.511 calls=25677

 Trigger for constraint fksolexcep: time=902.697 calls=25677

 Trigger for constraint fksolfact: time=913.719 calls=25677

 Trigger for constraint fksolfir: time=1025.314 calls=25677

 Trigger for constraint fksolgar: time=958.980 calls=25677

 Trigger for constraint fksolid: time=1231.825 calls=25677

 Trigger for constraint fksollincre: time=978.157 calls=25677

 Trigger for constraint fksollincreope: time=926.211 calls=25677

 Trigger for constraint fksolliq: time=965.437 calls=25677

 Trigger for constraint fksolmer: time=952.036 calls=25677

 Trigger for constraint fksolobser: time=1106.787 calls=25677

 Trigger for constraint fksolparades: time=967.431 calls=25677

 Trigger for constraint fksolparapag: time=927.396 calls=25677

 Trigger for constraint fksolperid: time=938.529 calls=25677

 Trigger for constraint fksolpig: time=915.139 calls=25677

 Trigger for constraint fksolpla: time=1018.932 calls=25677

 Trigger for constraint fksolrenovsolid: time=897.788 calls=25677

 Trigger for constraint fksolsegcre: time=910.886 calls=25677

 Trigger for constraint fksolsegsolid: time=938.941 calls=25677

 Trigger for constraint fksolsegurooper: time=915.147 calls=25677

 Trigger for constraint fksolsublid: time=903.705 calls=25677

 Trigger for constraint fksoltabcuo: time=899.935 calls=25677

 Trigger for constraint fksoltarcre: time=924.028 calls=25677

 Trigger for constraint fksoltasefec: time=993.236 calls=25677

 Trigger for constraint fksolveh: time=966.006 calls=25677

 Trigger for constraint fksolver: time=910.991 calls=25677

 Trigger for constraint fksolvis: time=1022.102 calls=25677

 Trigger for constraint fkverifsol: time=836.807 calls=25677

 Total runtime: 1605529.144 ms

(65 filas)

As you can see, each trigger is called once for each record in the table I'm trying to delete, and that's what takes a lot of time. Any insight on this will be really useful.

Thanks,

Roberto