Ashish Arte <ashish@openskysoftware.com> writes:
> We recently upgraded to Postgres 7.4 from 7.3.9 and noticed that the
> foreign key constraints compile noticeably faster. In 7.3 the
> constraints would typically take more than an hour to run on our
> production data. Now they take a minute or two.
> Can anybody explain such a major performance improvement ?
Hey, we do do some work on this thing from time to time ;-)
Probably you are talking about this:
2003-10-06 12:38 tgl
* src/: backend/commands/tablecmds.c,
backend/utils/adt/ri_triggers.c, include/commands/trigger.h: During
ALTER TABLE ADD FOREIGN KEY, try to check the existing rows using a
single LEFT JOIN query instead of firing the check trigger for each
row individually. Stephan Szabo, with some kibitzing from Tom Lane
and Jan Wieck.
regards, tom lane