我有一个查询：
SELECT
'{\"nombre\":\"'||c.column_name||'\",\"type\":\"'|| c.data_type
||'\",\"is_nullable\":\"'|| c.is_nullable||'\",\"is_pk\":\"'|| CASE WHEN constraint_type = 'PRIMARY KEY' THEN 'SI' ELSE 'NO' END
||'\",\"max_length\":\"'||COALESCE(c.character_maximum_length::VARCHAR,'')||'\",\"FK_schema\":\"'||COALESCE(ccu.table_schema,'')||'\",\"FK_tabla\":\"'||
COALESCE(ccu.table_name,'')||'\",\"FK_columna\":\"'||COALESCE(ccu.column_name,'')||
'\"}' as id, c.column_name
FROM information_schema.columns AS c
LEFT JOIN information_schema.key_column_usage i ON I.table_name=c.table_name AND I.table_schema=c.table_schema AND c.column_name = I.column_name
LEFT JOIN information_schema.table_constraints tc ON TC.constraint_name = I.CONSTRAINT_NAME AND constraint_type IN ('FOREIGN KEY','PRIMARY KEY')
LEFT JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
AND constraint_type IN ('FOREIGN KEY')
WHERE c.table_schema = '".$_POST['schema']."' AND c.table_name='".$_POST['tabla']."'
AND NOT EXISTS (
SELECT q.column_name FROM information_schema.constraint_column_usage q
inner join information_schema.table_constraints USING(constraint_name )
WHERE c.table_schema = q.table_schema AND q.table_name=c.table_name AND q.column_name = c.column_name
AND tc.constraint_name > q.constraint_name AND TC.constraint_type <> 'PRIMARY KEY')
ORDER BY c.ordinal_position;
它可以工作，但是在具有97个架构的数据库中，需要一整分钟的时间来运行。
我怎样才能解决这个问题？
没有这一行：
LEFT JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND constraint_type = 'FOREIGN KEY'
不到1秒的时间。
我做了一个新的查询阅读此postgressql ...
select
'{\"nombre\":\"'||columna.attname||'\",\"tipo\":\"'|| tipo.typname
||'\",\"is_nullable\":\"'|| CASE WHEN columna.attnotnull THEN 'NO' ELSE 'SI' END||'\",\"is_pk\":\"'|| CASE WHEN pk.conname IS NULL THEN 'NO' ELSE 'SI' END
||'\",\"max_length\":\"'||COALESCE(character_maximum_length::varchar,'')||'\",\"FK_schema\":\"'||COALESCE(fk_schema.nspname,'')||'\",\"FK_tabla\":\"'||
COALESCE(fk_tabla.relname,'')||'\",\"FK_columna\":\"'||COALESCE(fk_columna.attname,'')||
'\"}' as id, columna.attname,columna.*
from pg_catalog.pg_namespace pgschema
inner join pg_catalog.pg_class clase ON PGSCHEMA.OID = clase.relnamespace and relname ='".$_POST['tabla']."'
inner join pg_catalog.pg_attribute columna ON ATtReLID = clase.oid
inner join pg_catalog.pg_type tipo ON tipo.oid = columna.atttypid
inner join information_schema.columns ON table_schema = pgschema.nspname AND table_name = clase.relname AND column_name = columna.attname
left join pg_catalog.pg_constraint pk ON pk.contype = 'p' AND PK.conrelid = clase.oid and array[columna.attnum] && pk.conkey
left join pg_catalog.pg_constraint Fk ON FK.contype = 'f' AND FK.conrelid = clase.oid and array[columna.attnum] && fk.conkey
left join pg_catalog.pg_class Fk_tabla ON FK.confrelid = fk_tabla.oid -- AND FK.conrelid = clase.oid and array[columna.attnum] && fk.conkey
left join pg_catalog.pg_namespace Fk_schema ON FK_tabla.relnamespace = fk_schema.oid
left join pg_catalog.pg_attribute fk_columna ON fk_tabla.OID = fk_columna.attrelid AND ARRAY[fk_columna.attnum] && fk.confkey
where pgschema.nspname = '".$_POST['schema']."' and columna.attnum > 0;
不到1秒的时间。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句