On 23 Jul 2010, at 23:22, Marcelo de Moraes Serpa wrote: > The following query works: > > SELECT * FROM users WHERE 'com.app.mycompany' LIKE reversed_domain || % > > However, it does sequential search, meaning it doesn't use any index.
The database may choose to use a seqscan for several reasons, not necessarily related to how you write your query. Is it a problem in your case? An EXPLAIN ANALYSE would give us more insight. I would expect the planner to pick an index scan if there's sufficient data in that table, as a suffix-search like that suits a btree index just fine. > What I would like to know is, how could I make it use an index? I've If you really have to, for testing purposes you can temporarily disable sequential scans (SET enable_seqscan TO 'off') to see if the plan resulting from that is indeed more efficient. If it is, that probably means your database statistics aren't up to date ([VACUUM ]ANALYSE) or the statistics target for that specific column is too small (ALTER TABLE users ALTER COLUMN reversed_domain SET STATISTICS ....). > done some research and asked around #postgres but things are still not > clear to me. Some good souls hinted me at the prefix extension, but > how would I use it? Is there any other simpler / extension-free way to > solve this issue? I'm not familiar with said extension, but I think that one's aimed at LIKE searches where the search term _starts_ with a wildcard instead of _ending_ with one. That's a situation where a btree index is in trouble. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4c4abfcd286214416410229! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
