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 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!
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';