在PostgreSQL中，如何判断表的每个索引是否聚集？ [英] In PostgreSQL, how can we tell if each index of a table is clustered or not?

问题描述

在PostgreSQL中，如何判断表的每个索引是否聚集？

In PostgreSQL, how can we tell if each index of a table is clustered or not?

这与

This is counterpart to In MySQL, how can we tell if an index of a table is clustered or not?

谢谢。

推荐答案

Postgres不像MySql那样支持聚簇索引。可能有一个用于聚集表的索引。您可以通过查询系统目录不分散的列来进行检查-index.html rel = nofollow noreferrer> pg_index。

Postgres does not support clustered indexes in the sense like in MySql. There may be an index which was used to cluster the table. You can check this by querying the column indisclustered in the system catalog pg_index.

不分散-如果为true，则该表最后一次聚集

indisclustered - If true, the table was last clustered on this index

示例：

create table my_table(id serial primary key, str text unique);

select relname, indisclustered
from pg_index i
join pg_class c on c.oid = indexrelid
where indrelid = 'public.my_table'::regclass

     relname      | indisclustered 
------------------+----------------
 my_table_str_key | f
 my_table_pkey    | f
(2 rows)

cluster my_table using my_table_str_key;

select relname, indisclustered
from pg_index i
join pg_class c on c.oid = indexrelid
where indrelid = 'public.my_table'::regclass

     relname      | indisclustered 
------------------+----------------
 my_table_str_key | t
 my_table_pkey    | f
(2 rows)

请阅读有关群集：

对表进行群集时，将根据索引信息对其进行物理重新排序。群集是一项一次性操作：表在随后进行更新时，更改不会被群集。也就是说，没有尝试根据新的或更新的行的索引顺序来存储它们。

When a table is clustered, it is physically reordered based on the index information. Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order.

这篇关于在PostgreSQL中，如何判断表的每个索引是否聚集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！