pgsql指定字段不走索引 sql不走索引的情况

转载

编程梦想编织者 2024-04-22 12:02:12

文章标签 pgsql指定字段不走索引数据库查询优化字段 文章分类 数据仓库大数据

有一个老的问题经常会问道数据库转件就是为什么或者为什么表的索引没有被使用？
下面的文章将会简单的回答这个常见的问题，并按着统计学意义排序。

一、对于外行人来说，你怎么知道索引没有被使用呢？
可以使用 EXPLAIN ; EXPLAIN ANALYZE 或者 pgadmin 的图形执行计划来查看。 ">http://www.postgresonline.com/journal/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html>
如果查询语句确实没有走索引，那么我们应该怎么做呢？

原因：过时的统计信息。如果你的数据库 auto-vacuum 选项是打开的，那么就不会引起这个问题的发生。但是如果你最近刚批量的导入数据，且新建了索引，那么很有可能，统计信息过期，导致查询优化器不能获得最新的统计信息，做出正确的判断。
方案： vacuum analyze verbose sometable_name;

这里增加了 verbose 参数，是因为它可以使你看的更清晰 vacuum 是如何运作的。

原因：查询优化器认为选择 table scan 要比选择 index scan 更快：做出这种选择的原因是，查询涉及的表太小了，或者我们构建索引的字段重复值太多。

方案： 对于 布尔字段构建索引可以认为不是很重要，因为一半是数据，另一半也是数据。不过对这种字段构建部分索引是最好的选择，值索引活动的数据。

构建的索引与实际过滤字段的方式不兼容。这里有很多种解决方法
3.1 LIKE %me 将永远不会选择索引扫描，但是 LIKE me% 就有可能选择索引扫描

3.2 大小写的陷阱，例子如下

<pre><code>
 CREATE INDEX idx_faults_name ON faults USING btree (fault_name);     --- 构建的索引
 --- 查询语句如下
 SELECT * FROM faults WHERE UPPER(FAULT_NAME) LIKE 'CAR%' Possible fix;
--- 解决办法如下
 CREATE INDEX idx_faults_name ON faults  USING BTREE (upper(fault_name));
</code></pre>

3.3 这个问题很难被意识到，通过阅读别人的问题，你就会更好的发现问题。这个问题涉及到了 运算符类

<https://www.postgresql.org/message-id/49075E5E.6040701@gmail.com> 知道这个问题的解决方法的人很少

（
The other point is that in non-C locales, a standard varchar index isn't

usable for LIKE (the sort order is usually wrong). You can re-initdb
in C locale or create a varchar_pattern_ops index.
booktown=# create index first_name_idx_vpo on auth using btree

(first_name varchar_pattern_ops);

）
  解决方案如下：
  <pre><code>
  CREATE INDEX idx_faults_uname_varcahr_parttern on faluts USING BTREE (UPPER (fault_name) varchar_parttern_ops);
  </code></pre>
 看到这个解决方案，可能还需要下面的变体和 *IN* 子句才能解决问题。
 我们并不能证明这是数据库编码的问题， 数据的不同或者数据库版本的不同。 8。2 ， 8.3 。 对于pg8.3 我们推荐使用 *UTF-8* 的编码方式初始化数据库。而对于 8.2 版本是使用的 *SQL-ASCII*， 这个参数 *varcahr_pattern_ops* 对于 *like* 语句是足够解决的。
  <pre><code>
  CREATE INDEX idx_faults_uname ON faults USING btree (upper (falut_name));

 SELECT fault_name from faults WHERE upper(fault_name) IN ( 'CASCADIA ABDUCTION', 'CABIN FEVER');
 </code></pre>
3.4  不兼容的数据类型。
       例如， 我们为日期类型构建索引，但是却通过将日期类型转换为 text 类型， 用在比较语句中， 是不选索引扫描的。