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
);Read on to find out. This pattern was first spotted by Maxim Boguk on a 400 GiB production table, where the speedup was approximately 50x.