🔗 How moving one word can speed up a query 10–50x

Two logically equivalent queries on a soft-delete pattern show dramatically different performance: one using EXISTS with a condition on active rows, the other using NOT EXISTS with a condition on deleted rows. The 32x speedup comes from two stacked factors: when NOT EXISTS finds nothing in the small index of deleted rows, it skips heap fetches entirely, while EXISTS finding a match requires heap verification on an actively updated table.

The visibility map breaks on concurrent updates, forcing expensive random reads into a 13 GiB table for Q1, which performs 132,280 heap fetches versus Q2's 2,680. Q2's small index (22 MiB) stays warm in cache while Q1's large index (1,050 MiB) requires constant disk reads, and Q1 dirties 112,214 pages versus Q2's 2,109, creating write pressure affecting all sessions.