From: Tom Lane <tgl@sss.pgh.pa.us>
To: Heikki Linnakangas <hlinnaka@iki.fi>
cc: Xiaoran Wang <fanfuxiaoran@gmail.com>,
        pgsql-hackers <pgsql-hackers@postgresql.org>,
        Andrew Kane <andrew@ankane.org>,
        Matthias van de Meent <boekewurm+postgres@gmail.com>
Subject: Re: Avoid computing ORDER BY junk columns unnecessarily
In-reply-to: <dd7eed69-4e99-4da2-87a8-2a94b35309fa@iki.fi>
References: <2ca5865b-4693-40e5-8f78-f3b45d5378fb@iki.fi>
 <CAGjhLkPyROkuJYpKfQxax3fBEB_0vLN_CuQEPcfB5A8+GD85dg@mail.gmail.com>
 <dd7eed69-4e99-4da2-87a8-2a94b35309fa@iki.fi>
Comments: In-reply-to Heikki Linnakangas <hlinnaka@iki.fi>
	message dated "Fri, 22 Dec 2023 12:48:53 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1052849.1703258655.1@sss.pgh.pa.us>
Date: Fri, 22 Dec 2023 10:24:15 -0500
Message-ID: <1052850.1703258655@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1052850.1703258655%40sss.pgh.pa.us>
Precedence: bulk

Heikki Linnakangas <hlinnaka@iki.fi> writes:
> Hmm, so return the computed column from the index instead of recomputing 
> it? Yeah, that makes sense too and would help in this example.

Yeah, that's been on the to-do list for ages.  The main problems are
(1) we need the planner to not spend too much effort on looking for
subexpression matches, and (2) amcanreturn ability isn't implemented
by the executor in plain indexscans.  There's another thread right now
discussing fixing (2), after which we could perhaps work on this.

> It won't 
> help in all cases though, the index might not store the original value 
> in the first place.

I'm a little skeptical that an index could produce an accurate ORDER BY
result if it doesn't store the values-to-be-sorted exactly.  Any loss
of information would compromise its ability to sort nearly-identical
values correctly.  A more credible argument is that the index might
expose amcanorder ability but not amcanreturn; but what I'm saying is
that that's probably an AM implementation gap that ought to be fixed.

How much of your patchset still makes sense if we assume that we
can always extract the ORDER BY column values from the index?

			regards, tom lane