【PostgreSQL】 字首模糊查詢級優化

monkjavaer發表於2019-07-08
SQL優化

前匹配模糊

使用B-Tree來加速優化前匹配模糊查詢

構造資料

新建一張商品表，插入一千萬條資料。

create table goods(id int, name varchar);

insert into goods select generate_series(1,10000000),md5(random()::varchar);

未建立索引時查詢"123%"的商品名字，執行計劃顯示耗時大約為575ms：

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' ;
========================================
Gather  (cost=1000.00..136516.59 rows=1000 width=37) (actual time=1.390..572.857 rows=2364 loops=1)
  Output: id, name
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=83334
  ->  Parallel Seq Scan on public.goods  (cost=0.00..135416.59 rows=417 width=37) (actual time=0.750..528.116 rows=788 loops=3)
        Output: id, name
        Filter: ((goods.name)::text ~~ '123%'::text)
        Rows Removed by Filter: 3332545
        Buffers: shared hit=83334
        Worker 0: actual time=1.032..511.776 rows=676 loops=1
          Buffers: shared hit=24201
        Worker 1: actual time=0.145..511.737 rows=755 loops=1
          Buffers: shared hit=26101
Planning time: 0.065 ms
Execution time: 573.157 ms

優化1，建立索引，lc_collate方式（B-Tree）

lc_collate (string)
報告文字資料排序使用的區域。詳見第 23.1 節。該值是在資料庫被建立時確定的。

lc_collate (string)
報告文字資料排序使用的區域。詳見第 23.1 節。該值是在資料庫被建立時確定的。

create index idx_c on goods(name collate "C");

執行計劃顯示耗時為10ms以內：

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' collate "C";

優化2，建立索引，操作符類varchar_pattern_ops方式

create index idx_varchar on goods(name varchar_pattern_ops);

執行計劃顯示耗時為5ms以內：

explain (analyze,verbose,timing,costs,buffers) select * from goods where name like '123%' collate "C";

======================================
Bitmap Heap Scan on public.goods  (cost=86.60..7681.10 rows=1000 width=37) (actual time=0.740..4.628 rows=2364 loops=1)
  Output: id, name
  Filter: ((goods.name)::text ~~ '123%'::text)
  Heap Blocks: exact=2330
  Buffers: shared hit=2351
  ->  Bitmap Index Scan on idx_varchar  (cost=0.00..86.35 rows=2179 width=0) (actual time=0.487..0.487 rows=2364 loops=1)
        Index Cond: (((goods.name)::text ~>=~ '123'::text) AND ((goods.name)::text ~<~ '124'::text))
        Buffers: shared hit=21
Planning time: 0.139 ms
Execution time: 4.891 ms

PostgreSQL擴充套件

關鍵字ILIKE可以替換LIKE， 表示大小寫無關，這是一個PostgreSQL擴充套件。

參考

https://www.postgresql.org/docs/10/indexes-opclass.html
https://github.com/digoal/blog/blob/master/201704/20170426_01.md

相關文章