4 ms·
Right, Postgres supports this use case explicitly with INCLUDE [1], which can be more efficient than just indexing all the columns you need. Pretty sure SQL Ser
by uhoh-itsmaciek 5y ago
Right, Postgres supports this use case explicitly with INCLUDE [1], which can be more efficient than just indexing all the columns you need. Pretty sure SQL Server has something similar.
[1]: https://www.postgresql.org/docs/current/sql-createindex.html https://www.postgresql.org/docs/current/sql-createindex.html
- geophile 5y agoYes, Postgres has been playing various tricks in the last few years, to make this optimization actually optimize things, but the Postgres MVCC approach makes it difficult. The problem is that index information can be stale, and that you often have to go to the indexed page to determine current state. They've made progress to lessen that "often" to "sometimes", but unless version information is added to indexes, they won't be able to fix this completely. I was teaching a database course a couple of years ago, using Postgres, and in exercises on query optimization, I found it surprisingly difficult to get columns added to indexes to produce a convincing improvement.