Even though I'm a big fan of Postgres, its lack of loose index scan is frustrating.. The workaround is a recursive query that effectively acts as a loop, which is even more bizarre. Check out this new blog post about the performance issue with Postgres SELECT DISTINCT: www.dbos.dev/blog/postgre...
Postgres SELECT DISTINCT Does Not Scale | DBOS
Postgres SELECT DISTINCT performs surprisingly poorly. We explain why and how we mitigated it.
dbos.dev