Horton

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

Andy Fan <zhihui.fan1213@gmail.com>

View thread View on postgr.es
From: Andy Fan <zhihui.fan1213@gmail.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: David Rowley <dgrowleyml@gmail.com>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Date: 2020-05-22T00:11:51Z
Lists: pgsql-hackers 
>  My question is whether it should be added as an optional facility of a
> parameterised sub plan, rather than an always-needed full-strength node.
> That way the choice of whether to use it can happen at execution time once
> we notice that we've been called too many times.
>
>
Actually I am not sure about what does the "parameterized sub plan" mean (I
treat is a SubPlan Node), so please correct me if I misunderstand you:)
Because
the inner plan in nest loop not a SubPlan node actually.  so if bind the
facility to SubPlan node, we may loss the chances for nest loop.  And when
we
consider the usage for nest loop,  we can consider the below example, where
this
feature will be more powerful.


select j1o.i, j2_v.sum_5
from j1 j1o
inner join lateral
(select im100, sum(im5) as sum_5
from j2
where j1o.im100 = im100
and j1o.i = 1
group by im100) j2_v
on true
where j1o.i = 1;

-- 
Best Regards
Andy Fan

Commits

  1. Add Result Cache executor node (take 2)

  2. Add Result Cache executor node

  3. Allow estimate_num_groups() to pass back further details about the estimation

  4. Allow users of simplehash.h to perform direct deletions

  5. Cache if PathTarget and RestrictInfos contain volatile functions

  6. Fix pull_varnos' miscomputation of relids set for a PlaceHolderVar.