主页 / dba / 问题 / 16612
Accepted
dezso
Asked: 2012-04-18 03:34:27 +0800 CST

简单连接中未使用的主键索引

  • 772

我有以下表格和索引定义：

CREATE TABLE munkalap (
    munkalap_id serial PRIMARY KEY,
    ...
);

CREATE TABLE munkalap_lepes (
    munkalap_lepes_id serial PRIMARY KEY,
    munkalap_id integer REFERENCES munkalap (munkalap_id),
    ...
);

CREATE INDEX idx_munkalap_lepes_munkalap_id ON munkalap_lepes (munkalap_id);

为什么以下查询中没有使用 munkalap_id 上的任何索引？

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id);

QUERY PLAN
Hash Join  (cost=119.17..2050.88 rows=38046 width=214) (actual time=0.824..18.011 rows=38046 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.005..4.574 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=3252 width=4) (actual time=0.810..0.810 rows=3253 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 115kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=3252 width=4) (actual time=0.003..0.398 rows=3253 loops=1)
Total runtime: 19.786 ms

即使我添加一个过滤器也是一样的：

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id) WHERE NOT lezarva;

QUERY PLAN
Hash Join  (cost=79.60..1545.79 rows=1006 width=214) (actual time=0.616..10.824 rows=964 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.007..5.061 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=86 width=4) (actual time=0.587..0.587 rows=87 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 4kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=86 width=4) (actual time=0.014..0.560 rows=87 loops=1)
              Filter: (NOT lezarva)
Total runtime: 10.911 ms
postgresql performance

2 个回答

  1. Best Answer

    许多人听说过“顺序扫描不好”的指导，并试图将其从计划中消除，但这并不是那么简单。如果查询要覆盖表中的每一行，顺序扫描是获取这些行的最快方法。这就是您的原始连接查询使用 seq 扫描的原因，因为两个表中的所有行都是必需的。

    在规划查询时，Postgres 的规划器会估计不同可能方案下各种操作（计算、顺序和随机 IO）的成本，并选择它估计成本最低的计划。从旋转存储（磁盘）进行 IO 时，随机 IO 通常比顺序 IO 慢很多，random_page_cost 和 seq_page_cost的默认 pg 配置估计成本差异为 4:1。

    在考虑使用索引的连接或过滤方法与顺序扫描表的方法时，这些考虑因素会起作用。使用索引时，计划可能会通过索引快速找到一行，然后必须考虑随机读取块来解析行数据。在您的第二个查询添加了过滤谓词的情况下WHERE NOT lezarva，您可以看到这如何影响 EXPLAIN ANALYZE 结果中的计划估计。规划器估计连接产生的 1006 行（与实际结果集 964 非常接近）。鉴于较大的表 munkalap_lepes 包含大约 38K 行，规划器看到连接将不得不访问表中大约 1006/38046 或 1/38 的行。它还知道平均行宽是 214 字节，一个块是 8K，所以大约有 38 行/块。

    有了这些统计信息，计划者认为连接很可能必须读取所有或大部分表的数据块。由于索引查找也不是免费的，并且相对于 IO，扫描评估过滤条件的块的计算非常便宜，因此规划器选择顺序扫描表并在计算 seq 扫描时避免索引开销和随机读取会更快。

    在现实世界中，数据通常通过 OS 页面缓存在内存中可用，因此并非每个块读取都需要 IO。很难预测缓存对给定查询的有效性，但 Pg 计划器确实使用了一些简单的启发式方法。配置值Effective_cache_size 通知规划者估计产生实际 IO 成本的可能性。较大的值将导致它估计随机 IO 的成本较低，因此可能会将其偏向于索引驱动方法而不是顺序扫描。

    • 30

  2. 您正在从两个表中检索所有行，因此使用索引扫描并没有真正的好处。仅当您从表中选择几行（通常小于 10%-15%）时，索引扫描才有意义

    • 8

相关问题