SELECT a,b,c FROM t WHERE a LIKE '?%'
是的,目前,需要简单地找到“a”以输入文本开头的行.我希望将查看速度降低到100毫秒以下(瞬间出现).建议？在我看来,全文搜索在这里没有帮助,因为我的文本列太短,但我很乐意尝试,如果有价值.
哦,顺便说一下,我还在mongodb和索引列“a”中加载了完全相同的数据.在mongodb中加载数据非常快(mongodb).在进行精确查找时,mongodb和Postgres都非常快速.但是,Postgres在进行上述跟踪通配符搜索时实际上是闪耀的,持续时间约为mongodb的1/3.我很乐意追求mongodb,如果我能加快速度,因为这只是一个只读操作.
更新：首先,一些EXPLAIN ANALYZE输出
EXPLAIN ANALYZE SELECT a,c FROM t WHERE a LIKE 'abcd%'
"Seq Scan on t (cost=0.00..282075.55 rows=802 width=40)
(actual time=1220.132..1220.132 rows=0 loops=1)"
" Filter: ((a)::text ~~ 'abcd%'::text)"
"Total runtime: 1220.153 ms"
我实际上想要将Lower(a)与搜索词进行比较,搜索词总是至少4个字符,所以
EXPLAIN ANALYZE SELECT a,c FROM t WHERE Lower(a) LIKE 'abcd%'
"Seq Scan on t (cost=0.00..302680.04 rows=40612 width=40)
(actual time=4.681..3321.387 rows=788 loops=1)"
" Filter: (lower((a)::text) ~~ 'abcd%'::text)"
"Total runtime: 3321.504 ms"
所以我创建了一个索引
CREATE INDEX idx_t ON t USING btree (Lower(Substring(a,1,4) ));
"Seq Scan on t (cost=0.00..302680.04 rows=40612 width=40)
(actual time=3243.841..3243.841 rows=0 loops=1)"
" Filter: (lower((a)::text) = 'abcd%'::text)"
"Total runtime: 3243.860 ms"
似乎唯一一次使用索引是我正在寻找完全匹配的时候
EXPLAIN ANALYZE SELECT a,c FROM t WHERE a = 'abcd'
"Index Scan using idx_t on geonames (cost=0.00..57.89 rows=13 width=40)
(actual time=40.831..40.923 rows=17 loops=1)"
" Index Cond: ((ascii_name)::text = 'Abcd'::text)"
"Total runtime: 40.940 ms"
通过使用varchar_pattern_ops和am now looking for an even quicker lookups实现索引找到了解决方案.
使用这样的索引：
CREATE INDEX t_a_lower_idx ON t (lower(substring(a,4)));
或者在Postgresql 9.1中更简单：
CREATE INDEX t_a_lower_idx ON t (lower(left(a,4)));
使用此查询：
SELECT * FROM t WHERE lower(left(a,4)) = 'abcd';
这100％在功能上等同于：
SELECT * FROM t WHERE lower(a) LIKE 'abcd%'
要么：
SELECT * FROM t WHERE a ILIKE 'abcd%'
但不是：
SELECT * FROM t WHERE a LIKE 'abcd%'
CREATE INDEX t_a_idx ON t (substring(a,4));
或者使用Postgresql 9.1更简单：
CREATE INDEX t_a_idx ON t (left(a,4));
并使用此查询：
SELECT * FROM t WHERE left(a,4) = 'abcd';
左锚定的可变长度搜索项
不区分大小写.指数：
编辑：几乎忘了：如果你使用除默认“C”之外的任何其他语言环境运行数据库,则需要在我的示例中使用specify the operator class explicitly – text_pattern_ops：
CREATE INDEX t_a_lower_idx ON t (lower(left(a,<insert_max_length>)) text_pattern_ops);
查询：
SELECT * FROM t WHERE lower(left(a,<insert_max_length>)) ~~ 'abcdef%';
可以使用索引,并且几乎与具有固定长度的变体一样快.
您可能对此post on dba.SE with more details about pattern matching感兴趣,特别是关于运算符的最后一部分〜> =〜和〜<〜.
