django - 使用 RegEx 的 Heroku Postgres 性能

Question

我有一个 iPhone 应用程序连接到在 Heroku 上运行的 Django 服务器。用户点击一个词(如“cape”)，然后向服务器查询包含该词的任何其他段落。所以现在我用一些 RegEx 做一个 SQL 查询:

SELECT "connectr_passage"."id", "connectr_passage"."third_party_id", "connectr_passage"."third_party_created", "connectr_passage"."source", "connectr_passage"."text", "connectr_passage"."author", "connectr_passage"."raw_data", "connectr_passage"."retweet_count", "connectr_passage"."favorited_count", "connectr_passage"."lang", "connectr_passage"."location", "connectr_passage"."author_followers_count", "connectr_passage"."created", "connectr_passage"."modified" FROM "connectr_passage" WHERE ("connectr_passage"."text" ~ E'(?i)\ycape\y' AND NOT ("connectr_passage"."text" ~ E'https?://' ))

在一个包含大约 412K 行数据的表上，使用 $9 'dev' 数据库，这个查询需要 1320 毫秒所以对于应用程序用户来说感觉很慢，因为总响应时间甚至更长。

在我的本地机器上使用完全相同的数据库(MBP、8gb ram、ssd)，这个查询需要 629.214 毫秒

我知道开发数据库有一些限制(不做内存缓存等)，所以我的问题是:

1. 有什么方法可以加快速度吗？在文本列上添加索引似乎没有帮助。

2. 升级到其中一个生产数据库是否会显着提高此性能？它们对我的需要来说相当昂贵。

3. 您知道任何其他托管连接到 Heroku 的数据库的好方法吗？

4. 除了执行正则表达式 sql 查询来搜索术语外，还有什么推荐的替代方法吗？我正在考虑创建一个自定义的单词索引存储或其他东西，也许某处有一个插件。干草堆？

---

----- 编辑-----

以下是大象对我的查询的评价:

Sort  (cost=16979.75..16979.83 rows=34 width=175) (actual time=616.131..616.132 rows=18 loops=1)
   Sort Key: author_followers_count
   Sort Method:  quicksort  Memory: 30kB
   ->  Seq Scan on connectr_passage  (cost=0.00..16978.89 rows=34 width=175) (actual time=10.863..616.027 rows=18 loops=1)
         Filter: (((text)::text ~ '(?i)\\ycape\\y'::text) AND ((text)::text !~ 'https?://'::text))
 Total runtime: 616.229 ms

所以它看起来像是在进行全表扫描，所以索引不起作用。我是 Postgres 新手，所以不确定我是否有这个权利，但这是我的索引(通过在 Django 模型中设置 db_index=True 创建):

public | connectr_passage_text                                | index | connectr | connectr_passage

---

另一个编辑:

这是最新的 - 在使用 pg_trgm 附加组件之后。

create extension pg_trgm;
create index passage_trgm_gin on connectr_passage using gin (text gin_trgm_ops);

第一次尝试:

d2lgd5pcso4g2k=> explain analyze select * from connectr_passage where text ~ E'cape\y';
                                                      QUERY PLAN                                                       

 Seq Scan on connectr_passage  (cost=0.00..28627.30 rows=95 width=177) (actual time=2647.828..2647.828 rows=0 loops=1)
   Filter: ((text)::text ~ 'capey'::text)
   Rows Removed by Filter: 970514
 Total runtime: 2647.866 ms
(4 rows)

该死的，还是 super 慢。但是等等，如果我在 RegEx 之前做一个简单的过滤器会怎么样:

d2lgd5pcso4g2k=> explain analyze select * from connectr_passage where text like '%cape%' and text ~ E'(?i)\ycape\y';
                                                          QUERY PLAN                                                          

 Bitmap Heap Scan on connectr_passage  (cost=578.14..762.70 rows=1 width=177) (actual time=11.432..11.432 rows=0 loops=1)
   Recheck Cond: ((text)::text ~~ '%cape%'::text)
   Rows Removed by Index Recheck: 165
   Filter: ((text)::text ~ '(?i)ycapey'::text)
   Rows Removed by Filter: 468
   ->  Bitmap Index Scan on passage_trgm_gin  (cost=0.00..578.14 rows=95 width=0) (actual time=8.845..8.845 rows=633 loops=1)
         Index Cond: ((text)::text ~~ '%cape%'::text)
 Total runtime: 11.479 ms
(8 rows)

超快!

Answer 1

因此，由于 mu-is-too-short 的建议和一些谷歌搜索，这几乎已经解决了。基本上 PostgreSQL pg_trgm 扩展解决了这个问题，并使查询速度提高了 800 倍!