You can add
LIMIT 1, but
LIMIT is applied after aggregation (so after the count). Adding it in the same query level does nothing useful, since there is only 1 row left to return anyway: the row count. Would be misleading nonsense.
If you want to go that route, add
LIMIT in a subquery and count in the outer
SELECT count(*) FROM (
SELECT -- empty SELECT list
For this special purpose it's fastest to leave the
SELECT list empty.
count(*) only counts the existence of rows without regard to any columns.
You can do that with any
LIMIT, really. To check whether there are at least N rows. See:
Checking for N = 1 is just your special case. And while we only care whether any rows exist and not how many,
EXISTS is the better, faster tool:
SELECT EXISTS (
SELECT FROM submission s
JOIN post p ON s.id = p.submission_id
WHERE s.user_name = ?
AND p.tag ~* 'verified'
AND NOT p.tag ~* 'unverified'
lower(col) LIKE '%something%' is a bit faster than
col ~* 'something'. But if your tables are big and performance matters (like your question indicates), you should have matching indexes to begin with, a trigram index on
tag to be precise. Assuming tag is from table
post (you left room for interpretation there):
CREATE INDEX post_tag_gin_trgm_idx ON post USING gin (tag gin_trgm_ops);
Then the simpler expression is just as fast. See:
Depending on cardinalities and data distribution it might make sense to combine that with
submission_id in a multicolumn index:
CREATE INDEX post_foo_trgm_idx ON post USING gin (submission_id, tag gin_trgm_ops);
You'd need the additional module btree_gin for that. See:
And it might make sense to have another multicolumn index on
submission(id, user_name) - or on
submission(user_name, id), again depending on the mentioned details. The first variant is more likely to be useful in case Postgres expects the predicate on
submission.user_name to be more selective.
(Your handling of tags might be optimized further.)
If the result is greater than 0 I do something in my application for this user.
If that something is another SQL DML statement, make it a single statement for additional gains. Example:
SET bar = 'baz'
WHERE EXISTS ( ...) -- like above
AND bar IS DISTINCT FROM 'baz' -- avoid empty updates
About the added last line:
Or you embed the expression in plpgsql code for procedural needs, like demonstrated here: