Postgres SELECT DISTINCT Scans Every Row, No Matter How You Index It
Postgres SELECT DISTINCT Does Not Scale
Peter Kraft of DBOS describes a Postgres performance trap: SELECT DISTINCT always scans every row matching its predicates, even with a perfect index on queue name, status, and partition key. Diagnosing a partitioned queue workload, the team found the simplest-looking query was the most expensive. The post explains why Postgres makes this choice and how to work around it.
No matter how you index your table, no matter how few unique values there are to retrieve, SELECT DISTINCT will always scan every row that matches its predicates.