How moving one word can speed up a query 10–50x
· 12 min read
One of these queries is 32x faster than the other. Which one, and why?
Query 1:
select pt.*
from post_tags pt
where pt.tag_id = any($1)
and exists (
select
from posts
where posts.post_id = pt.post_id
and not deleted
);Query 2:
select pt.*
from post_tags pt
where pt.tag_id = any($1)
and not exists (
select
from posts
where posts.post_id = pt.post_id
and deleted
);These two queries are logically equivalent here because posts.post_id is a primary key — the subquery matches at most one row, so AND NOT deleted vs. AND deleted simply flips a boolean. A foreign key on post_tags.post_id guarantees no orphans.
Read on to find out why the performance difference is so dramatic. This pattern was first spotted by Maxim Boguk on a 400 GiB production table, where the speedup was approximately 50x.
