non index use on LIKE on a non pattern string - Mailing list pgsql-performance

Hello,

I have noticed that with a SELECT query containing the following
constraint:

    column LIKE ?

and an index on that column, PostgreSQL will not use the index
even if the parameter doesn't contain special pattern characters
such as %.

From PG POV it might be logical, because, who is stupid enough to
use the LIKE operator if it's unneeded, right?

However from my application POV the users sometimes want to
provide a pattern with % and sometimes a more precise condition,
and of course, I am uneasy at writing two very similar SQL
requests with only the LIKE/= difference; in the end, the non use
of an index means unwanted performance degradation.

I have come with the following hack in the SQL:

       ( position('%' in ?) > 0 OR column = ? )
       AND ( position('%' in ?) = 0 OR column LIKE ? )

(I know it doesn't cover all the pattern possibilities)

Any thoughts on what would be the best approach? Mine looks a bit
ugly.

Thanks,

--
Guillaume Cottenceau