Date: Tue, 11 Sep 2007 15:54:42 -0300
From: Fernan Aguero <fernan@unsam.edu.ar>
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: efficient pattern queries (using LIKE, ~)
Message-ID: <20070911185442.GA38153@iib.unsam.edu.ar>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.16 (2007-06-09)

Hi,

I have a table containing some ~13 million rows. Queries on
indexed fields run fast, but unanchored pattern queries on a
text column are slooooow. Indexing the column doesn't help
(this is already mentioned in the manual).
http://www.postgresql.org/docs/8.2/interactive/indexes-types.html

However, no alternative solution is mentioned for indexing
and/or optimizing queries based on unanchored patterns: 
i.e. description LIKE '%kinase%'.

I've already searched the archives, read the manual, googled
around and the only alternative I've found is: full text
indexing (tsearch2 in postgresql-contrib; OpenFTS; others?)

But do note that i) I'm not interested in finding results 'similar to'
the query term (and ranked based on similarity) but just
results 'containing an exact substring' of the query term ...
i.e. not the original goal of a full text search

And, ii) from what I've read it seems that for both tsearch2
and OpenFTS the queries have to be rewritten to explicitly
evaluate the pattern on the special indices, i.e. they're
not transparently available (i.e. via the query planner),

I'm hoping for something like:
CREATE INDEX newindex ON table USING fti (column);

and then having the new index automagically used by the
planner in cases like:
SELECT * FROM table WHERE column LIKE '%magic%';

If there's anything like this, I've failed at finding it ...

Thanks for any pointer,

Fernan

PS: additional information

This is on PostgreSQL-8.2.4, FreeBSD-6.2 (amd64).

EXPLAIN ANALYZE SELECT COUNT(*) FROM dots.transcript WHERE product LIKE '%kinase%'; QUERY PLAN                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=651878.85..651878.86 rows=1 width=0) (actual time=45587.244..45587.246 rows=1 loops=1)
   ->  Seq Scan on nafeatureimp  (cost=0.00..651878.85 rows=1 width=0) (actual time=33.049..45582.628 rows=2255 loops=1)
         Filter: (((subclass_view)::text = 'Transcript'::text) AND ((string13)::text ~~ '%kinase%'::text))
 Total runtime: 45589.892 ms
(4 rows)