Projection pushdown to index access method

From: Chris Cleveland <ccleveland(at)dieselpoint(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Projection pushdown to index access method
Date: 2023-09-19 14:32:04
Message-ID: CABSN6Vc5hbu=jLR-9knmNjfZfciK=nFF7Qezhu-3WW-H38B+NA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I'm working on an index access method. I have a function which can appear
in a projection list which should be evaluated by the access method itself.
Example:

SELECT title, my_special_function(body)
FROM books
WHERE book_id <===> 42;

"<===>" is the operator that invokes the access method. The value returned
by my_special_function() gets calculated during the index scan, and depends
on information that exists only in the index.

How do I get the system to pull the value from the index instead of trying
to calculate it?

So far, I have created a CustomScan and set it using set_rel_pathlist_hook.
The hook function gives us a PlannerInfo, RelOptInfo, Index, and
RangeTblEntry. So far as I can tell, only RelOptInfo.reltarget.exprs gives
us any info on the SELECT expressions, but unfortunately, the exprs are Var
nodes that contain the (title, body) columns from above, and do not say
anything about my_special_function().

Where do I find the actual final projection exprs?

Am I using the right hook?

Is there any example code out there on how to do this?

I know this is possible, because the docs for PathTarget say this:

PathTarget
*
* This struct contains what we need to know during planning about the
* targetlist (output columns) that a Path will compute. Each RelOptInfo
* includes a default PathTarget, which its individual Paths may simply
* reference. However, in some cases a Path may compute outputs different
* from other Paths, and in that case we make a custom PathTarget for it.
*
*For example, an indexscan might return index expressions that would *
otherwise need to be explicitly calculated.*

--
Chris Cleveland
312-339-2677 mobile

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2023-09-19 15:13:47 Relation bulk write facility
Previous Message Peter Eisentraut 2023-09-19 14:31:35 Re: pg_resetwal tests, logging, and docs update