3 ms·

can you elaborate? I was living under impression that what sqlite has, is exactly what covering index is...

• simonw 3y ago
  In PostgreSQL a covering index can be configured which includes extra information from columns that aren't part of the searchable index itself. It's documented quite well here: https://www.postgresql.org/docs/current/indexes-index-only-scans.html https://www.postgresql.org/docs/current/indexes-index-only-s... CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);
• masklinn 3y ago
  With a "proper" covering index (an INCLUDE clause in SQL Server or Postgres for example) you add data to the index value. This means it can be retrieved just by looking into the index but - it's not constrained (e.g. to be orderable) - it does not affect the behaviour of the index, so you can have covering data in a UNIQUE index, or in a PK constraint (although for the latter one might argue a clustered index is superior) - it only takes space in leaf nodes, not interior nodes, so you can have better occupancy of interior node pages, less pages to traverse during lookup, and they have better cache residency - and finally the intent is clearer, when you put everything in the key it does not tell the reader what's what and why it there, and thus makes it harder to evaluate changes