主键是否在 postgresql 中自动索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42898988/
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
is primary key automatically indexed in postgresql?
提问by Ram Talreja
I have created table name as d with ID column as primary key and then just inserted records as shown in output, but after fetching all records this output still displayed same as order in which records are inserted. but output as a see now not in ordered form.
我已将表名创建为 d，以 ID 列作为主键，然后只插入记录，如输出所示，但在获取所有记录后，此输出仍显示与插入记录的顺序相同。但输出为现在不是有序的形式。
回答by maximilianus
PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key columns. (See CREATE INDEX for more information.)
PostgreSQL 自动为每个唯一约束和主键约束创建索引以强制唯一性。因此，没有必要为主键列显式创建索引。（有关更多信息，请参阅创建索引。）
Source: Docs
资料来源： 文档
回答by a_horse_with_no_name
but after fetching all records this output still displayed same as order in which records are inserted
但在获取所有记录后，此输出仍显示与插入记录的顺序相同
There is NOdefault "sort" order - even if there is an index on that column (which indeed is the case in Postgres: the primary key is supported by a unique index in the background)
有NO默认的“排序”命令-即使对该列的索引（这的确是Postgres里的情况：主键是唯一索引在后台支持）
Rows in a relational table are not sorted.
关系表中的行没有排序。
The only(really: the only) way to get a specific order is to use an ORDER BY
获得特定订单的唯一（实际上：唯一）方法是使用ORDER BY
If you do not specify an ORDER BYthe database is free to return the rows in any order it wants - and that order can change at any time.
如果您没有指定ORDER BY，数据库可以按它想要的任何顺序自由地返回行 - 并且该顺序可以随时更改。
The order can change because of various reasons:
由于各种原因，订单可能会发生变化：
- other sessions are running the same statement
- the table was updated
- the execution plan changes
- ...
- 其他会话正在运行相同的语句
- 表已更新
- 执行计划改变
- ...
回答by ps2goat
In addition to what the others have said, Postgres does not have a concept of a 'Clustered Index' like Microsoft SQL Server and other databases have. You can cluster an index, but it is a one-time operation (until you call it again) and will not maintain the clustering of rows upon edits, etc. See the docs
除了其他人所说的之外，Postgres 没有像 Microsoft SQL Server 和其他数据库那样具有“聚集索引”的概念。您可以集群索引，但它是一次性操作（直到您再次调用它）并且不会在编辑等时维护行的集群。请参阅文档
I was running into the same thing you were, where I half expected the rows to be returned in order of primary key (I didn't insert them out of order like you did, though). They did come back upon initial insert, but editing a record in Postgres seems to move the record to the end of the page, and the records quickly became out of order (I updated fields other than the primary key).
我遇到了和你一样的事情，我一半希望行按主键的顺序返回（不过，我没有像你那样乱序插入它们）。他们确实在初始插入时返回，但在 Postgres 中编辑记录似乎将记录移动到页面的末尾，并且记录很快变得乱序（我更新了主键以外的字段）。
