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)
