如何在不使用全文检索的情况下提高查询速度？
这是我的简单查询；通过搜索 selectnothing 我确信我不会有任何点击。
SELECT nome_t FROM myTable WHERE nome_t ILIKE '%selectnothing%';
这是 EXPLAIN ANALYZE VERBOSE
Seq Scan on myTable (cost=0.00..15259.04 rows=37 width=29) (actual time=2153.061..2153.061 rows=0 loops=1)
Output: nome_t
Filter: (nome_t ~~* '%selectnothing%'::text)
Total runtime: 2153.116 ms
myTable 有大约 350k 行，表定义类似于：
CREATE TABLE myTable (
nome_t text NOT NULL,
)
我在 nome_t 上有一个索引，如下所示：
CREATE INDEX idx_m_nome_t ON myTable
USING btree (nome_t);
尽管这显然是全文搜索的一个很好的候选者，我想暂时排除该选项。
该查询本应从 Web 应用程序运行，目前大约需要 2 秒，这显然太多了；
我可以采取什么措施（例如使用其他索引方法）来提高此查询的速度？
This is my simple query; By searching selectnothing I'm sure I'll have no hits.
SELECT nome_t FROM myTable WHERE nome_t ILIKE '%selectnothing%';
This is the EXPLAIN ANALYZE VERBOSE
Seq Scan on myTable (cost=0.00..15259.04 rows=37 width=29) (actual time=2153.061..2153.061 rows=0 loops=1)
Output: nome_t
Filter: (nome_t ~~* '%selectnothing%'::text)
Total runtime: 2153.116 ms
myTable has around 350k rows and the table definition is something like:
CREATE TABLE myTable (
nome_t text NOT NULL,
)
I have an index on nome_t as stated below:
CREATE INDEX idx_m_nome_t ON myTable
USING btree (nome_t);
Although this is clearly a good candidate for Fulltext search I would like to rule that option out for now.
This query is meant to be run from a web application and currently it's taking around 2 seconds which is obviously too much;
Is there anything I can do, like using other index methods, to improve the speed of this query?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不，ILIKE '%selectnothing%' 总是需要全表扫描，每个索引都是无用的。您需要全文搜索，实现起来并不难。
编辑：你可以使用Wildspeed，我忘记了这个选项。索引会很大，但你的性能也会好得多。
http://www.sai.msu.su/~megera/wiki/wildspeed
No, ILIKE '%selectnothing%' always needs a full table scan, every index is useless. You need full text search, it's not that hard to implement.
Edit: You could use a Wildspeed, I forgot about this option. The indexes will be huge, but your performance will also be much better.
http://www.sai.msu.su/~megera/wiki/wildspeed
您可以做的另一件事 - 是将表 myTable 中的 nome_t 列分解到它自己的表中。从表中搜索一列很慢（如果还有五十个其他宽列），因为其他数据有效地减慢了对该列的扫描速度（因为每页/范围的记录较少）。
another thing you can do-- is break this nome_t column in table myTable into it's own table. Searching one column out of a table is slow (if there are fifty other wide columns) because the other data effectively slows down the scan against that column (because there are less records per page/extent).