I wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
>> Tom Lane wrote:
>>> One thing I was toying with was to add an index to pg_constraint on,
>>> say, (confrelid, conrelid), and to replace the existing seqscans for FK
>>> constraints with scans using this index.
>> I think there is more than one place that would benefit from such an
>> index. Probably turn into a syscache as well?
> Yeah, that was in the back of my mind too, but I haven't looked through
> the code to see. A syscache wouldn't work because it's not a unique key.
Having looked through the code, the only two places that currently seem
to have any need for an index on confrelid are the two paths in TRUNCATE
that find/check for FK relationships. So I'm hesitant to add an index
just for that; seems like too much overhead to put onto all other
updates of pg_constraint.
What we can perhaps do instead is pull out the related OIDs (ie, a
function that given a rel OID returns a list of rels that have FK
dependencies on that rel) and then sort that list into OID order before
acting on it.
Note: the OID-sort-order concept is not perfect; if the OID counter were
to wrap around while the regression tests are running, you could get a
bogus failure of this type. That seems low enough probability to live
with, though. Anyway it'll never happen in the buildfarm's usage, since
buildfarm only runs the tests in freshly-initdb'd databases.
regards, tom lane