Effects of cascading references in foreign keys
От | Martin Lesser |
---|---|
Тема | Effects of cascading references in foreign keys |
Дата | |
Msg-id | 87acgszihk.fsf@nb-aspire.bettercom.de обсуждение исходный текст |
Ответы |
Re: Effects of cascading references in foreign keys
Re: Effects of cascading references in foreign keys |
Список | pgsql-performance |
Which effects have UPDATEs on REFERENCEd TABLEs when only columns in the referenced table are updated which are not part of the FOREIGN KEY constraint? I have one "master"-table like create table t_master ( m_id serial primary key, m_fld1 ..., m_fld2 ..., ... ) The table above is referenced from several (~30) other tables, i.e. like create table t_detail ( d_ebid int REFERENCES t_master (m_id) ON UPDATE CASCADE ON DELETE CASCADE, d_fld1 ..., d_fld2 ..., ... ) All tables which reference t_master have appropriate indexes on the referencing columns, vacuum/analyze is done regularly (daily). Does an UPDATE of e.g. m_fld1 in t_master cause a 'lookup' in all tables which have a cascading update-rule or is this 'lookup' only triggered if the referenced column in t_master is explicitly updated? After removing some detail tables which are not longer needed we see an improvemed performance so at the moment it _looks_ like each update in t_master triggers a 'lookup' in each referencing table also if the referenced column (m_id) is not changed. I've read "If the row is updated, but the referenced column is not actually changed, no action is done." in the docs but it is not clear for me whether this "no action" really means "null action" and so the improved performance has other reasons. TIA, Martin
В списке pgsql-performance по дате отправления: