postgresql – 具有复合主键的表中的记录顺序是什么

这个问题使得错误的假设是主键强加了一个表顺序.没有PostgreSQL表没有定义的顺序,有或没有主键;它们是排列在页面块中的“堆”行.如果需要,使用查询的ORDER BY子句进行排序.

您可能会认为PostgreSQL表存储为按主键顺序存储在磁盘上的索引导向表,但这不是Pg的工作原理.我认为InnoDB存储由主键组织的表(但尚未检查),并且在某些其他供应商的数据库中使用通常称为“聚簇索引”或“索引组织表”的功能是可选的. PostgreSQL当前不支持此功能(至少9.3).

也就是说,PRIMARY KEY使用UNIQUE索引来实现,并且该索引有一个顺序.它从索引的左侧列(从而是主键)向上排序,就好像是ORDER BY col1 ASC,col2 ASC,col3 ASC ;. PostgreSQL中的任何其他b-tree(与GiST或GIN不同)索引也是如此,因为它们使用b+trees实现.

所以在表中：

CREATE TABLE demo (
   a integer,b text,PRIMARY KEY(a,b)
);

系统会自动创建相当于：

CREATE UNIQUE INDEX demo_pkey ON demo(a ASC,b ASC);

创建表时会向您报告,例如：

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

检查表格时可以看到这个索引：

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,如果页面空间有限,我认为它会尝试.

索引(而不是堆)的固有顺序的一个后果是,搜索速度要快得多：

SELECT * FROM demo ORDER BY a,b;
SELECT * FROM demo ORDER BY a;

比：

SELECT * FROM demo ORDER BY a DESC,b;

并且这些都不能使用主键索引,除非您在b上有索引,否则它们将执行seqscan：

SELECT * FROM demo ORDER BY b,a;
SELECT * FROM demo ORDER BY b;

这是因为PostgreSQL可以使用(a,b)上的索引与(a)上的索引几乎一样快.它不能使用(a,b)上的索引,就像它是(b)上的索引一样 – 甚至不是缓慢的,它只是不能.

对于DESC条目,对于那个Pg,必须进行反向索引扫描,这比普通的前向索引扫描慢.如果您在EXPLAIN ANALYZE中看到大量反向索引扫描,并且您可以承担额外索引的性能成本,您可以在DESC顺序的字段上创建一个索引.

对于WHERE子句而言,这并不只是ORDER BY.您可以使用(a,b)上的索引来搜索WHERE a = 4或WHERE a = 4 AND b = 3,但不能单独搜索WHERE b = 3.