Postgres LIKE'...％'不使用索引 [英] Postgres LIKE '...%' doesn't use index

问题描述

我有一个要在其中搜索主键前缀的表.主键具有03.000221.1，03.000221.2，03.000221.3等值，我想检索所有以03.000221.开头的字符.

I have a table in which I want to search by a prefix of the primary key. The primary key has values like 03.000221.1, 03.000221.2, 03.000221.3, etc. and I want to retrieve all that begin with 03.000221..

我的第一个想法是使用LIKE '03.000221.%'进行过滤，认为Postgres足够聪明，可以在索引中查找03.000221.并从该点开始进行范围扫描.但是，不会，这会执行顺序扫描.

My first thought was to filter with LIKE '03.000221.%', thinking Postgres would be smart enough to look up 03.000221. in the index and perform a range scan from that point. But no, this performs a sequential scan.

                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..253626.34 rows=78 width=669)
   Workers Planned: 2
   ->  Parallel Seq Scan on ...  (cost=0.00..252618.54 rows=32 width=669)
         Filter: ((id ~~ '03.000221.%'::text)
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true

如果我使用普通的>=和<范围进行等效操作，则e. G. id >= '03.000221.' and id < '03.000221.Z'它确实使用索引:

If I do an equivalent operation using a plain >= and < range, e. g. id >= '03.000221.' and id < '03.000221.Z' it does use the index:

                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ... on ...  (cost=0.56..8.58 rows=1 width=669)
   Index Cond: ((id >= '03.000221.'::text) AND (id < '03.000221.Z'::text))

但这比较脏，在我看来，Postgres应该能够推断出它可以用LIKE进行等效的索引范围查找.为什么不呢?

But this is dirtier and it seems to me that Postgres should be able to deduce it can do an equivalent index range lookup with LIKE. Why doesn't it?

推荐答案

如果使用

PostgreSQL will do this if you are build the index with text_pattern_ops operator, or if you are using the C collation.

如果您使用随机的其他排序规则，则PostgreSQL无法推断出任何有关它的内容.通过非常常见的"en_US.utf8"排序规则观察这一点.

If you are using some random other collation, PostgreSQL can't deduce much of anything about it. Observe this, in the very common "en_US.utf8" collation.

select * from (values ('03.000221.1'), ('03.0002212'), ('03.000221.3')) f(x) order by x;
      x      
-------------
 03.000221.1
 03.0002212
 03.000221.3

然后您的查询自然会导致这个错误答案:

Which then naturally leads to this wrong answer with your query:

select * from (values ('03.000221.1'), ('03.0002212'), ('03.000221.3')) f(id)
    where ((id >= '03.000221.'::text) AND (id < '03.000221.Z'::text))
     id      
-------------
 03.000221.1
 03.0002212
 03.000221.3

这篇关于Postgres LIKE'...％'不使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！