主页 / dba / 问题 / 6115
Accepted
codecool
Asked: 2011-09-25 06:00:16 +0800 CST

PostgreSQL中索引的工作

  • 772

我有几个关于 PostgreSQL 中索引工作的问题。我有一个Friends带有以下索引的表：

   Friends ( user_id1 ,user_id2)

user_id1并且是表user_id2的外键user

  1. 这些是等价的吗？如果不是那为什么？

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

  2. 如果我创建主键（user_id1，user_id2），它会自动为它创建索引吗？

    如果第一个问题中的索引不相等，那么在上面的主键命令上创建了哪个索引？

postgresql index

5 个回答

  1. Best Answer

    这个答案是关于（默认）B-tree索引。稍后见，有关 GiST、GIN 等的相关答案：

    以下是在多列索引的第二列上查询表的结果。
    任何人都可以轻松复制这些效果。在家里试试。

    我在 Debian 上使用 23322 行的中型数据库表在 Debian 上测试了 PostgreSQL 9.0.5 。adr它实现了表（地址）和（属性）之间的 n:m 关系att，但这与这里无关。简化模式：

    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 中，一切仍然基本正确。最显着的变化：

    都支持指数表现。（不过，顺序扫描也变得更快了。）

    • 94

  2. 重新 1) 是和否。

    对于同时使用两列的查询，例如where (user_id1, user_id2) = (1,2)创建哪个索引并不重要。

    对于仅在其中一个列上具有条件的查询，例如where user_id1 = 1它确实很重要，因为通常只有“前导”列可以用于优化器的比较。因此where user_id1 = 1可以使用索引 (user_id1, user_id2) 但不能在所有情况下都使用索引 (user_id2, user_id1)。

    在玩过这个之后（在 Erwin 好心地向我们展示了它的工作设置之后），似乎这在很大程度上取决于第二列的数据分布，尽管我还没有发现哪种情况使优化器能够使用尾随列对于 WHERE 条件。

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

    re 2) 是的，它会创建一个索引

    从手册中引用

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

    re 2a)Primary Key (user_id1,user_id2)将在 (user_id1,user_id2) 上创建一个索引（您可以通过简单地创建这样的主键很容易地自己找到它）

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

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

    • 32

  3. 广告 1)
    PostgreSQL 中有一些限制，如 @a_horse_with_no_name 描述的。在8.0 版之前，多列索引只能用于对前导列的查询。这在 8.1 版中得到了改进。Postgres 14 （更新）的当前手册解释说：

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

    强调我的。我可以从经验中证实这一点。
    另请参阅测试用例在此处添加了我稍后的答案。

    • 12

  4. 这是对杰克的回答的回复，评论不会做。

    在9.2 版本之前的 PostgreSQL中没有覆盖索引。由于 MVCC 模型，必须访问结果集中的每个元组以检查可见性。您可能正在考虑 Oracle。

    PostgreSQL 开发人员谈论“仅索引扫描”。事实上，该功能已随 Postgres 9.2 一起发布。阅读提交信息。
    Depesz 写了一篇内容丰富的博客文章。

    INCLUDEPostgres 11的子句引入了真正的覆盖索引（更新）。相关：

    这也有点不对劲：

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

    正如我在其他答案的评论中所报告的那样，我还使用一个包含两个整数的表运行测试，仅此而已。索引包含与表相同的列。btree 索引的大小约为表的 2/3。不足以解释因子 3 的加速。根据您的设置，我运行了更多测试，简化为两列和 100000 行。在我的 PostgreSQL 9.0 安装中，结果是一致的。

    如果表有额外的列，索引的加速会变得更加显着，但这肯定不是唯一的因素。

    概括

    • 多列索引可用于对非前导列进行查询的选择性标准，但根据表和索引元组的大小和可见性，加速只是一个很小的因素。较高的行表示较宽的行，较低的表示结果集中表的较大部分。

    • 如果性能很重要，请首先使用这些列创建一个附加索引。

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

    • 12

    1. 这些是等价的吗？如果不是那为什么？

      索引(user_id1,user_id2) 和索引(user_id2,user_id1)

    这些不是等效的，一般来说 index(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
    • 8

相关问题