问题描述
在PostgreSQL中，当多个列的组合指定为 PRIMARY KEY 时，记录的顺序如何？
In PostgreSQL, when a combination of multiple columns is specified as the PRIMARY KEY, how are the records ordered?
这是假设PostgreSQL按照主键的顺序对记录进行排序。
This is with the assumption that PostgreSQL orders the records in the order of the primary key. Does it?
此外，如果使用PostgreSQL，主键是否会自动索引？
Also, is the primary key automatically indexed in case of PostgreSQL?
推荐答案
这个问题做出了一个误导性的假设，即主键完全强加了一个表顺序。没错PostgreSQL表没有定义的顺序，带有或不带有主键。它们是按页面块排列的堆行。如果需要，可以使用查询的 ORDER BY 子句强制执行排序。
This question makes the misguided assumption that the primary key imposes a table order at all. It doesn't. PostgreSQL tables have no defined order, with or without a primary key; they're a "heap" of rows arranged in page blocks. Ordering is imposed using the ORDER BY clause of queries when desired.
您可能会认为PostgreSQL表已存储作为面向索引的表以主键顺序存储在磁盘上，但这不是Pg的工作方式。我认为InnoDB存储按主键组织的表（但尚未检查），并且在其他一些供应商的数据库中是可选的，使用的功能通常称为聚集索引或索引组织表。 PostgreSQL当前不支持此功能（至少从9.3开始）。
You might be thinking that PostgreSQL tables are stored as index-oriented tables that're stored on disk in primary key order, but that isn't how Pg works. I think InnoDB stores tables organized by the primary key (but haven't checked), and it's optional in some other vendors' databases using a feature often called "clustered indexes" or "index-organized tables". This feature isn't currently supported by PostgreSQL (as of 9.3 at least).
也就是说， PRIMARY KEY 是使用 UNIQUE 索引实现的，该索引有一个顺序。它从索引的左列（因此为主键）开始按升序排序，就好像它是 ORDER BY col1 ASC，col2 ASC，col3 ASC; 。 PostgreSQL中的其他b树索引（不同于GiST或GIN）也是如此，因为它们是使用。
That said, the PRIMARY KEY is implemented using a UNIQUE index, and there is an ordering to that index. It is sorted in ascending order from the left column of the index (and therefore the primary key) onward, as if it were ORDER BY col1 ASC, col2 ASC, col3 ASC;. The same is true of any other b-tree (as distinct from GiST or GIN) index in PostgreSQL, as they're implemented using b+trees.
因此在表中：
CREATE TABLE demo (
a integer,
b text,
PRIMARY KEY(a,b)
);
系统将自动创建以下项：
the system will automatically create the equivalent of:
CREATE UNIQUE INDEX demo_pkey ON demo(a ASC, b ASC);
创建表时会向您报告，例如：
This is reported to you when you create a table, eg:
regress=> CREATE TABLE demo (
regress(> a integer,
regress(> b text,
regress(> PRIMARY KEY(a,b)
regress(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "demo_pkey" for table "demo"
CREATE TABLE
检查表时可以看到此索引：
You can see this index when examining the table:
regress=> \d demo
Table "public.demo"
Column | Type | Modifiers
--------+---------+-----------
a | integer | not null
b | text | not null
Indexes:
"demo_pkey" PRIMARY KEY, btree (a, b)
您可以在此索引上的 CLUSTER 来根据主键，但这是一次操作。系统不会维持该顺序-尽管如果由于非默认 FILLFACTOR 而导致页面中有可用空间，
You can CLUSTER on this index to re-order the table according to the primary key, but it's a one-time operation. The system won't maintain that ordering - though if there's space free in the pages due to a non-default FILLFACTOR I think it will try to.
固有的或索引（而不是堆）的目的是，它搜索快得多：
One consequence of the inherent ordering of the index (but not the heap) is that it is much faster to search for:
SELECT * FROM demo ORDER BY a, b;
SELECT * FROM demo ORDER BY a;
比：
SELECT * FROM demo ORDER BY a DESC, b;
这些都不可以使用主键索引，除非您不进行任何操作，否则它们将进行seqscan在 b 上有一个索引：
and neither of these can use the primary key index at all, they'll do a seqscan unless you have an index on b:
SELECT * FROM demo ORDER BY b, a;
SELECT * FROM demo ORDER BY b;
这是因为PostgreSQL可以使用（a，b）几乎与仅（a）的索引一样快。它不能仅使用（a，b）上的索引，就好像仅使用（b）上的索引一样-不能
This is becaues PostgreSQL can use an index on (a,b) almost as fast as an index on (a) alone. It cannot use an index on (a,b) as if it were an index on (b) alone - not even slowly, it just can't.
对于 DESC 条目，因为一个Pg必须执行反向索引扫描，它比普通的正向索引扫描慢。如果在 EXPLAIN ANALYZE 中看到大量反向索引扫描，并且您负担得起额外索引的性能成本，则可以在<$ c $中的字段上创建索引c> DESC 订单。
As for the DESC entry, for that one Pg must do a reverse index scan, which is slower than an ordinary forward index scan. If you're seeing lots of reverse index scans in EXPLAIN ANALYZE and you can afford the performance cost of the extra index you can create an index on the field in DESC order.
对于 WHERE 子句，不仅是 ORDER BY 。您可以在（a，b）上使用索引来搜索 WHERE a = 4 或其中a = 4和b = 3 ，但不是仅搜索 WHERE b = 3 。
This is true for WHERE clauses, not just ORDER BY. You can use an index on (a,b) to search for WHERE a = 4 or WHERE a = 4 AND b = 3 but not to search for WHERE b = 3 alone.
这篇关于具有复合主键的表中记录的顺序是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！