How to increase query speed without using full-text search?

Posted by andre matos on Stack Overflow See other posts from Stack Overflow or by andre matos
Published on 2011-01-06T11:42:46Z Indexed on 2011/01/06 11:53 UTC
Read the original article Hit count: 235

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?

© Stack Overflow or respective owner

Related posts about sql

Related posts about Performance