Re: Avoid computing ORDER BY junk columns unnecessarily

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)