> with the full table scan being insertion order/sort by rowid
I'm not sure that makes sense. As far as I'm aware selecting without sorting has no guaranteed order across vendors at all.
No guaranteed order, but very typically a predictable order depending on the query plan chosen. If you’re relying on an ordering, you need to specify it in the query.
However, the scan order is predictable for a given engine depending on the query plan. If it’s doing an index scan, you’ll get results in index order and if it’s doing a table scan you’ll get it in table storage order (assuming a transactional, single node [edit: also single threaded] OLTP database- this goes out the window when outputs are aggregated across multi node (edit: or multi threaded) scans without an ORDER BY)
Postgres can do parallel table and index scans. Don't rely on order without explicit order by.
That overlooks hash indexes or index-only queries, of course.
Love the article but I'm not sure that the indirection of the index being an additional data structure really helps understanding it better.
I think you basically only need to understand that once we have some degenerated indexes with bad selectivity where a full table scan would be better. Here the additional index access is neck breaking.
Of course, this sorting-intuition doesn't help in understanding the different kind of indexes. Is that a topic for newbies though?