Is it possible to index "SELECT domain_name from domains WHERE 'x.example.com' like domain_name" where domain_name contains "%.example.com"

Question

Hey, I am trying to create a system to match wildcard domain names. Although the query above will work for me is it possible to index it at all?

I don't have access to the DB to do any clever stuff as I am using Heroku to host my app but I can add indexes easy enough?

Thanks!

Edit:

I would like to match wildcard domain names in my database. So I have a table with a column called domain_name and this can contain something like '%.example.com' to match if I pass in x.example.com or hello.example.com as an input parameter.

Answer 1

Use reverse function and function index. http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks

If You use Postgresql 9.1 "reverse" function is build in.

CREATE OR REPLACE FUNCTION reverse(varchar) RETURNS varchar AS $$ 
  $reversed = reverse $_[0]; 
  return $reversed; 
$$ LANGUAGE plperlu IMMUTABLE;

CREATE INDEX rev_email ON users( (reverse(email) ) varchar_pattern_ops );
SELECT * FROM _users WHERE reverse(email) LIKE reverse ('%.cz');