I know this is a pretty basic question, and I think I know the answer...but I'd like to confirm.
Are these queries truly equivalent?
SELECT * FROM FOO WHERE BAR LIKE 'X'
SELECT * FROM FOO WHERE BAR ='X'
Perhaps there is a performance overhead in using like with no wild cards?
I have an app that optionally uses LIKE & wild cards. The SP currently does the like and appends the wild cards -- I am thinking of just updating the query to use like but have the app append the wild cards as needed.
As @ocdecio says, if the optimizer is smart enough there should be no difference, but if you want to make sure about what is happening behind the scenes you should compare the two query's execution plans.
I found one case where using LIKE is 20x - 125x faster than using = (which goes to show that armchair philosophizing about "what the optimizer would do" is not as easy as some would think, or people vastly overestimate the intelligence of these tools even on very simple tasks).
It's a database with ~50 million rows.
Create a trigram GIN index:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON profiles USING gin (username gin_trgm_ops);
And now one query takes 200 ms, while the the other takes 4 - 25 secs.
SELECT userid,username,country FROM profiles WHERE username LIKE 'someuser';
SELECT userid,username,country FROM profiles WHERE username='someuser';