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 1rPpQQ-00ETKq-BQ for pgsql-general@arkaria.postgresql.org; Tue, 16 Jan 2024 19:48:42 +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 1rPpQP-0071HK-CL for pgsql-general@arkaria.postgresql.org; Tue, 16 Jan 2024 19:48:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rPpQP-0071HC-28 for pgsql-general@lists.postgresql.org; Tue, 16 Jan 2024 19:48:41 +0000 Received: from mail-oa1-x36.google.com ([2001:4860:4864:20::36]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rPpQM-002Af6-R4 for pgsql-general@postgresql.org; Tue, 16 Jan 2024 19:48:40 +0000 Received: by mail-oa1-x36.google.com with SMTP id 586e51a60fabf-21082dcce8aso490986fac.3 for ; Tue, 16 Jan 2024 11:48:38 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705434516; x=1706039316; darn=postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=j05rfo7O414ZxPKRIhYtpbRIBHXWglP61AR/Z5ZMu5I=; b=JQrfPvthrxeIge6LessmxhmAorn/bgJilM8jsTE2QETy4aR4qxG5hTkU34cwtJ6q8Z S3SmwWnC6r5+lsRtwIw6NeK7NcDJNur1iyECPNWdwHKI0KqW/RZJtLSv28TkdPqK+aJ5 7yZuxgGkLYQZY779c5uRJmPYNbCVgJ1+S1N+SqM8qerDdHDcGgi/NUiglGkfhnmWALfF lFDmfCBpP5VyXe/foXgvls+eKcxOzNpuWGe/3zc0hHpd9hPpUwdwGhgM8wazqk5YYX7p 37wp3qclJDf6i+hJbtskVzB/5ZvLWERqJHEQY7IiT/CRon9LYez1W4S2UMljl2dV8juL 5BzQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705434516; x=1706039316; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=j05rfo7O414ZxPKRIhYtpbRIBHXWglP61AR/Z5ZMu5I=; b=fW6mc/T4lIpu4iJx86R2Avo3UvQicbX5mVcNmjvJWGdMKhb34OVHL6b+/M1NtFlF7e SqMfiX1xpAxUCwhHj3CQnCl1UblN8PB6ZMhtmT3k+8TbO+YIkgZ11h1hz0bwKpRdaDDK LmPHlv+ffqrD1YanxVyadWMnhDZao0yYKtFg+x8s05meZMVl2cTAf99PTXI4iLe4zRXQ 5/wXupZ5AcCwPsqLpe75Z71I0wt5BKWr0bZWvv3Fc+Y3nQi9sAfw0TtdSF20yIQUUfqJ Fb91vcTjOt+V86brutZYzn0PF8nXcOO/YyzOIydJxZnRvaySiBOgO75kenMaHQe2xfYw zN/g== X-Gm-Message-State: AOJu0YywmOr/NQpwOpS8avHpixfb3wjfShZFlXuNt0Z40o9/Be4JkHbp EnX/zxZT1ZzwsrM+P2Ci0Q== X-Google-Smtp-Source: AGHT+IG5Qn4okTAXsaklECzN/yWpEtICdnR3OfuVJL4hvBUlGPHGcuBypHBXsvDRaC4k9u3jtR72IA== X-Received: by 2002:a05:6870:2197:b0:206:78d6:c652 with SMTP id l23-20020a056870219700b0020678d6c652mr12227966oae.60.1705434516557; Tue, 16 Jan 2024 11:48:36 -0800 (PST) Received: from [192.168.2.16] (070-113-014-216.res.spectrum.com. [70.113.14.216]) by smtp.gmail.com with ESMTPSA id gv5-20020a056870aa0500b0020602055f18sm3204303oab.35.2024.01.16.11.48.35 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 16 Jan 2024 11:48:36 -0800 (PST) Message-ID: Date: Tue, 16 Jan 2024 13:48:33 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Why scan all columns when we select distinct c1? Content-Language: en-US To: Tom Lane , Ron Johnson Cc: Yongtao Huang , pgsql-general@postgresql.org References: <1239078.1705247163@sss.pgh.pa.us> From: Jim Nasby In-Reply-To: <1239078.1705247163@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/14/24 9:46 AM, Tom Lane wrote: > Ron Johnson writes: >> You can't scan just one column of a row-oriented table. Technically you kinda can, depending on your definition of "scan". >> 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 piece of info that's not mentioned here is how tuples (rows) are actually processed to extract individual datums (columns). The full details are in heap_deform_tuple() in backend/access/common/heaptuple.c, but the general gist is that (ignoring nulls) to read a tuple the code has to go datum by datum, computing the size of each datum to determine the physical location of the *next* datum. So if you want the 3rd datum in a tuple, you need to calculate the size of the 1st datum to see where the 2nd datum lives, and then compute the size of the 2nd datum to see where the 3rd one lives. In this example, if c1 is literally the first column in the table, then heap_deform_tuple is free to ignore everything else in the tuple, so long as the code calling heap_deform_tuple() knows to ask for only 1 datum. If all that sounds kinda expensive and tedious: you're right, it is, and it's why deforming tuples is generally done as late as possible. Based on what Tom's saying, in 9.6+ the HashAggregate code would be calling heap_deform_tuple(), and I'd expect it to only be retrieving c1. Without going through all the code, I think what's happening in 9.4 is the projection ends up calling heap_deform_tuple instead of the HashAgg code. It's still only grabbing c1; it's just doing it sooner rather than later. In this particular case I don't think it'd make much difference, but in more complicated queries it could certainly have a noticable effect. Either way, the explain output is kinda confusing. IMO it'd be more accurate if it said something like "Output: t1". And Ron's main point that you're going to be reading an entire row of t1 from the OS is also true. BTW, there's another place where the code waits as long as possible to access actual data in the hopes of avoiding needless work and that's values that have been TOASTed. heap_deform_tuple() doesn't actually need to de-toast data, so it will simply return a Datum that is a "pointer" (not a C pointer) to the toasted data. That will only be detoasted if something actually needs the actual data. In some cases that be a big performance win. -- Jim Nasby, Data Architect, Austin TX