I wrote: > Alvaro Herrera <[EMAIL PROTECTED]> 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 ---------------------------(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