Mario Weilguni <mweilguni@xxxxxxxx> writes: > I can confirm this for a quite larger result set (4020 rows) for a DB > with 410 tables and a lot of foreign key constraints. > ... > This is Postgresql 8.2.4, on a Dual-Core XEON 3.6GHz. With nested_loops > off, I get a very fast response (330ms). FWIW, it looks like 8.3 is significantly smarter about this example --- it's able to push the toplevel conditions on CONSTRAINT_SCHEMA and CONSTRAINT_TYPE down inside the UNION, where 8.2 fails to do so. Which is not to say that there's not more left to do on optimizing the information_schema views. In this particular case, for example, I wonder why the UNION in INFORMATION_SCHEMA.TABLE_CONSTRAINTS isn't a UNION ALL. There's probably a lot more such micro-optimizations that could be done if anyone was motivated to look at it. regards, tom lane
