主页 / 问题 / 954679
Accepted
Pavel
Asked:2020-03-10 21:39:44 +0000 UTC2020-03-10 21:39:44 +0000 UTC

为什么非聚集索引不起作用？

  • 772

有一个没有聚集索引或主键的简单表示例：

create table non_pk (a int, b int);

已为此表创建索引：

create index non_pk_idx on non_pk (a, b);

索引表确认创建成功：

SELECT * FROM pg_indexes WHERE tablename = 'non_pk'                                                                                                                                                ;
 schemaname | tablename | indexname  | tablespace |                       indexdef                       
------------+-----------+------------+------------+------------------------------------------------------
 public     | non_pk    | non_pk_idx |            | CREATE INDEX non_pk_idx ON non_pk USING btree (a, b)

但是在查询这些字段之后：

select * from non_pk where a = 4 and b = 5;
 a | b 
---+---
 4 | 5
(1 row)

统计信息表未显示在搜索索引字段时使用了索引：

select relname, seq_scan, idx_scan from pg_stat_all_tables where relname = 'non_pk';
 relname | seq_scan | idx_scan 
---------+----------+----------
 non_pk  |       10 |        0
(1 row)

为什么索引不起作用以及在这种情况下需要什么才能使查询的搜索select * from non_pk where a = 4 and b = 5;使用索引create index non_pk_idx on non_pk (a, b);？

sql

1 个回答

  1. Best Answer

    PostgreSQL 依赖于基于成本的查询优化器。也就是说，为请求创建了许多可能的执行计划，每个执行计划都有其在某些鹦鹉中执行的成本。然后选择执行选项，结果证明在鹦鹉中更便宜。并不是说它实际上比其他的快，而是优化器也必须快，所以采用了许多假设、统计数据和黑魔法，它通常可以工作。

    索引查找并不总是比全表搜索快。（从调度程序的角度来看，在这个问题的上下文中更重要的是并不总是更便宜）对于小表和大表（特别是如果你想阅读表的显着部分），按索引跳转查找可能比按顺序遍历所有内容要慢。而且通常计划者的决定是正确的。因此，在一个表中生成一定量的数据（一万行）并至少在这么小的样本上进行测试是有意义的，而不是在空表上进行测试。

    您不能在 PostgreSQL 的查询中强制使用索引。没有调度程序提示，开发人员社区显然拒绝添加此类提示，更愿意接收错误报告并在必要时修复调度程序。

    在考虑特定请求时，使用起来很方便explain：

    explain select * from non_pk where a = 4 and b = 5;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Index Only Scan using non_pk_idx on non_pk  (cost=0.15..8.17 rows=1 width=8)
   Index Cond: ((a = 4) AND (b = 5))

    explain将显示调度程序对此请求的看法。同样（通常更）有用explain analyze，它将运行查询并显示它是如何：

    explain analyze select * from non_pk where a = 4 and b = 5;
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using non_pk_idx on non_pk  (cost=0.15..8.17 rows=1 width=8) (actual time=0.046..0.046 rows=0 loops=1)
   Index Cond: ((a = 4) AND (b = 5))
   Heap Fetches: 0
 Planning Time: 0.101 ms
 Execution Time: 0.075 ms

    如您所见，在我的系统上，调度程序决定使用索引。

    然而，阅读和理解explain更接近于一种独立的艺术形式。网上有很多关于此的材料，但回答附带问题的格式太窄而无法描述。

    PS：postgresql 中没有聚集索引（包括版本 12 的未来版本）。即使你执行了命令CLUSTER，数据库以后也不会尝试保持这个顺序。

    • 1

相关问题