CODEWITHSUNDEEP
postgresql
Perlos
Perlos

Reputation: 2118

Check value if exists in column

I'd like to know how to maximize speed when querying for the presence of a varchar value in a column in a specific table. I don't need to know where it is, or how many occurrences there are, I just want a true/false. Of course, the column has an index.

Now, I have this:

SELECT exists (SELECT 1 FROM table WHERE column = <value> LIMIT 1);

Upvotes: 32

Views: 54132

Answers (2)

blindguy
blindguy

Reputation: 1009

I have had recent success with the following in some cases:

SELECT count(1) > 0
FROM table
WHERE column = <value>

Upvotes: 2

Mike Sherrill &#39;Cat Recall&#39;
Mike Sherrill &#39;Cat Recall&#39;

Reputation: 95512

EXISTS should normally return as soon as the subquery finds one row that satisfies its WHERE clause. So I think your query is as fast as you can make it.

I was a little surprised that LIMIT 1 seems to always speed up the query very slightly. I didn't expect that. You can see the effect with EXPLAIN ANALYZE.

EXPLAIN ANALYZE
SELECT exists (SELECT 1 FROM table WHERE column = <value> LIMIT 1);

Upvotes: 32

Related Questions