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.