Postgresql - 具有大小写混合的 trigram gin 索引的表或 ILike 不使用索引

Question

三元组索引的表，如果查询中存在大小写混合或 ILike 则不起作用。我不确定我错过了什么。有什么想法吗？

(我使用的是 PostgreSQL 9.6.2)

CREATE TABLE public.tbltest (
    "tbltestId" int NOT null ,
    "mystring1" text,
    "mystring2" character varying,  
    CONSTRAINT "tbltest_pkey" PRIMARY KEY ("tbltestId")
);

insert into tbltest ("tbltestId","mystring1", "mystring2")
select x.id, x.id || ' Test', x.id || ' Test' from generate_series(1,100000) AS x(id);

CREATE EXTENSION pg_trgm;

CREATE INDEX tbltest_idx1 ON tbltest using gin ("mystring1" gin_trgm_ops);
CREATE INDEX tbltest_idx2 ON tbltest using gin ("mystring2" gin_trgm_ops);

在查询中使用小写文本有效，并使用索引

explain analyse 
select * from tbltest 
where "mystring2" Like '%test%';

QUERY PLAN                                                                                                                   |
-----------------------------------------------------------------------------------------------------------------------------|
Bitmap Heap Scan on tbltest  (cost=20.08..56.68 rows=10 width=24) (actual time=29.846..29.846 rows=0 loops=1)                |
  Recheck Cond: ((mystring2)::text ~~ '%test%'::text)                                                                        |
  Rows Removed by Index Recheck: 100000                                                                                      |
  Heap Blocks: exact=726                                                                                                     |
  ->  Bitmap Index Scan on tbltest_idx2  (cost=0.00..20.07 rows=10 width=0) (actual time=12.709..12.709 rows=100000 loops=1) |
        Index Cond: ((mystring2)::text ~~ '%test%'::text)                                                                    |
Planning time: 0.086 ms                                                                                                      |
Execution time: 29.875 ms                                                                                                    |

如果我在搜索中添加混合大小写，则 Like 不会使用索引

explain analyse 
select * from tbltest 
where "mystring2" Like '%Test%';

QUERY PLAN                                                                                                    |
--------------------------------------------------------------------------------------------------------------|
Seq Scan on tbltest  (cost=0.00..1976.00 rows=99990 width=24) (actual time=0.011..33.376 rows=100000 loops=1) |
  Filter: ((mystring2)::text ~~ '%Test%'::text)                                                               |
Planning time: 0.083 ms                                                                                       |
Execution time: 51.259 ms                                                                                     |

ILike也不使用索引

explain analyse 
select * from tbltest 
where "mystring2" ILike '%Test%';

QUERY PLAN                                                                                                    |
--------------------------------------------------------------------------------------------------------------|
Seq Scan on tbltest  (cost=0.00..1976.00 rows=99990 width=24) (actual time=0.012..87.038 rows=100000 loops=1) |
  Filter: ((mystring2)::text ~~* '%Test%'::text)                                                              |
Planning time: 0.134 ms                                                                                       |
Execution time: 105.757 ms                                                                                    |

Answer 1

PostgreSQL 没有在最后两个查询中使用索引，因为这是处理查询的最佳方式，而不是因为它不能使用它。

在您的 EXPLAIN 输出中，您可以看到第一个查询返回零行(actual ... rows=0)，而其他两个查询返回每一行在表格中(actual ... rows=100000)。

PostgreSQL 优化器的估计准确地反射(reflect)了这种情况。

由于它无论如何都必须访问表的大部分行，因此 PostgreSQL 知道如果它按顺序扫描表比使用更复杂的索引访问方法获得结果的成本要低得多。