Re: Hybrid Hash/Nested Loop joins and caching results from subplans

On Sat, Aug 29, 2020 at 3:33 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I think David's points elsewhere on the thread about ProjectSet and
> Materialize nodes are interesting.

Indeed, I'm now finding it very difficult to look past the similarity with:

postgres=# explain select count(*) from t t1 cross join t t2;
QUERY PLAN
----------------------------------------------------------------------------
Aggregate (cost=1975482.56..1975482.57 rows=1 width=8)
-> Nested Loop (cost=0.00..1646293.50 rows=131675625 width=0)
-> Seq Scan on t t1 (cost=0.00..159.75 rows=11475 width=0)
-> Materialize (cost=0.00..217.12 rows=11475 width=0)
-> Seq Scan on t t2 (cost=0.00..159.75 rows=11475 width=0)
(5 rows)

I wonder what it would take to overcome the overheads of the separate
Result Cache node, with techniques to step out of the way or something
like that.

> [tricky philosophical questions about ancient and maybe in some cases arbitrary choices]

Ack.