How an index keeps a query fast as a table grows 50 times
A PostgreSQL benchmark comparing the same query over 1, 10, and 50 million rows, with and without the right index.
Blog
How to eliminate the N+1 query problem with a JOIN
Why replacing 51 SQL commands with a single JOIN made a bank statement query approximately 16 times faster.
How to speed up case-insensitive searches with an expression index
How to use a PostgreSQL expression index to make a case-insensitive prefix search approximately 33 times faster.
How to reduce an index from 387 MB to 3 MB using a partial index
How a partial PostgreSQL index serves an anti-fraud queue using 3,104 kB instead of the 387 MB required by a full index.
Optimize your queries by replacing OFFSET with keyset pagination
A benchmark comparing OFFSET and keyset pagination over a whale account's statement, from the first page to an offset of 4,950 rows.
How to avoid unnecessary table access using a covering index
How to use a covering index to calculate an account balance without accessing the heap, reducing execution time from 12.249 ms to 2.780 ms.
Why your composite index isn't making the query fast
The same two columns, in the two possible orders, over 10 million rows: 17 ms against 0.060 ms. Three execution plans, and why the gap is not a rounding error.