PostgreSQL中的索引工作

   Friends ( user_id1 ,user_id2) 

Index(user_id1,user_id2) and Index(user_id2,user_id1)

#1 楼

这个答案是关于（默认）B树索引的。请参阅稍后的有关GiST，GIN等的相关答案：

为什么GiST索引用于在非前导列上进行过滤？


这是查询多列索引第二列上的表的结果。
任何人都可以轻松复制效果。
我在Debian上使用PostgreSQL 9.0.5在中等大小的真实数据库中进行了测试，该数据库具有23322行。它实现了表adr（地址）和att（属性）之间的n：m关系，但这与此处无关。简化的架构：

 CREATE TABLE adratt (
  adratt_id serial PRIMARY KEY
, adr_id    integer NOT NULL
, att_id    integer NOT NULL
, log_up    timestamp NOT NULL DEFAULT (now()::timestamp)
, CONSTRAINT adratt_uni UNIQUE (adr_id, att_id)
);
 

UNIQUE约束有效地实现了唯一索引。为了确保可以得到相同的结果，我用一个普通索引重复了该测试。

CREATE INDEX adratt_idx ON adratt(adr_id, att_id);

该表聚集在adratt_uni索引上，并且在运行测试之前：

CLUSTER adratt;
ANALYZE adratt;

在(adr_id, att_id)上对查询的顺序扫描尽可能快。多列索引仍然可以单独用于第二个索引列上的查询条件。
我运行了几次查询以填充缓存，并从十次运行中挑选了最好的一个以获取可比的结果。 > 1。使用两列进行查询

SELECT *
FROM   adratt
WHERE  att_id = 90
AND    adr_id = 10;

  adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       123 |     10 |     90 | 2008-07-29 09:35:54
(1 row)
 

EXPLAIN ANALYZE的输出：

 Index Scan using adratt_uni on adratt  (cost=0.00..3.48 rows=1 width=20) (actual time=0.022..0.025 rows=1 loops=1)
  Index Cond: ((adr_id = 10) AND (att_id = 90))
Total runtime: 0.067 ms
 

2。使用第一列进行查询

SELECT * FROM adratt WHERE adr_id = 10;

  adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       126 |     10 |     10 | 2008-07-29 09:35:54
       125 |     10 |     13 | 2008-07-29 09:35:54
      4711 |     10 |     21 | 2008-07-29 09:35:54
     29322 |     10 |     22 | 2011-06-06 15:50:38
     29321 |     10 |     30 | 2011-06-06 15:47:17
       124 |     10 |     62 | 2008-07-29 09:35:54
     21913 |     10 |     78 | 2008-07-29 09:35:54
       123 |     10 |     90 | 2008-07-29 09:35:54
     28352 |     10 |    106 | 2010-11-22 12:37:50
(9 rows)
 

EXPLAIN ANALYZE的输出：

 Index Scan using adratt_uni on adratt  (cost=0.00..8.23 rows=9 width=20) (actual time=0.007..0.023 rows=9 loops=1)
  Index Cond: (adr_id = 10)
Total runtime: 0.058 ms
 

3。使用第二列进行查询

SELECT * FROM adratt WHERE att_id = 90;

  adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       123 |     10 |     90 | 2008-07-29 09:35:54
       180 |     39 |     90 | 2008-08-29 15:46:07
...
(83 rows)
 

EXPLAIN ANALYZE的输出：

 Index Scan using adratt_uni on adratt  (cost=0.00..818.51 rows=83 width=20) (actual time=0.014..0.694 rows=83 loops=1)
  Index Cond: (att_id = 90)
Total runtime: 0.849 ms

 

4。禁用indexscan和bitmapscan

SET enable_indexscan = off;
SELECT * FROM adratt WHERE att_id = 90;

EXPLAIN ANALYZE的输出：

 Bitmap Heap Scan on adratt  (cost=779.94..854.74 rows=83 width=20) (actual time=0.558..0.743 rows=83 loops=1)
  Recheck Cond: (att_id = 90)
  ->  Bitmap Index Scan on adratt_uni  (cost=0.00..779.86 rows=83 width=0) (actual time=0.544..0.544 rows=83 loops=1)
        Index Cond: (att_id = 90)
Total runtime: 0.894 ms

 

SET enable_bitmapscan = off;
SELECT * FROM adratt WHERE att_id = 90;

EXPLAIN ANALYZE的输出：

 Seq Scan on adratt  (cost=0.00..1323.10 rows=83 width=20) (actual time=0.009..2.429 rows=83 loops=1)
  Filter: (att_id = 90)
