Index Merges vs Composite Indexes in Postgres and MySQL
Index Merges vs Composite Indexes in Postgres and MySQLNov 2022Composite indexes are about 10x faster than index merges. In Postgres, the gap is larger than in MySQL because Postgres doesn't support index-only scans for queries that involve index merges.Napkin MathComposite Index: ~1msIndex Merge: ~...
Hasnain says:
“If you’re wondering if you need to add a composite index, or can get away with creating to single indexes and rely on the database to use both indexes — then the rule of thumb we establish is that an index merge will be ~10x slower than the composite index. However, we’re still talking less than 100ms in most cases, as long as you’re operating on 100s of rows (which in a relational, operational database, hopefully you mostly are).”
Posted on 2022-11-28T00:53:15+0000