如何在不使用全文搜索的情况下提高查询速度?

This is my simple query; By searching selectnothing I'm sure I'll have no hits.

这是我的简单查询;通过搜索selectnothing，我确信我不会有什么结果。

SELECT nome_t FROM myTable WHERE nome_t ILIKE '%selectnothing%';

This is the EXPLAIN ANALYZE VERBOSE

这是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:

myTable有大约350k行，表定义如下:

CREATE TABLE myTable (
    nome_t text NOT NULL,
)

I have an index on nome_t as stated below:

我对nome_t有如下索引:

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秒，这显然太多了;有什么我可以做的，比如使用其他索引方法来提高查询的速度吗?

2 个解决方案

#1

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.

不，我喜欢'%selectnothing%'总是需要一个完整的表扫描，每个索引都是无用的。你需要全文搜索，这并不难实现。

Edit: You could use a Wildspeed, I forgot about this option. The indexes will be huge, but your performance will also be much better.

编辑:你可以使用一个通配符，我忘记了这个选项。索引将是巨大的，但是您的性能也会更好。

Wildspeed extension provides GIN index support for wildcard search for LIKE operator.

通配符扩展为诸如操作符之类的通配符搜索提供了GIN索引支持。

http://www.sai.msu.su/~megera/wiki/wildspeed

http://www.sai.msu.su/ megera / wiki / wildspeed

#2

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).

另一种方法是将表myTable中的nome_t列分解到它自己的表中。从表中搜索一列是很慢的(如果还有其他50个宽列)，因为其他数据有效地减慢了对该列的扫描(因为每个页面/范围的记录更少)。

#1