pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Xiaoran Wang <fanfuxiaoran@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Andrew Kane <andrew@ankane.org>
Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>
Subject: Re: Avoid computing ORDER BY junk columns unnecessarily
Date: Fri, 29 Dec 2023 13:23:17 -0500
Message-ID: <3227129.1703874197@sss.pgh.pa.us> (raw)
In-Reply-To: <356d5811-d8f0-4c29-a601-b61334337b11@iki.fi>
References: <2ca5865b-4693-40e5-8f78-f3b45d5378fb@iki.fi>
	<CAGjhLkPyROkuJYpKfQxax3fBEB_0vLN_CuQEPcfB5A8+GD85dg@mail.gmail.com>
	<dd7eed69-4e99-4da2-87a8-2a94b35309fa@iki.fi>
	<1052850.1703258655@sss.pgh.pa.us>
	<02c142a8-051d-4252-a3a5-9d87be1c6e6b@iki.fi>
	<1067708.1703266312@sss.pgh.pa.us>
	<2935085.1703806977@sss.pgh.pa.us>
	<356d5811-d8f0-4c29-a601-b61334337b11@iki.fi>

Heikki Linnakangas <hlinnaka@iki.fi> writes:
> On 29/12/2023 01:42, Tom Lane wrote:
>> I didn't stop to trace the details but I'm pretty sure this is why
>> you're getting the bogus extra projections shown in the 0005 patch.

> They're not bogus. With the patches, projecting away the junk columns is 
> visible in the plan as an extra Result node, while currently it's done 
> as an implicit step in the executor. That seems fine and arguably an 
> even more honest representation of what's happening, although I don't 
> like the extra verbosity in EXPLAIN output.

I didn't bring it up in my previous message, but I'm not really on
board with trying to get rid of the executor junkfilter.  It seems
to me that that code is probably faster than a general-purpose
projection, and surely faster than an extra Result node, so I think
that is not a goal that would improve matters.

However, what I *was* trying to say is that I think those projections
occur because the lower-level plan node is still outputting the
columns you want to suppress, and then the planner realizes that it
needs a projection to create the shortened tlist.  But that's not
saving any work, because we still computed the expensive function :-(.
We need a more thorough treatment of the problem to ensure that the
lower-level plan nodes don't emit these columns either.

> Related to this, we are not currently costing the target list evaluation 
> correctly for index-only scans.

Right, and we're dumb in other ways too: if the index can return
f(x) but not x, we fail to realize that we can use it for an IOS
in the first place, because there's an assumption that we'd better
be able to fetch x.  Currently I think the best way around that
might be via the other discussion that's going on about unifying
regular indexscans and index-only scans [1].  If we do that then
we could postpone the decision about whether we actually need x
itself, and perhaps that would simplify getting this right.

I'm kind of inclined to think that it'd be best to push the
other discussion forward first, and come back to this area
when it's done, because that will be touching a lot of the
same code as well as (maybe) simplifying the planner's problem.

			regards, tom lane

[1] https://www.postgresql.org/message-id/20230609000600.syqy447e6metnvyj%40awork3.anarazel.de





view thread (12+ messages)  latest in thread

Message-ID: <3227129.1703874197@sss.pgh.pa.us>
Permalink:  ../3227129.1703874197@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3227129.1703874197@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-hackers@postgresql.org
  Cc: tgl@sss.pgh.pa.us, hlinnaka@iki.fi, fanfuxiaoran@gmail.com, andrew@ankane.org, boekewurm+postgres@gmail.com
  Subject: Re: Avoid computing ORDER BY junk columns unnecessarily
  In-Reply-To: <3227129.1703874197@sss.pgh.pa.us>
  References: <2ca5865b-4693-40e5-8f78-f3b45d5378fb@iki.fi> <CAGjhLkPyROkuJYpKfQxax3fBEB_0vLN_CuQEPcfB5A8+GD85dg@mail.gmail.com> <dd7eed69-4e99-4da2-87a8-2a94b35309fa@iki.fi> <1052850.1703258655@sss.pgh.pa.us> <02c142a8-051d-4252-a3a5-9d87be1c6e6b@iki.fi> <1067708.1703266312@sss.pgh.pa.us> <2935085.1703806977@sss.pgh.pa.us> <356d5811-d8f0-4c29-a601-b61334337b11@iki.fi> <3227129.1703874197@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox