一、未做索引的查询效率

作为对比，先对未索引的查询做测试

EXPLAIN ANALYZE select * from gallery_map where author = '曹志耘';

                                                   QUERY PLAN

-----------------------------------------------------------------------------------------------------------------

 Seq Scan on gallery_map  (cost=0.00..7002.32 rows=1025 width=621) (actual time=0.011..39.753 rows=1031 loops=1)

   Filter: ((author)::text = '曹志耘'::text)

   Rows Removed by Filter: 71315

 Planning time: 0.194 ms

 Execution time: 39.879 ms

(5 rows)

Time: 40.599 ms

EXPLAIN ANALYZE select * from gallery_map where author like '曹志耘';

                                                   QUERY PLAN

-----------------------------------------------------------------------------------------------------------------

 Seq Scan on gallery_map  (cost=0.00..7002.32 rows=1025 width=621) (actual time=0.017..41.513 rows=1031 loops=1)

   Filter: ((author)::text ~~ '曹志耘'::text)

   Rows Removed by Filter: 71315

 Planning time: 0.188 ms

 Execution time: 41.669 ms

(5 rows)

Time: 42.457 ms

EXPLAIN ANALYZE select * from gallery_map where author like '曹志耘%';

                                                   QUERY PLAN

-----------------------------------------------------------------------------------------------------------------

 Seq Scan on gallery_map  (cost=0.00..7002.32 rows=1028 width=621) (actual time=0.017..41.492 rows=1031 loops=1)

   Filter: ((author)::text ~~ '曹志耘%'::text)

   Rows Removed by Filter: 71315

 Planning time: 0.307 ms

 Execution time: 41.633 ms

(5 rows)

Time: 42.676 ms

很显然都会做全表扫描

二、创建btree索引

PostgreSQL默认索引是btree

CREATE INDEX ix_gallery_map_author ON gallery_map (author);

EXPLAIN ANALYZE select * from gallery_map where author = '曹志耘';

                                                             QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------

 Bitmap Heap Scan on gallery_map  (cost=36.36..2715.37 rows=1025 width=621) (actual time=0.457..1.312 rows=1031 loops=1)

   Recheck Cond: ((author)::text = '曹志耘'::text)

   Heap Blocks: exact=438

   ->  Bitmap Index Scan on ix_gallery_map_author  (cost=0.00..36.10 rows=1025 width=0) (actual time=0.358..0.358 rows=1031 loops=1)

         Index Cond: ((author)::text = '曹志耘'::text)

 Planning time: 0.416 ms

 Execution time: 1.422 ms

(7 rows)

Time: 2.462 ms

EXPLAIN ANALYZE select * from gallery_map where author like '曹志耘';

                                                             QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------

 Bitmap Heap Scan on gallery_map  (cost=36.36..2715.37 rows=1025 width=621) (actual time=0.752..2.119 rows=1031 loops=1)

   Filter: ((author)::text ~~ '曹志耘'::text)

   Heap Blocks: exact=438

   ->  Bitmap Index Scan on ix_gallery_map_author  (cost=0.00..36.10 rows=1025 width=0) (actual time=0.560..0.560 rows=1031 loops=1)

         Index Cond: ((author)::text = '曹志耘'::text)

 Planning time: 0.270 ms

 Execution time: 2.295 ms

(7 rows)

Time: 3.444 ms

EXPLAIN ANALYZE select * from gallery_map where author like '曹志耘%';

                                                   QUERY PLAN

-----------------------------------------------------------------------------------------------------------------

 Seq Scan on gallery_map  (cost=0.00..7002.32 rows=1028 width=621) (actual time=0.015..41.389 rows=1031 loops=1)

   Filter: ((author)::text ~~ '曹志耘%'::text)

   Rows Removed by Filter: 71315

 Planning time: 0.260 ms

 Execution time: 41.518 ms

(5 rows)

Time: 42.430 ms

EXPLAIN ANALYZE select * from gallery_map where author like '%研究室';

                                                   QUERY PLAN

-----------------------------------------------------------------------------------------------------------------

 Seq Scan on gallery_map  (cost=0.00..7002.32 rows=2282 width=621) (actual time=0.064..52.824 rows=2152 loops=1)

   Filter: ((author)::text ~~ '%研究室'::text)

   Rows Removed by Filter: 70194

 Planning time: 0.254 ms

 Execution time: 53.064 ms

(5 rows)

Time: 53.954 ms

可以看到，等于、like的全匹配是用到索引的，like的模糊查询还是全表扫描

三、创建gin索引

CREATE EXTENSION pg_trgm;

