Unnecessary calculations in Execproject
Hi all,
I'm recently working on debugging an extension that utilizes user-defined operator
classes, and I find it hard to understand some of the details.
I'll use extension cube as an example to briefly describe my problem, execute
following sql,
```
CREATE EXTENSION cube;
CREATE TABLE test(a int,b cube);
CREATE INDEX ON test USING gist(b);
INSERT INTO test VALUES (1, '[(1), (2)]');
SET enable_seqscan TO off;
SELECT a FROM test ORDER BY b~>1;
```
In this case, the tuple returned by indexscan has been ordered according to the
expression b~>1, but this expression is still executed in Execproject.
I hacked some of the code into my extension to skip these unnecessary operations,
and the performance will improve by 2%~5%. Then I think that if there is a way
to remove the nodes corresponding to these redundant operations in the targetlist
when initializing the execute node, this performance optimization will be applicable
widely. If this idea can be approved, I can try to write a patch for it.
Warm regards,
Zhao.
Hi Zhao,
Thanks for sharing.
I hacked some of the code into my extension to skip these unnecessary operations,
and the performance will improve by 2%~5%. Then I think that if there is a way
to remove the nodes corresponding to these redundant operations in the targetlist
when initializing the execute node, this performance optimization will be applicable
widely. If this idea can be approved, I can try to write a patch for it.
Sounds like a promising area of research / improvement. If it improves
performance for certain queries and doesn't cause any performance
degradation for others, I don't see why it wouldn't be approved.
Unfortunately it's hard to tell more without any specific patch on
hands.
--
Best regards,
Aleksander Alekseev
On Fri, 19 Sept 2025 at 15:59, Aleksander Alekseev
<aleksander@tigerdata.com> wrote:
Hi Zhao,
Thanks for sharing.
I hacked some of the code into my extension to skip these unnecessary operations,
and the performance will improve by 2%~5%. Then I think that if there is a way
to remove the nodes corresponding to these redundant operations in the targetlist
when initializing the execute node, this performance optimization will be applicable
widely. If this idea can be approved, I can try to write a patch for it.Sounds like a promising area of research / improvement. If it improves
performance for certain queries and doesn't cause any performance
degradation for others, I don't see why it wouldn't be approved.
Unfortunately it's hard to tell more without any specific patch on
hands.
FYI, this issue has been discussed a few times before over time, more
recently with [0]/messages/by-id/2ca5865b-4693-40e5-8f78-f3b45d5378fb@iki.fi and [1]/messages/by-id/flat/CABSN6Vc0VXr+1w+dn0iUJ--LgGUwM6XwBxcXN+vF15fMsYE9cg@mail.gmail.com.
Kind regards,
Matthias van de Meent
Databricks
[0]: /messages/by-id/2ca5865b-4693-40e5-8f78-f3b45d5378fb@iki.fi
[1]: /messages/by-id/flat/CABSN6Vc0VXr+1w+dn0iUJ--LgGUwM6XwBxcXN+vF15fMsYE9cg@mail.gmail.com