I'm aware you already know that information_schema is slow [1] [2], so I
just want to expose/document another case and tests I did.
I'm using the following view to check what tables depend on what other
tables.
CREATE VIEW raw_relation_tree AS
SELECT
tc_p.table_catalog AS parent_catalog,
tc_p.table_schema AS parent_schema,
tc_p.table_name AS parent_table,
tc_c.table_catalog AS child_catalog,
tc_c.table_schema AS child_schema,
tc_c.table_name AS child_table
FROM
information_schema.referential_constraints AS rc
NATURAL JOIN information_schema.table_constraints AS tc_c
LEFT JOIN information_schema.table_constraints AS tc_p ON
rc.unique_constraint_catalog = tc_p.constraint_catalog AND
rc.unique_constraint_schema = tc_p.constraint_schema AND
rc.unique_constraint_name = tc_p.constraint_name
;
test=# select count(*) from raw_relation_tree;
count
-------
11
(1 row)
An EXPLAIN ANALYZE for a simple SELECT on each of the FROM tables give:
referential_constraints: ~9ms.
table_constraints: ~24ms.
The result, on the above view: ~80ms. Fair enough. But if I apply a
condition:
SELECT * FROM ___pgnui_relation_tree.raw_relation_tree WHERE
parent_schema <> child_schema;
it takes ~2 seconds (!) to complete.
I tried using an alternate table_constraints definition by creating my
own view and changing UNION to UNION ALL (as per [2]) The results were:
table_constraints using UNION ALL has the same number of rows as the
UNION version.
table_constraints now take about 4 ms (as expected).
VIEW raw_relation_tree is now 110 ms.
VIEW raw_relation_tree WHERE parent_schema <> child_schema: 3.3 sec.
EXPLAIN results are way too long to post here. If it is ok, I'll gladly
post them.
Using 8.3.6.
[1] http://archives.postgresql.org/pgsql-bugs/2008-12/msg00144.php
[2]
http://archives.postgresql.org/pgsql-performance/2008-05/msg00062.php
