Comment by branko_d
6 hours ago
That's true for seeks into the clustered (primary) index because that index includes all fields, so you don't need to "jump" to the heap to get them.
However, seeking into a secondary index, and then reading a column not included in that index incurs an additional index seek (into the clustered index), which may be somewhat slower than what would happen in a heap-based table.
So there are pros and cons, as usual...
I have found very minimal penalty on secondary index reads in practice such that it has never made a difference.
Remember some databases always use clustered index internally (SQLite, MySql) such that even if you have no primary key they will create a hidden one instead for use with the index.
https://www.sqlite.org/rowidtable.html
It is nice to have the choice which way to go and would be nice if PG implemented this. It can have significant space savings on narrow table with one primary index and performance advantages.