Thread: Prefix LIKE search and indexes issue.
Hello list, So, I have a small query design issue and I'd like to borrow some of your wisdom. Let's say I a users relation, and each user has a reversed_domain field. id | name | reversed_domain 1 Josh com.app ... I then have a firefox plugin which makes request to my application server, sending along the current URL the user is browsing. Let's say the URL is "http://mycompany.app.com/login". The code on the app takes this data and: 1) Extracts the domain out of it; 2) Reverses the domain We then get the following string as a result: "com.app.mycompany". I then want to find the user Josh, by reversed_domain. However, as you can see, the strings are different, and in most cases will be. I just want it to match the first two parts of the domain (com.app). 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. What I would like to know is, how could I make it use an index? I've 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? Thanks in advance, Marcelo.
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 ita 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 suitsa 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') tosee 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 forthat 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 awildcard 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!
In article <AANLkTims+x5BpfAXF+9_cOHiaGa7=B_NpN=hw99kGxsK@mail.gmail.com>, Marcelo de Moraes Serpa <celoserpa@gmail.com> writes: > Some good souls hinted me at the prefix extension, but > how would I use it? Like this: CREATE TABLE users ( id serial NOT NULL, name text NOT NULL, reversed_domain prefix_range NULL, PRIMARY KEY (id) ); CREATE INDEX users_dom_ix ON users USING gist (reversed_domain); SELECT id, name, reversed_domain FROM users WHERE reversed_domain @> 'com.app.mycompany';