The ESR Equality Sort Range Rule of Composite Indexes
summary
Explains a concrete rule for Postgres composite indexes: put all equality first columns, then range columns, then the columns used for ORDER BY/LIMIT so the index can produce the exact range and avoid a separate sort. Shows SQL examples on Postgres, demonstrates queries that drop a sort step and reduce row reads, and gives the tipping point: if the range predicate is more selective than the ORDER BY/LIMIT then range before sort when more selective yields the best performance.
AI-written summary · about
related talks
37mRules of Indexing in PostgreSQL
57mCookbook for Creating INDEXes -- All about Indexing
37mRules of Indexing in PostgreSQL
50mHow to choose the right index
26mBeyond Joins and Indexes
01h 00mFlexible Indexing With Postgres
45mSQL Indexing Beyond Simple Columns: Expressions, Text Fragments, Attributes J...
50mRange Types in PostgreSQL 9.2 - Your Life Will Never Be the Same