Total runtime: 2.680 ms
 

结论
如预期的那样，多列索引仅用于第二列的查询。
正如预期的那样，它效率较低，但查询速度仍快3倍
禁用索引扫描后，查询计划者将选择位图堆扫描，其执行速度几乎与之相同。仅在禁用该选项后，它才返回顺序扫描。
请参阅其他答案以获取手册的原始引用。
自Postgres 9.0起进行更新
Postgres 13中的所有内容基本上仍然适用。最值得注意的变化：


Postgres 9.2中的仅索引扫描
使用Postgres 11中的INCLUDE关键字真正覆盖索引

多项性能和空间改进（尤其是多列索引）在Postgres 12中。

在Postgres 13中进行重复数据删除。

都支持索引性能。 （不过，顺序扫描也变得更快。）

#2 楼

re 1）是和否。

对于同时使用两列的查询，例如where (user_id1, user_id2) = (1,2)创建哪个索引都没有关系。

对于仅在其中一列有条件的查询，例如这很重要，因为优化器通常只能将“前导”列用于比较。因此，where user_id1 = 1将能够使用索引（user_id1，user_id2），但无法在所有情况下都使用索引（user_id2，user_id1）。

经过研究（在Erwin友善地向我们展示了可以工作的设置之后），这似乎在很大程度上取决于第二列的数据分布，尽管我还没有找到这种情况使优化器可以在WHERE条件下使用尾随列。

Oracle 11也可以（有时）使用不在索引定义开头的列。

re 2）是，它将创建索引

引用手册


添加主键将自动在列或组的btree索引上创建唯一的btree索引。主键中使用的列。


关于2a）where user_id1 = 1将在（user_id1，user_id2）上创建一个索引（只需创建这样的主键就可以很容易地自己找到索引）

我强烈建议您阅读手册中有关索引的章节，它基本上可以回答上述所有问题。

另外，要创建什么索引？ depesz撰写的一篇文章很好地解释了索引列和其他与索引相关的主题的顺序。

#3 楼

广告1）
PostgreSQL中有一些限制，例如@a_horse_with_no_name所描述的。直到8.0版之前，多列索引只能用于前导列的查询。在版本8.1中对此进行了改进。当前的Postgres 10手册（已更新）说明：


多列B树索引可用于查询条件，该条件涉及到索引列的任何子集，但当前（最左侧）
列存在约束时，索引最有效。确切的规则是，前导
列上的相等约束，再加上第一列上没有等式约束的不等式约束，将用于限制
索引中被扫描。在索引中检查了这些列右侧的列上的约束，因此它们可以适当地保存对
表的访问，但是不会减少索引中具有
的部分。扫描。例如，给定(a, b, c)的索引和查询
条件WHERE a = 5 AND b >= 42 AND c < 77，则必须从第一个条目扫描
，其中a = 5并且b = 42直到最后一个
条目a = 5时。将跳过c> = 77的索引条目，
，但仍必须对其进行扫描。在
原则上，该索引可用于对b和/或c有约束但对a没有约束的查询—但是必须扫描整个索引，因此在大多数情况下，
与使用索引相比，计划者更喜欢顺序表扫描。


强调我的想法。我可以从经验中确认。
也可以在测试用例中添加我以后的答案。

#4 楼

这是对杰克回答的答复，不会发表评论。
9.2版之前的PostgreSQL中没有覆盖索引。由于MVCC模型，必须访问结果集中的每个元组以检查可见性。您可能会想到Oracle。
PostgreSQL开发人员谈论“仅索引扫描”。实际上，该功能已随Postgres 9.2一起发布。阅读提交消息。
Depesz写了一篇非常有用的博客文章。
真正的覆盖索引（更新）是通过带有Postgres 11的INCLUDE子句引入的。相关文章：

进行查询带有主键而外键的运行速度比仅带有主键的查询要快吗？

这也有点问题：

它依赖于以下事实：索引的“扫描”通常比索引表的“完全扫描”更快，这是因为表中没有出现在索引中的额外列。

正如我对其他答案的评论中所报告的那样，我还使用两个整数的表进行了测试，仅此而已。索引与表具有相同的列。 btree索引的大小约为表大小的2/3。不足以说明加速因子3。根据您的设置，我进行了更多测试，简化为两列并具有100000行。在我的PostgreSQL 9.0安装上，结果是一致的。
如果表中有其他列，则使用index的加速会变得更充分，但这当然不是唯一的因素。
总结要点：


