Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rP2gd-008v03-4C for pgsql-general@arkaria.postgresql.org; Sun, 14 Jan 2024 15:46:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rP2gc-00C93R-4t for pgsql-general@arkaria.postgresql.org; Sun, 14 Jan 2024 15:46:10 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rP2gb-00C91y-MB for pgsql-general@lists.postgresql.org; Sun, 14 Jan 2024 15:46:09 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rP2gX-001Vis-Kz for pgsql-general@postgresql.org; Sun, 14 Jan 2024 15:46:08 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 40EFk34a1239079; Sun, 14 Jan 2024 10:46:03 -0500 From: Tom Lane To: Ron Johnson cc: Yongtao Huang , pgsql-general@postgresql.org Subject: Re: Why scan all columns when we select distinct c1? In-reply-to: References: Comments: In-reply-to Ron Johnson message dated "Sun, 14 Jan 2024 09:51:28 -0500" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <1239077.1705247163.1@sss.pgh.pa.us> Content-Transfer-Encoding: 8bit Date: Sun, 14 Jan 2024 10:46:03 -0500 Message-ID: <1239078.1705247163@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Ron Johnson writes: > On Sun, Jan 14, 2024 at 6:18 AM Yongtao Huang > 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