PostgreSQL LIKE 查询效率提升实验
一、未做索引的查询效率作为对比,先对未索引的查询做测试 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
where author like -----------------------------------------------------------------------------------------------------------------
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
'曹志耘%';
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);
'曹志耘';
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
'曹志耘';
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
----------------------------------------------------------------------------------------------------------------- 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 '%研究室';
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;
ON gallery_map USING gin (author gin_trgm_ops);
'曹%';
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
'%耘%';
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
'%研究室%';
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
由于pg_trgm的索引是把字符串切成多个3元组,然后使用这些3元组做匹配,所以gin_trgm索引对于少于3个字符(包括汉字)的查询,只有前缀匹配会走索引 另外,还测试了btree_gin,效果和btree一样 注意: 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
本站原创,转载时保留以下信息: 本文转自:深度开源(open-open.com) 原文标题:PostgreSQL LIKE 查询效率提升实验 原文地址:http://www.open-open.com/lib/view/open1463100004089.html (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |