postgresql 关于 postgres 中的聚集索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4796548/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
About clustered index in postgres
提问by twimo
I'm using psql to access a postgres database. When viewing the metadata of a table, is there any way to see whether an index of a table is a clustered index?
我正在使用 psql 访问 postgres 数据库。在查看表的元数据时，有什么办法可以查看表的索引是否是聚簇索引？
I heard that the PRIMARY KEY of a table is automatically associated with a clustered index, is it true?
听说一个表的PRIMARY KEY会自动关联一个聚集索引，是真的吗？
回答by araqnid
Note that PostgreSQL uses the term "clustered index" to use something vaguely similar and yet very different to SQL Server.
请注意，PostgreSQL 使用术语“聚集索引”来使用与 SQL Server 模糊相似但又非常不同的东西。
If a particular index has been nominated as the clustering index for a table, then psql's \dcommand will indicate the clustered index, e.g.,
如果某个特定索引已被指定为表的聚集索引，则 psql 的\d命令将指示聚集索引，例如，
Indexes:
"timezone_description_pkey" PRIMARY KEY, btree (timezone) CLUSTER
PostgreSQL does not nominate indices as clustering indices by default. Nor does it automatically arrange table data to correlate with the clustered index even when so nominated: the CLUSTER command has to be used to reorganise the table data.
PostgreSQL 默认不指定索引作为集群索引。即使如此指定，它也不会自动排列表数据以与聚集索引相关联：必须使用 CLUSTER 命令来重新组织表数据。
回答by beldaz
In PostgreSQL the clustered attribute is held in the metadata of the corresponding index, rather than the relation itself. It is the indisclusteredattribute in pg_indexcatalogue. Note, however, that clustering relations within postgres is a one-time action: even if the attribute is true, updates to the table do not maintain the sorted nature of the data. To date, automatic maintenance of data clustering remains a popular TODOitem.
在 PostgreSQL 中，聚集属性保存在相应索引的元数据中，而不是关系本身。它是pg_index目录中的indisclustered属性。但是请注意，postgres 中的聚类关系是一次性操作：即使属性为真，对表的更新也不会保持数据的排序性质。迄今为止，数据聚类的自动维护仍然是一个流行的TODO项目。
There is often confusion between clusteredand integratedindexes, particularly since the popular textbooks use conflicting names, and the terminology is different again in the manuals of postgres and SQL server (to name just two). When I talk about an integrated index(also called a main indexor primary index) I mean one in which the relation data is contained in the leaves of the index, as opposed an externalor secondaryindex in which the leaves contain index entries that point to the table records. The former type is necessarily always clustered. Unfortunately postgres only supports the latter type. Anyhow, the fact that an integrated (primary) index is always clusteredmay have given rise to the belief that "a PRIMARY KEY of a table is automatically associated with a clustered index". The two statements sound similar, but are different.
聚集索引和集成索引之间经常存在混淆，特别是因为流行的教科书使用了冲突的名称，并且在 postgres 和 SQL Server 的手册中术语再次不同（仅举两个）。当我谈论集成索引（也称为主索引或主索引）时，我指的是其中关系数据包含在索引的叶子中的索引，而不是外部索引或二级索引，其中叶子包含指向的索引条目到表记录。前一种类型必然总是成簇的。不幸的是 postgres 只支持后一种类型。无论如何，事实是集成（主）索引始终是聚集的，这可能会让人相信“表的 PRIMARY KEY 会自动与聚集索引相关联”。这两种说法听起来很相似，但又有所不同。
回答by a_horse_with_no_name
is there any way to see whether an index of a table is a clustered index
有什么办法可以查看一个表的索引是否是聚集索引
PostgreSQL does not have a clustered index, so you won't be able to see them.
PostgreSQL 没有聚集索引，因此您将无法看到它们。
I heard that the PRIMARY KEY of a table is automatically associated with a clustered index, is it true?
听说一个表的PRIMARY KEY会自动关联一个聚集索引，是真的吗？
No, that's not true (see above)
不，那不是真的（见上文）
You can manually cluster a table along an index, but this is nothing that will be maintained automatically (as e.g. with SQL Server's clustered indexes).
您可以沿着索引手动聚集表，但这不会自动维护（例如使用 SQL Server 的聚集索引）。
For more details, see the description of the CLUSTERcommand in the manual.
更多详细信息，请参见手册中对CLUSTER命令的描述。
回答by Anvesh
PostgreSQL does not have direct implementation of CLUSTER index like Microsoft SQL Server.
PostgreSQL 没有像 Microsoft SQL Server 那样直接实现 CLUSTER 索引。
Reference Taken from this Blog:
In PostgreSQL, we have one CLUSTER command which is similar to Cluster Index.
在 PostgreSQL 中，我们有一个类似于 Cluster Index 的 CLUSTER 命令。
Once you create your table primary key or any other Index, you can execute the CLUSTER command by specifying that Index name to achieve the physical order of the Table Data.
创建表主键或任何其他索引后，您可以通过指定该索引名称来执行 CLUSTER 命令，以实现表数据的物理顺序。
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.
当表被聚簇时，它会根据索引信息进行物理重新排序。聚类是一次性操作：随后更新表时，不会对更改进行聚类。也就是说，不会尝试根据索引顺序存储新的或更新的行。
Syntax of Cluster:
集群的语法：
First time you must execute CLUSTER using the Index Name.
第一次必须使用索引名称执行 CLUSTER。
CLUSTER table_name USING index_name;
Cluster the table:
对表进行聚类：
Once you have executed CLUSTER with Index, next time you should execute only CLUSTER TABLE because It knows that which index already defined as CLUSTER.
一旦你用索引执行了 CLUSTER，下次你应该只执行 CLUSTER TABLE 因为它知道哪个索引已经定义为 CLUSTER。
CLUSTER table_name;
回答by Rafiul Islam
Cluster Indexing
集群索引
A cluster index means telling the database to store the close values actually close to one another on the disk. They can uniquely identify the rows in the SQL table. Every table can have exactly one one clustered index. A cluster index can covers more than one column. By default, a column with a primary key already has a clustered index.
簇索引意味着告诉数据库将实际接近的接近值存储在磁盘上。它们可以唯一标识 SQL 表中的行。每张表都可以有一个一一的聚集索引。一个簇索引可以覆盖多列。默认情况下，具有主键的列已经具有聚集索引。
A dictionary itself a table with clustered index. Because all the data physically stored in alphabetical order.
字典本身就是一个带有聚集索引的表。因为所有的数据都是按字母顺序物理存储的。
Non Cluster Indexing
非集群索引
Non clustered indexing are like simple indexing of a book. They are just used for fast retrieval of data. Not sure to have unique data. A non clustered index contains the non clustered index keys and each keys contains the data location pointer. Just a book content index contains the key of a topic or chapter and the page location of that.
非聚集索引就像一本书的简单索引。它们仅用于快速检索数据。不确定是否有唯一数据。非聚集索引包含非聚集索引键，每个键包含数据位置指针。只是书籍内容索引包含主题或章节的关键字及其页面位置。
A book content table just remember the content name and its page location. It is not sure that the data is unique. Because same paragraph or text line or word can be placed many times.
书籍内容表只记住内容名称及其页面位置。不确定数据是否唯一。因为可以多次放置相同的段落或文本行或单词。
PostgreSQL Indexing
PostgreSQL 索引
PostgreSQL automatically create indexes for PRIMARY KEYand every UNIQUEconstraints of a table. Login to a database in PostgreSQL terminal and type \d table_name. All stored indexes will be visualized. If there exist the a clustered index that will also be identified.
PostgreSQL 自动为表的PRIMARY KEY每个UNIQUE约束创建索引。在 PostgreSQL 终端中登录数据库并键入 \d table_name. 所有存储的索引都将被可视化。如果存在也将被识别的聚集索引。
CREATE TABLE IF NOT EXISTS profile(
uid serial NOT NULL UNIQUE PRIMARY KEY,
username varchar(30) NOT NULL UNIQUE,
phone varchar(11) NOT NULL UNIQUE,
age smallint CHECK(age>12),
address text NULL
);
"profile_pkey" PRIMARY KEY, btree (uid)
"profile_phone_key" UNIQUE CONSTRAINT, btree (phone)
"profile_username_key" UNIQUE CONSTRAINT, btree (username)
CREATE INDEX profile_index ON profile(uid, username);
This is actually a non cluster index. Now make it to the cluster index
使非集群索引成为集群索引这实际上是一个非簇索引。现在进入集群索引
ALTER TABLE profile CLUSTER ON profile_index;
check the table
\d profile. it will show like thisTable "public.profile" Column | Type | Collation | Nullable | Default ----------+-----------------------+-----------+----------+-------------------------------------- uid | integer | | not null | nextval('profile_uid_seq'::regclass) username | character varying(30) | | not null | phone | character varying(11) | | not null | age | smallint | | | address | text | | | Indexes: "profile_pkey" PRIMARY KEY, btree (uid) "profile_phone_key" UNIQUE CONSTRAINT, btree (phone) "profile_username_key" UNIQUE CONSTRAINT, btree (username) "profile_index" btree (uid, username) CLUSTER Check constraints: "profile_age_check" CHECK (age > 12)See, profile_index now CLUSTER
现在，重新对表进行集群，以便该表可以遵循集群索引角色检查表
\d profile。它会像这样显示Table "public.profile" Column | Type | Collation | Nullable | Default ----------+-----------------------+-----------+----------+-------------------------------------- uid | integer | | not null | nextval('profile_uid_seq'::regclass) username | character varying(30) | | not null | phone | character varying(11) | | not null | age | smallint | | | address | text | | | Indexes: "profile_pkey" PRIMARY KEY, btree (uid) "profile_phone_key" UNIQUE CONSTRAINT, btree (phone) "profile_username_key" UNIQUE CONSTRAINT, btree (username) "profile_index" btree (uid, username) CLUSTER Check constraints: "profile_age_check" CHECK (age > 12)看，profile_index 现在是 CLUSTER
CLUSTER profile;
回答by seb
If you want to know if a given table is CLUSTERed using SQL, you can use the following query to show the index being used (tested in Postgres versions 9.5 and 9.6):
如果您想知道给定的表是否CLUSTER使用 SQL 编辑，您可以使用以下查询来显示正在使用的索引（在 Postgres 9.5 和 9.6 版中测试）：
SELECT
i.relname AS index_for_cluster
FROM
pg_index AS idx
JOIN
pg_class AS i
ON
i.oid = idx.indexrelid
WHERE
idx.indisclustered
AND idx.indrelid::regclass = 'your_table_name'::regclass;
