Postgres SELECT DISTINCT Does Not Scale

nattaylor · 2026-09-26T03:10:34

Loose index scan is made for this https://dev.mysql.com/doc/refman/8.0/en/group-by-optimizatio...

Postgres doesn't have it yet https://wiki.postgresql.org/wiki/Loose_indexscan

DiabloD3 · 2026-09-26T02:42:09

"Postgres SELECT DISTINCT Does Not Scale"

Correct. This is documented in depth: DISTINCT sorts the results first.

The article's use case seems to imply the author did not know about GROUP BY, nor does it imply the author knew about indexes, nor ANALYZE. Postgres 18's new skip scan indexing also could help here, so ensuring the planner chooses that could help.

Dylan16807 · 2026-09-26T02:47:21

Would GROUP BY fix the issue?

The article explains that skip scan doesn't do anything here.

> nor does it imply the author knew about indexes, nor ANALYZE

Indexes were talked about a lot, and they explicitly mentioned looking at the query plan.

DiabloD3 · 2026-09-26T06:00:04

The article seems to have changed since I commented.

gfody · 2026-09-26T05:49:09

nope - either gets you a HashAggregate, GroupAggregate, or Unique depending on whether the column/input is indexed/ordered

tpetry · 2026-09-26T04:16:12

Did you even read the article? They show that a perfect index for their query didnt help because Skip Scan is currently not used for DISTINCT queries.

DiabloD3 · 2026-09-26T05:59:53

The article seems to have changed since I commented.