我需要优化下面的pgsql选择语句-它目前需要34平均秒执行

Question

我有一个显示所有关系的视图。

我对视图使用如下select:

CREATE VIEW relationships AS SELECT DISTINCT
relations.name,
sch_tco.id AS table_id,
relations.related_table AS related_table,
scd_tco.id AS related_id,
relations.foreign_column AS foreign_column_name,
relations.local_column AS local_column_name,
count(DISTINCT relations.name) AS relationships,
count(DISTINCT relations.referenced_tables) AS foreign_keys_count,
count(DISTINCT relations.referencing_tables) AS references_count,
count(DISTINCT related_table) AS related_tables_count,
count(DISTINCT relations.referenced_tables) AS referenced_tables_count,
count(DISTINCT relations.referencing_tables) AS referencing_tables_count
FROM ( SELECT DISTINCT
pk_tco.table_name AS name,
fk_tco.table_name AS related_table,
fk_tco.table_name AS referencing_tables,
NULL::varchar(100) AS referenced_tables,
cc_tco.column_name AS foreign_column,
pc_tco.column_name AS local_column
FROM
information_schema.referential_constraints rco
JOIN information_schema.table_constraints fk_tco ON rco.constraint_name = fk_tco.constraint_name
AND rco.constraint_schema = fk_tco.table_schema
JOIN information_schema.table_constraints pk_tco ON rco.unique_constraint_name = pk_tco.constraint_name
AND rco.unique_constraint_schema = pk_tco.table_schema
JOIN information_schema.constraint_column_usage AS cc_tco ON cc_tco.constraint_name = pk_tco.constraint_name
AND cc_tco.table_schema = pk_tco.table_schema
JOIN information_schema.key_column_usage AS kc_tco ON kc_tco.constraint_name = pk_tco.constraint_name
AND kc_tco.table_schema = pk_tco.table_schema
JOIN information_schema.key_column_usage AS pc_tco ON pc_tco.constraint_name = fk_tco.constraint_name
AND pc_tco.table_schema = fk_tco.table_schema
GROUP BY
name,
related_table,
referenced_tables,
referencing_tables,
foreign_column,
local_column
UNION ALL SELECT DISTINCT
fk_tco.table_name AS name,
pk_tco.table_name AS related_table,
NULL AS referencing_tables,
pk_tco.table_name AS referenced_tables,
kc_tco.column_name AS foreign_column,
pc_tco.column_name AS local_column
FROM
information_schema.referential_constraints rco
JOIN information_schema.table_constraints fk_tco ON rco.constraint_name = fk_tco.constraint_name
AND rco.constraint_schema = fk_tco.table_schema
JOIN information_schema.table_constraints pk_tco ON rco.unique_constraint_name = pk_tco.constraint_name
AND rco.unique_constraint_schema = pk_tco.table_schema
JOIN information_schema.columns p ON fk_tco.table_name = p.table_name
JOIN information_schema.columns t ON pk_tco.table_name = t.table_name
JOIN information_schema.constraint_column_usage AS cc_tco ON cc_tco.constraint_name = fk_tco.constraint_name
AND cc_tco.table_schema = fk_tco.table_schema
JOIN information_schema.key_column_usage AS kc_tco ON kc_tco.constraint_name = pk_tco.constraint_name
AND kc_tco.table_schema = pk_tco.table_schema
AND kc_tco.position_in_unique_constraint = kc_tco.ordinal_position --IMPORTANT!
JOIN information_schema.key_column_usage AS pc_tco ON pc_tco.constraint_name = fk_tco.constraint_name
AND pc_tco.table_schema = fk_tco.table_schema
AND pc_tco.position_in_unique_constraint = pc_tco.ordinal_position --IMPORTANT!
GROUP BY
name,
related_table,
referencing_tables,
referenced_tables,
foreign_column,
local_column) relations
JOIN tables sch_tco ON relations.name = sch_tco.name
JOIN tables scd_tco ON relations.related_table = scd_tco.name
JOIN information_schema.columns t ON sch_tco.name = t.table_name
JOIN information_schema.columns p ON scd_tco.name = p.table_name
GROUP BY
relations.name,
related_table,
table_id,
related_id,
foreign_column_name,
local_column_name
ORDER BY
relationships DESC;

生成如下表

relationshipstbody> <<tr>

name	table_id	related_id	foreign_column_name	local_column_name	foreign_keyys_count	references_count	related_tables_count	referenced_tables_count	referencing_tables_count	referencing_tables_count		referencing_tables_count
用户	8298968 d - 54 - c9 - 496 a - 8320 bb67ffb5cfec	配置	f954e6b8 - 111 c - 4 - ed7 b6d6 d68545640645	id	user_id	1	0	1	1	0	1
	8298968 d - 54 - c9 - 496 a - 8320 bb67ffb5cfec	todos	d1de713a - 5405 - 4 - bc6 - 80 - 5 - c4c20d0343c	id	user_id	1	0	1	1	0	1

我用这个查询解决了这个问题:

select distinct tables.id,
tables.name,
tables.parent,
(SELECT jsonb_agg(jsonb_build_object(
'table_schema', tc.table_schema,
'constraint_name', tc.constraint_name,
'table_name', tc.table_name,
'column_name', kcu.column_name,
'foreign_table_schema', ccu.table_schema,
'foreign_table_name', ccu.table_name,
'foreign_column_name', ccu.column_name))
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' and ccu.table_name = tables.name) relationships,
(select jsonb_agg(jsonb_build_object('name', cols.column_name, 'type', cols.udt_name))
from information_schema.columns cols
where cols.table_name = tables.name)      table_schema
from tables
left join information_schema.columns table_cols on tables.name = table_cols.table_name