在PostgreSQL中创建多列索引

我现在正在研究为表创建索引。

我发现了有关多列索引的信息,但不确定其影响。 例:

We have SQLs on findById, findByIdAndStatus, findByResult.

It says that the most used on WHERE should be listed first in the columns list. But I was wondering if it'll have a huge impact if I create index on different combination where clauses.

这:(为所有索引创建一个索引)

CREATE INDEX CONCURRENTLY ON Students (id, status, result)

这:(在不同的查询上创建不同的索引)

CREATE INDEX CONCURRENTLY ON Students (id)

CREATE INDEX CONCURRENTLY ON Students (id, status)

CREATE INDEX CONCURRENTLY ON Students (result)

提前非常感谢您!

评论
  • 坏pi气
    坏pi气 回复

    您阅读的经验法则是错误的。 更好的规则是:仅在索引有用且经常被使用时才创建这样的索引,以使值得每个索引附带的数据修改性能降低。

    A multi-column B-tree index on (a, b, c) is useful in several cases:

    1. if the query looks like this:

      SELECT ... FROM tab
      WHERE a = $1 AND b = $2 AND c <operator> $3
      

      where <operator> is an operator supported by the index and $1, $2 and $3 are constants.

    2. if the query looks like this:

      SELECT ... FROM tab
      WHERE a = $1 AND b = $2
      ORDER BY c;
      

      or like this

      SELECT ... FROM tab
      WHERE a = $1
      ORDER BY b, c;
      

      Any decorations in the ORDER BY clause must be reflected in the CREATE INDEX statement. For example, for ORDER BY b, c DESC the index must be created on (a, b, c DESC) or (a, b DESC, c) (indexes can be read in both directions).

    3. if the query looks like this:

      SELECT c
      FROM tab
      WHERE a = $1 AND b <operator> $2;
      

      If the table is newly VACUUMed, this can get you an index only scan, because all required information is in the index.

      In recent PostgreSQL versions, such an index in better created as

      CREATE INDEX ON tab (a, b) INCLUDE (c);
      
  • 禁锢的誓言
    禁锢的誓言 回复

    为所有人创建一个索引并创建不同的索引将对查询产生完全不同的影响。

    1. You can use EXPLAIN to see if indexes are getting used for the queries.

    2. This video is really good to know about DB indexes.

    3. Index CREATE INDEX CONCURRENTLY ON Students (id, status, result) will be used only and only if query uses id, (id,status) or (id, status and result) in WHERE clause. a query with status in Where will not use this index at all.

    索引基本上是平衡的二叉树。多列索引将按id对行进行索引,然后按id排序的行将进一步按状态(然后是结果)进行索引。 您可以看到在该索引中,根本没有通过状态进行排序。它仅在以ID的第一索引索引的行上可用。

    一定要看一下视频,它很好地解释了所有这些。