Message-ID: <dd7eed69-4e99-4da2-87a8-2a94b35309fa@iki.fi>
Date: Fri, 22 Dec 2023 12:48:53 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Avoid computing ORDER BY junk columns unnecessarily
Content-Language: en-US
To: Xiaoran Wang <fanfuxiaoran@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>,
 Andrew Kane <andrew@ankane.org>,
 Matthias van de Meent <boekewurm+postgres@gmail.com>
References: <2ca5865b-4693-40e5-8f78-f3b45d5378fb@iki.fi>
 <CAGjhLkPyROkuJYpKfQxax3fBEB_0vLN_CuQEPcfB5A8+GD85dg@mail.gmail.com>
From: Heikki Linnakangas <hlinnaka@iki.fi>
In-Reply-To: 
 <CAGjhLkPyROkuJYpKfQxax3fBEB_0vLN_CuQEPcfB5A8+GD85dg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/dd7eed69-4e99-4da2-87a8-2a94b35309fa%40iki.fi>
Precedence: bulk

On 22/12/2023 11:05, Xiaoran Wang wrote:
> I haven't dug into your patch yet, but for this problem, I have another 
> idea.
> -------
> explain verbose
>    select foo from mytable order by sha256(bar::bytea);
> 
>                                              QUERY PLAN
> 
> ------------------------------------------------------------------------------------------------
>    Index Scan using mytable_sha256_idx on public.mytable
> (cost=0.29..737.28 rows=10000 width=64)
>      Output: foo, sha256((bar)::bytea)
> (2 rows)
> 
> The index is used to satisfy the ORDER BY, but the expensive ORDER BY
> expression is still computed for every row, just to be thrown away by
> the junk filter.
> ------
> How about adding the orderby column value  in 'xs_heaptid' with the 
> 'xs_heaptid'
> together? So that we can use that value directly instead of computing it 
> when using
> an index scan to fetch the ordered data.

Hmm, so return the computed column from the index instead of recomputing 
it? Yeah, that makes sense too and would help in this example. It won't 
help in all cases though, the index might not store the original value 
in the first place.

-- 
Heikki Linnakangas
Neon (https://neon.tech)