Better space usage and performance for that single index since the table is the index, slightly worse performance for non clustered indexes due to a double index traversal.

That said, one can often capture much of the performance benefit with Postgres indexes - basically, instead of trying to tightly-pack the base tables, copy the data into indexes which are tightly packed exactly as you need. Put another way, Postgres indexes are synchronously updated materialized views - and yes, the Postgres planner/optimizer will automatically answer queries from an index if it's faster and all the columns are in there.

I esp recommend reading about function and expression indexes, as well as the (new) covering indexes.

http://blog.scoutapp.com/articles/2016/05/31/3-postgresql-in... https://www.endpoint.com/blog/2013/06/10/postgresql-function... https://paquier.xyz/postgresql-2/postgres-11-covering-indexe...