CREATE INDEX ix_gallery_map_author ON gallery_map USING gin (author gin_trgm_ops);

EXPLAIN ANALYZE select * from gallery_map where author like '曹%';

                                                             QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------

 Bitmap Heap Scan on gallery_map  (cost=19.96..2705.69 rows=1028 width=621) (actual time=0.419..1.771 rows=1031 loops=1)

   Recheck Cond: ((author)::text ~~ '曹%'::text)

   Heap Blocks: exact=438

   ->  Bitmap Index Scan on ix_gallery_map_author  (cost=0.00..19.71 rows=1028 width=0) (actual time=0.312..0.312 rows=1031 loops=1)

         Index Cond: ((author)::text ~~ '曹%'::text)

 Planning time: 0.358 ms

 Execution time: 1.916 ms

(7 rows)

Time: 2.843 ms

EXPLAIN ANALYZE select * from gallery_map where author like '%耘%';

                                                   QUERY PLAN

-----------------------------------------------------------------------------------------------------------------

 Seq Scan on gallery_map  (cost=0.00..7002.32 rows=1028 width=621) (actual time=0.015..51.641 rows=1031 loops=1)

   Filter: ((author)::text ~~ '%耘%'::text)

   Rows Removed by Filter: 71315

 Planning time: 0.268 ms

 Execution time: 51.957 ms

(5 rows)

Time: 52.899 ms

EXPLAIN ANALYZE select * from gallery_map where author like '%研究室%';

                                                             QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------

 Bitmap Heap Scan on gallery_map  (cost=31.83..4788.42 rows=2559 width=621) (actual time=0.914..4.195 rows=2402 loops=1)

   Recheck Cond: ((author)::text ~~ '%研究室%'::text)

   Heap Blocks: exact=868

   ->  Bitmap Index Scan on ix_gallery_map_author  (cost=0.00..31.19 rows=2559 width=0) (actual time=0.694..0.694 rows=2402 loops=1)

         Index Cond: ((author)::text ~~ '%研究室%'::text)

 Planning time: 0.306 ms

 Execution time: 4.403 ms

(7 rows)

Time: 5.227 ms

gin_trgm索引的效果好多了

由于pg_trgm的索引是把字符串切成多个3元组，然后使用这些3元组做匹配，所以gin_trgm索引对于少于3个字符（包括汉字）的查询，只有前缀匹配会走索引

另外，还测试了btree_gin，效果和btree一样

注意：
gin_trgm要求数据库必须使用UTF-8编码

demo_v1 #  \l demo_v1

                               List of databases

  Name   |   Owner   | Encoding |   Collate   |    Ctype    | Access privileges

---------+-----------+----------+-------------+-------------+-------------------

 demo_v1 | wmpp_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

参考：

http://www.postgres.cn/docs/9.4/pgtrgm.html
http://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql/10696

本站原创，转载时保留以下信息：

本文转自：深度开源（open-open.com）

原文标题：PostgreSQL LIKE 查询效率提升实验

原文地址：http://www.open-open.com/lib/view/open1463100004089.html

最新文章

1. 有关bootstrap
2. HDU-3549 最大流模板题
3. 《javascript高级程序设计》 第23章 离线应用与客户端存储
4. 修改weblogic PermGen
5. sql查询数据库中所有表的记录条数，以及占用磁盘空间大小。
6. hive UDF函数
7. 项目设计&重构&性能优化
8. rsyslog 直接读取日志,当日志截断后,不会继续发送
9. es5 中类的2种基本实现方法
10. [Kafka] - Kafka Java Consumer实现(一)
11. SqlBulkCopy 批量insert
12. html:table属性cellpadding
13. [bzoj2131]免费的馅饼 树状数组优化dp
14. Ceph神坑系列
15. 关于静态注册BroadcastReceiver接收不到广播的问题
16. 浅析String类
17. .so相关总结
18. Atitit 纯java项目的提升进度大法---通用json dsl接口
19. 【TCP/IP详解 卷一：协议】第十二章 广播与多播 ping实验
20. SQL Server 2008 清空删除日志文件(瞬间日志变几M)

热门文章

1. vue 关于deep watch / computed 监听不到 vuex state 对象变化的的问题
2. Jmeter 线程组、运行次数参数化
3. Matlab的集合运算[转]
4. mybatis-generator自动生成dao,mapping,model
5. MySQL 5.6学习笔记（函数）
6. 腾讯云服务器 - 安装redis3.2.9以及集群
7. mui做的苹果app生成ipa后放到自己的网站上让人下载安装
8. [svc]linux文件权限
9. 一个C#操作Excel类，功能比较全
10. 详细介绍Base64的编码转换方式