7m篮球比分即时比分网:PostgreSQL LIKE 查詢效率提升實驗

wuxqing 4年前發布 | 45K 次閱讀 性能優化 PostgreSQL 數據庫服務器

篮球比分188直播 www.703192.live

一、未做索引的查詢效率

作為對比,先對未索引的查詢做測試

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 </code></pre>

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

</code></pre>

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</code></pre>

很顯然都會做全表掃描

二、創建btree索引

PostgreSQL默認索引是btree

CREATE INDEX ix_gallery_map_author ON gallery_map (author);

</code></pre>

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

</code></pre>

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 </code></pre>

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</code></pre>

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 </code></pre>

可以看到,等于、like的全匹配是用到索引的,like的模糊查詢還是全表掃描

三、創建gin索引

CREATE EXTENSION pg_trgm;

CREATE INDEX ix_gallery_map_author ON gallery_map USING gin (author gin_trgm_ops);</code></pre>

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</code></pre>

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</code></pre>

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</code></pre>


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 |

</code></pre>

參考:

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

 

本站原創,轉載時保留以下信息:

本文轉自:深度開源(www.703192.live)

原文標題:PostgreSQL LIKE 查詢效率提升實驗

原文地址://www.703192.live/lib/view/open1463100004089.html

 本文由用戶 wuxqing 自行上傳分享,僅供網友學習交流。所有權歸原作者,若您的權利被侵害,請聯系管理員。
 轉載本站原創文章,請注明出處,并保留原始鏈接、圖片水印。
 本站是一個以用戶分享為主的開源技術平臺,歡迎各類分享!