<h2>一、未做索引的查询效率</h2>    <p>作为对比，先对未索引的查询做测试</p>    <pre>  <code class="language-sql">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>    <pre>  <code class="language-sql">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>    <pre>  <code class="language-sql">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>    <p>很显然都会做全表扫描</p>    <h2>二、创建btree索引</h2>    <p>PostgreSQL默认索引是btree</p>    <pre>  <code class="language-sql">CREATE INDEX ix_gallery_map_author ON gallery_map (author);    </code></pre>    <pre>  <code class="language-sql">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>    <pre>  <code class="language-sql">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>    <pre>  <code class="language-sql">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>    <pre>  <code class="language-sql">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>    <p>可以看到，等于、like的全匹配是用到索引的，like的模糊查询还是全表扫描</p>    <h2>三、创建gin索引</h2>    <pre>  <code class="language-sql">CREATE EXTENSION pg_trgm;    CREATE INDEX ix_gallery_map_author ON gallery_map USING gin (author gin_trgm_ops);</code></pre>    <pre>  <code class="language-sql">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>    <pre>  <code class="language-sql">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>    <pre>  <code class="language-sql">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>    <p><br> gin_trgm索引的效果好多了</p>    <p>由于pg_trgm的索引是把字符串切成多个3元组，然后使用这些3元组做匹配，所以gin_trgm索引对于少于3个字符（包括汉字）的查询，只有前缀匹配会走索引</p>    <p>另外，还测试了btree_gin，效果和btree一样</p>    <p>注意：<br> gin_trgm要求数据库必须使用UTF-8编码</p>    <pre>  <code class="language-sql">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>    <p>参考：</p>    <p>http://www.postgres.cn/docs/9.4/pgtrgm.html<br> http://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql/10696</p>    <p> </p>    <p>本站原创，转载时保留以下信息：</p>    <p>本文转自：深度开源（open-open.com）</p>    <p>原文标题：<a href="http://www.open-open.com/lib/view/open1463100004089.html">PostgreSQL LIKE 查询效率提升实验</a></p>    <p>原文地址：<a href="http://www.open-open.com/lib/view/open1463100004089.html">http://www.open-open.com/lib/view/open1463100004089.html</a></p>