如何判断何时对Postgres表进行集群以及使用了哪些索引 [英] How to tell when a Postgres table was clustered and what indexes were used

问题描述

通过群集实现的性能提升给我留下了深刻的印象，但是花费的时间却没有。

I've been impressed by the performance improvements achieved with clustering, but not with how long it takes.

我知道如果表或表需要重新构建集群，分区后，分区发生了变化，但是除非我记下上次对表进行群集的时间，否则如何确定何时需要再次进行分区？

I know clustering needs to be rebuilt if a table or partition is changed after the clustering, but unless I've made a note of when I last clustered a table, how can I tell when I need to do it again?

我可以使用此查询告诉我哪些表具有一个或多个聚集索引

I can use this query to tell me what table(s) have one or more clustered indexes

SELECT *
FROM   pg_class c
JOIN   pg_index i ON i.indrelid = c.oid
WHERE  relkind = 'r' AND relhasindex AND i.indisclustered 

我的问题是。

• 如何知道哪些索引已聚簇？ li>
  
• 有什么方法可以确切地找出上次集群表的时间吗？


• 如何判断聚集索引是否仍然有效？换句话说，如何知道一个表/索引已经改变了多少，以至于我需要重新构建集群。

• How can I tell which indexes have been clustered?
• Is there any way of finding out exactly when a table was last clustered?
• How can I tell if a clustered index is still 'valid', or in other words, how can tell how much a table/index has changed enough that I need to re-build the cluster.

我注意到重建簇索引的时间与最初构建簇索引的时间一样长（即使在此期间未触及任何表格）。所以我想避免重新聚类，除非我知道表需要它。

I've noticed that it takes just as long to re-build a clustered index as it does to build it in the first place (even if the table hasn't been touched in the meantime). So I want to avoid re-clustering unless I know the table needs it.

为清楚起见，更新（我希望）

UPDATE for clarity (I hope)

如果我使用此命令...。

If I use this command....

CLUSTER tableA USING tableA_idx1;

• 我以后如何找出引用了哪个索引，即
  tableA_idx1（表中定义了多个索引）？


• 在运行该命令时，它是否记录在任何地方？


• 我知道，当表发生更改时，有时可能需要使用 CLUSTER tableA 来重建/刷新/重新创建群集（不确定正确的措辞）。无论如何，是否知道表何时发生了很大变化以至于群集不再起作用？


• How can I find out at a later date which index was referenced i.e. tableA_idx1 (the table has multiple indexes defined)?
• Is it recorded anywhere when this command was run?
• I know that the cluster may need to be rebuilt/refreshed/recreated (not sure of the correct phraseology) occasionally using CLUSTER tableA when the table undergoes changes. Is there anyway of knowing when the table has changed so much that the clustering no longer helps?

推荐答案

要确定哪个索引最后一次用于聚集表，请使用 pg_index 系统目录。

To tell which index was last used to cluster the table, use the pg_index system catalog.

在表中查询属于该表的所有索引，并查看哪个设置了没用的。一次只能将一个表聚集到一个索引中。

Query the table for all indexes that belong to your table and see which one has indisclustered set. A table can only be clustered by a single index at a time.

无法找出何时上次聚集该表，但这不是很有趣。您想知道的是集群仍然有多出色。

There is no way to find out when the table was last clustered, but that's not very interesting anyway. What you want to know is how good the clustering still is.

要找到该信息，请查询 pg_stats 行作为您在其上进行集群的列。如果 correlation 接近1，则您仍然不错。值越小，表示的聚类就越多。

To find that, query the pg_stats line for the column on which you clustered. If correlation is close to 1, you are still good. The smaller the value gets, the more clustering is indicated.

这篇关于如何判断何时对Postgres表进行集群以及使用了哪些索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！