Re: Why scan all columns when we select distinct c1?

Am 14.01.24 um 12:17 schrieb Yongtao Huang:
>
>
> My question is why scan all columns in PG 16.01?
> If `select distinct c1`, scan the column `c1` is enough, like PG 9.4.

good question, I think because the seq scan always reads the column. If
you create an index on c1, this changes:

postgres=# create index idx1 on t1(c1);
CREATE INDEX
postgres=# set enable_seqscan to off;
SET
postgres=# explain (costs, verbose) select distinct c1 from t1;
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Unique  (cost=0.15..63.93 rows=200 width=4)
   Output: c1
   ->  Index Only Scan using idx1 on public.t1  (cost=0.15..61.10
rows=1130 width=4)
         Output: c1
(4 rows)

now we scan only the index and not the heap.

Regards, Andreas

--
Andreas Kretschmer
CYBERTEC PostgreSQL Services and Support