ruby-on-rails - 如何提高 ILIKE Performance？

Question

我的应用程序有一个页面，其中显示了以特定字母开头的州的所有城市。

例如:

State: Alabama, Page A
--> All cities in Alabama starting with alphabet 'A'

这是我的查询

City.where(state: 'Alabama').where("name ilike?", "a%")

此查询需要大约 110 - 140 毫秒。有什么方法可以将查询时间缩短到 <10 毫秒。

提前致谢:)

Answer 1

PostgreSQL 不为 LIKE 运算符使用通常的索引

postgres=# create index on obce(nazev);
CREATE INDEX
Time: 120.605 ms
postgres=# explain analyze select * from obce where nazev like 'P%';
┌─────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                             QUERY     PLAN                                              │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Seq Scan on obce  (cost=0.00..137.12 rows=435 width=41) (actual time=0.023..2.345 rows=450 loops=1) │
│   Filter: ((nazev)::text ~~ 'P%'::text)                                                             │
│   Rows Removed by Filter: 5800                                                                      │
│ Planning time: 0.485 ms                                                                             │
│ Execution time: 2.413 ms                                                                            │
└─────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)

您应该使用带有 varchar_pattern_ops 关键字的特殊语法

postgres=# create index on obce(nazev varchar_pattern_ops);
CREATE INDEX
Time: 124.709 ms
postgres=# explain analyze select * from obce where nazev like 'P%';
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                         QUERY PLAN                                                          │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Bitmap Heap Scan on obce  (cost=12.39..76.39 rows=435 width=41) (actual time=0.291..0.714 rows=450 loops=1)                 │
│   Filter: ((nazev)::text ~~ 'P%'::text)                                                                                     │
│   Heap Blocks: exact=58                                                                                                     │
│   ->  Bitmap Index Scan on obce_nazev_idx1  (cost=0.00..12.28 rows=400 width=0) (actual time=0.253..0.253 rows=450 loops=1) │
│         Index Cond: (((nazev)::text ~>=~ 'P'::text) AND ((nazev)::text ~<~ 'Q'::text))                                      │
│ Planning time: 0.953 ms                                                                                                     │
│ Execution time: 0.831 ms                                                                                                    │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

但这对 ILIKE 不起作用 - 解决方法可以是功能索引:

create index on obce(upper(nazev) varchar_pattern_ops);
select * from obce where upper(nazev) like upper('P%');

注意:“Nazev”是捷克语的名字

另一种可能性是使用 pg_trgm扩展和使用三元组索引。它适用于 LIKE、ILIKE，但索引要大得多 - 对于相对较小的静态表来说这不是问题。

create extension pg_trgm ;
create index on obce using gin (nazev gin_trgm_ops);

postgres=# explain analyze select * from obce where nazev like 'P%';
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                         QUERY PLAN                                                          │
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Bitmap Heap Scan on obce  (cost=15.37..79.81 rows=435 width=41) (actual time=0.327..0.933 rows=450 loops=1)                 │
│   Recheck Cond: ((nazev)::text ~~ 'P%'::text)                                                                               │
│   Rows Removed by Index Recheck: 134                                                                                        │
│   Heap Blocks: exact=58                                                                                                     │
│   ->  Bitmap Index Scan on obce_nazev_idx1  (cost=0.00..15.26 rows=435 width=0) (actual time=0.287..0.287 rows=584 loops=1) │
│         Index Cond: ((nazev)::text ~~ 'P%'::text)                                                                           │
│ Planning time: 0.359 ms                                                                                                     │
│ Execution time: 1.056 ms                                                                                                    │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘    
(8 rows)