I'll take a [citation needed] on that one.

jitl 4 days ago | parent | next [-]

https://www.postgresql.org/docs/current/indexes-index-only-s...

This is why Postgres b-tree indexes offer CREATE INDEX (indexCol1, indexCol2, ...) INCLUDE (includeCol1, includeCol2, ...). With INCLUDE, the index will directly store the listed additional columns, so if your query does `SELECT includeCol1 WHERE indexCol1 = X AND indexCol2 > Y`, you avoid needing to look up the entire row in the heap, because includeCol1 is stored in the index already. This is called a "covering index" because the index itself covers all the data necessary to answer the query, and you get an "index only scan" in your query plan.

The downside to creating covering indexes is that it's more work for Postgres to go update all the INCLUDE values in all your covering indexes at write time, so you are trading write speed for increased read speed.

I think it's quite typical to see this in SQL databases. SQLite behaves the same way for indexes; the exception is that if you create a WITHOUT ROWID table, then the table itself is sorted by primary key instead of by ROWID, so you get at most 1 index that maps directly to the row value. (sqlite docs: https://sqlite.org/withoutrowid.html)

▲

Sesse__ 4 days ago | parent [-]

That link directly contradicts what you are saying.

> This means that in an ordinary index scan, each row retrieval requires fetching data from both the index and the heap.

Note that it says _index and the heap_. Not _index and the primary index and the heap_. (For a B-tree-organized table, the leaf heap nodes are essentially the bottom of the primary index, so it means that to find anything, you need to follow the primary index from the top, which may or may not entail extra disk accesses. For a normal Postgres heap, this does not happen, you can just go directly to the right block.)

Index-only scans (and by extension, INCLUDE) are to avoid reaching into the heap at all.

> The downside to creating covering indexes is that it's more work for Postgres to go update all the INCLUDE values in all your covering indexes at write time, so you are trading write speed for increased read speed.

For updates, even those that don't touch INCLUDE values, Postgres generally needs to go update the index anyway (this the main weakness of such a scheme). HOT is an exception, where you can avoid that update if there's room in the same heap block, and the index scans will follow the marker(s) you left to “here's the new row!” instead of fetching it directly.

▲

jitl 3 days ago | parent [-]

Ah gotcha. My understanding of the heap reference was that heap lookups involve traversing a heap-like tree data structure, not a direct link to a specific leaf page.

Based on the limited description of OrioleDB I understand it works like SQLite WITHOUT ROWID, actually storing the row tuple in the primary key b-tree, but I didn’t go read the code

	▲	Sesse__ 3 days ago \| parent [-]
		It's confusing nomenclature, but Postgres' “heap” is not a heap (B-tree) data structure. It is just a bunch of blocks all on the same level, similar to how the “heap” in an operating system (as opposed to the stack) is. Notably, you can have a Postgres table without a primary key at all, not even an implicit one. > Based on the limited description of OrioleDB I understand it works like SQLite WITHOUT ROWID, actually storing the row tuple in the primary key b-tree, but I didn’t go read the code This is my understanding of OrioleDB as well.

▲

akorotkov 4 days ago | parent | prev [-]

Yep, regular PostgreSQL indexes point to a heap location (block number + offset). And it is the same for primary and secondary indexes.