From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Yongtao Huang <yongtaoh2022@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Why scan all columns when we select distinct c1?
Date: Sun, 14 Jan 2024 10:46:03 -0500
Message-ID: <1239078.1705247163@sss.pgh.pa.us> (raw)
In-Reply-To: <CANzqJaDG4guxWQX=OpxR_BOTO2bi2pDCKU1ppNj2znczJNSv8g@mail.gmail.com>
References: <CAOe1Go3uHkTrgBMm1O2pVWQAV09ZxVW_vaKM1GeAkAu_RByt2A@mail.gmail.com>
<CANzqJaDG4guxWQX=OpxR_BOTO2bi2pDCKU1ppNj2znczJNSv8g@mail.gmail.com>
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On Sun, Jan 14, 2024 at 6:18 AM Yongtao Huang <yongtaoh2022@gmail.com>
> wrote:
>> gpadmin=# create table t1 (c1 int, c2 text);
>> CREATE TABLE
>> gpadmin=# explain (costs off, verbose) select distinct c1 from t1;
>> QUERY PLAN
>> -----------------------------
>> HashAggregate
>> Output: c1
>> Group Key: t1.c1
>> -> Seq Scan on public.t1
>> Output: c1, c2 <---- pay attention <---- !!!
>> (5 rows)
>>
>> My question is why scan all columns in PG 16.01?
> You can't scan just one column of a row-oriented table.
> The real question is why it mentions c2.
The planner did that so that the SeqScan step doesn't have to
perform a projection: it can just return (a pointer to)
the physical tuple it found in the table, without doing extra
work to form a tuple containing only c1. The upper HashAgg
step won't really care. See use_physical_tlist() in createplan.c.
What I'm confused about is why 9.4 didn't do the same.
That optimization heuristic is very old, and certainly
would be applied by 9.4 in some circumstances. Testing
says the behavior in this specific case changed at 9.6.
I'm not quite interested enough to drill down further...
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, ronljohnsonjr@gmail.com, yongtaoh2022@gmail.com
Subject: Re: Why scan all columns when we select distinct c1?
In-Reply-To: <1239078.1705247163@sss.pgh.pa.us>
References: <CAOe1Go3uHkTrgBMm1O2pVWQAV09ZxVW_vaKM1GeAkAu_RByt2A@mail.gmail.com> <CANzqJaDG4guxWQX=OpxR_BOTO2bi2pDCKU1ppNj2znczJNSv8g@mail.gmail.com> <1239078.1705247163@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox