Hello everyone,
I am working with the Postgres optimizer for the first time, so bear with me...
I want to extend the optimizer to deal with aggregate queries a bit
better. The idea is from an old paper by Chaudhuri and Shim in VLDB
94. The gist of it is that when computing aggregates over the result
of joining multiple tables, under some conditions the aggregate can be
pushed into the join tree to reduce the size of join operands making
resulting plans cheaper.
So here is my problem, due to the path/plan separation of the Postgres
optimizer, this is not trivial (joins are decided in path, aggregates
decided in plan). As it stands, aggregate nodes can only appear as the
top node of subqueries.
Before I start trying this (creating aggregate paths seems the
reasonable thing to do) I would like your counsel.
1) Regardless of the optimization problem, is the executor able to
execute aggregate nodes within join trees (that is, not as the result
of subqueries)?
2) Has anyone tried something like this before?
3) For debugging purposes: Has anyone figured out a way to feed
hand-crafted plans to the executor? Setting up some of the data
structures (PlannerInfo, target lists) etc. does not look trivial. By
this I mean, beyond giving explicit join clauses in queries.
4) Any other suggestions?
Thank you very much,
Hector