Bruce Momjian <email@example.com> writes: > Thomas F. O'Connell wrote: >> It seems like this warrants an item somewhere in the release notes, >> and I'm not currently seeing it (or a related item) anywhere. Perhaps >> E.1.3.1 (Performance Improvements)? For some of the more extreme >> UPDATE scenarios I've seen, this could be a big win. > Hard to say, perhaps: > > Prevent referential integrity triggers from firing if referenced > columns are not changed by an UPDATE > > Previously, triggers would fire but do nothing.
And this "firing" has negative effects for the performance at least in versions before 8.1 (we use 8.0.3 in our production). One really dirty hack that comes in mind is to put an additional pk_table (with only one field, the pk from the master) between the "master"-table and the ~30 detail-tables so each update in the "master" would in most cases only trigger a lookup in one table. Only if a pk was really changed the CASCADEd trigger would force a triggered UPDATE in the detail-tables. After denormalization of two of the largest detail-tables into one table the performance improvement was about 10% due to the fact that up to 1 mio. of rows (of about 30 mio) in the "master"-table are updated daily and triggered a lookup in 190 mio. rows (before denormalization) resp. 115 rows (after denormalization). ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match