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) ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