多列索引可用于对非前导列的查询，但是对于选择条件，提速仅是系数3左右（结果中行的百分比很小）。如果结果很重要，则较大的元组较高，结果表的较大部分较低。


如果性能很重要，请在这些列上创建附加索引。


如果所有涉及的列都包含在索引（覆盖索引）中，并且所有涉及的行（每个块）对于所有事务都是可见的，则可以在pg 9.2或更高版本中获得“仅索引扫描”。

#5 楼

这些等效吗？如果没有，那为什么呢？

索引（user_id1，user_id2）和索引（user_id2，user_id1）




这些通常并不等同于索引（ bar，baz）对于select * from foo where baz=?形式的查询效率不高

Erwin已经证明了这样的索引确实可以加快查询速度，但是这种效果是有限的，并且与您通常期望的顺序不同使用索引来改善查找-它依赖于以下事实：对索引表的“完全扫描”通常比对索引表的“完全扫描”更快，这是因为表中没有出现在索引中的额外列。

摘要：索引甚至可以在非前导列上帮助查询，但是索引可以是二级和相对次要的两种方式之一，而不是通常因为索引由于其btree结构而以戏剧性的方式<<索引可以提供两种帮助的方法是，对索引的完全扫描比对表的完全扫描便宜得多，并且其中之一：
1.表查找便宜（因为例如，它们很少，或者它们是集群的），或者
2.索引被覆盖，因此在所有情况下都没有表查找，请参见此处的Erwins评论。

测试床：

create table foo(bar integer not null, baz integer not null, qux text not null);

insert into foo(bar, baz, qux)
select random()*100, random()*100, 'some random text '||g from generate_series(1,10000) g;

查询1（无索引，命中74个缓冲区）：

explain (buffers, analyze, verbose) select max(qux) from foo where baz=0;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=181.41..181.42 rows=1 width=32) (actual time=3.301..3.302 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=74
   ->  Seq Scan on stack.foo  (cost=0.00..181.30 rows=43 width=32) (actual time=0.043..3.228 rows=52 loops=1)
         Output: bar, baz, qux
         Filter: (foo.baz = 0)
         Buffers: shared hit=74
 Total runtime: 3.335 ms

查询2（带有索引-优化器忽略索引-再次达到74个缓冲区）：

create index bar_baz on foo(bar, baz);

explain (buffers, analyze, verbose) select max(qux) from foo where baz=0;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=199.12..199.13 rows=1 width=32) (actual time=3.277..3.277 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=74
   ->  Seq Scan on stack.foo  (cost=0.00..199.00 rows=50 width=32) (actual time=0.043..3.210 rows=52 loops=1)
         Output: bar, baz, qux
         Filter: (foo.baz = 0)
         Buffers: shared hit=74
 Total runtime: 3.311 ms

查询2（带有索引-我们诱使优化器使用它）：

explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=115.56..115.57 rows=1 width=32) (actual time=1.495..1.495 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=36 read=30
   ->  Bitmap Heap Scan on stack.foo  (cost=73.59..115.52 rows=17 width=32) (actual time=1.370..1.428 rows=52 loops=1)
         Output: bar, baz, qux
         Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
         Buffers: shared hit=36 read=30
         ->  Bitmap Index Scan on bar_baz  (cost=0.00..73.58 rows=17 width=0) (actual time=1.356..1.356 rows=52 loops=1)
               Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
               Buffers: shared read=30
 Total runtime: 1.535 ms

因此，在这种情况下，通过索引访问的速度是30个缓冲区的两倍-在索引方面，它的“速度稍快”！YMMV取决于表和索引的相对大小，与此相反，表中数据的过滤行数和聚类特征

相比之下，对前导列的查询使用索引的btree结构-在这种情况下，将命中2个缓冲区：

explain (buffers, analyze, verbose) select max(qux) from foo where bar=0;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=75.70..75.71 rows=1 width=32) (actual time=0.172..0.173 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=38
   ->  Bitmap Heap Scan on stack.foo  (cost=4.64..75.57 rows=50 width=32) (actual time=0.036..0.097 rows=59 loops=1)
         Output: bar, baz, qux
         Recheck Cond: (foo.bar = 0)
         Buffers: shared hit=38
         ->  Bitmap Index Scan on bar_baz  (cost=0.00..4.63 rows=50 width=0) (actual time=0.024..0.024 rows=59 loops=1)
               Index Cond: (foo.bar = 0)
               Buffers: shared hit=2
 Total runtime: 0.209 ms