我的 Postgres 数据库中有一些表,我已将这些表设置为围绕索引进行聚类。但我忘记了上次对这些表进行聚类维护是什么时候,而且我不想浪费时间对这些表进行聚类。我如何知道这些表是否进行了聚类?
答案1
您可以从数据字典中查询所有聚集表:
SELECT relname AS table_name
FROM pg_class c
JOIN pg_index i ON i.indrelid = c.oid
WHERE relkind = 'r' AND relhasindex AND i.indisclustered
答案2
接受的答案似乎不正确。
似乎查询中无法获得何时(或者是否)执行聚类的信息,但可以查询统计数据以查看聚类的效果如何:https://stackoverflow.com/questions/53299733/how-to-tell-when-a-postgres-table-was-clustered-and-what-indexes-were-used
答案3
基于@alex-stoddard的回答和他链接的 StackOverflow 问题,我能够构建这个查询,它似乎显示了用于聚类的所有索引表对:
SELECT
indisclustered,
index_name,
cls.relname AS table_name
FROM
pg_class cls
INNER JOIN (
SELECT
indexrelid,
indrelid,
indisclustered,
relname AS index_name
FROM pg_index ndx
INNER JOIN pg_class cls
ON cls.oid=indexrelid
WHERE indisclustered='t'
) tmp
ON cls.oid=tmp.indrelid
;
请注意,聚类是按索引而不是按列进行的(尽管索引通常基于列),但性能统计数据是按列收集的。如果您还想获取一些按列进行的统计数据,可以尝试:
SELECT
tablename,
attname,
correlation,
index_name,
indexdef
FROM (
SELECT
indisclustered,
index_name,
table_name,
indexdef
FROM (
SELECT
indisclustered,
index_name,
cls.relname AS table_name
FROM
pg_class cls
INNER JOIN (
SELECT
indexrelid,
indrelid,
indisclustered,
relname AS index_name
FROM pg_index ndx
INNER JOIN pg_class cls
ON cls.oid=indexrelid
WHERE indisclustered='t'
) tmp
ON cls.oid=tmp.indrelid
) clustering
INNER JOIN pg_indexes indxs
ON indxs.tablename=clustering.table_name AND
indxs.indexname=clustering.index_name
) indexes
INNER JOIN
pg_stats
ON indexes.table_name=pg_stats.tablename AND indexes.indexdef LIKE CONCAT('%', pg_stats.attname, '%')
;