Query planning/optimization using SPI

[tsirkin]

We are running trino in very resource restricted environment.
Having a restricted amount of memory, it would be nice if we could rewrite some parts of the query into a multiple ones, with intermediate results written to disk.
e.g. having a join query like this
select * from a inner join b on a.id=b.a_id where a.property = 1234;
it would be nice to execute the query with the following plan:

1. select * from a where a.property=1234
2. write the results of above into a temporary table tmp1
3. execute select * from tmp1 inner join b on a.id=b.a_id
   From our tests this reduces the resources need drastically in our case and we are willing to pay the performance costs in this case.
   So, can we do this say using SPI? e.g. by influencing the execution plan our even rewriting it?
   Or maybe any other idea?
   (While we know about spiller it does not helps us enough)

[findepi]

relates to

#9101
#684
and #18