查询性能较差的PostgreSQL

Question

我有一个问题：

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秒钟。

- Hernán Daniel Garau

Answer 1

由于查询似乎都涉及到有关您数据库的Postgres元数据，因此这些元数据的变化频率可能不如查询中的常规表那样高。

因此，我建议您使用 材料化视图，该视图设置为在某个您认为数据时效性可接受的时间间隔内进行刷新。也许是每小时或每天 - 这完全取决于这些数据的变化频率以及您需要多新鲜的数据（这两者也是相互关联的）。

因此，您可以执行以下操作：

CREATE MATERIALIZED VIEW mat_view AS
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;

这将在第一次运行此视图时基本上创建结果的快照。然后可以通过REFRESH MATERIALIZED VIEW mat_view;进行刷新。

您可能还希望使用CONCURRENTLY选项，它可以防止阻塞，但可能需要更长时间。 REFRESH MATERIALIZED VIEW命令可以放在cron中，以确保在所需间隔内完成。

注意：物化视图是Postgres 9.3中的新功能。 CONCURRENTLY添加在Postgres 9.4中。

针对OP评论的编辑：

关于Postgres 8.4，听起来您无法控制，但您可能希望使用此信息向服务器管理员和DBA游说，看看是否会改变他们的想法。

Postgres 8.4发布于7年前（2009年7月）。Postgres 8.4在近2年前（2014年7月）已经停止支持，不再提供更新。关于在8.4上可以做什么，显然受到了很大的限制...你可以尝试将JOIN分成几个不同的临时表，这可能会带来一些改善（虽然远不及后期版本中的材料化视图），这样查询规划器就可以更好地推理每个单独的查询并处理比整体更快的部分，但当然这是一个高度可变的事情，取决于数据、服务器调优和其他因素。此外，在查询之前为WHERE中的子选择创建一个临时表，并使用该表代替。

确保它（以及其他临时表）具有可能需要的任何索引，这样它就可以尽可能地进行索引扫描而不是全表扫描。参考EXPLAIN语句（PgAdmin也提供可视化的），以帮助查看查询规划器对查询的初始评估以及在将其分解为组件时如何更改。

- khampson

---

谢谢，但是在我的工作中使用的是Postgres 8.4。我和数据库管理员和服务器管理员谈过升级，但他们不想要。无论如何，我之前并不知道PostgreSQL有这个功能。数据很好。 - Hernán Daniel Garau

当然，没问题。我在上面的回答中添加了一些额外的信息和另一个选项，可能会有所帮助。 - khampson

我会在本周和下周评估各种选项。如果我找到一个不需要使用临时表的解决方案，我会发布它。感谢大家。 - Hernán Daniel Garau

Answer 2

我创建了一个新查询，读取了PostgreSQL的相关内容...

    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秒钟的时间。

- Hernán Daniel Garau