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

čt 20. 8. 2020 v 0:04 odesílatel David Rowley <dgrowleyml@gmail.com> napsal:

> On Wed, 19 Aug 2020 at 16:18, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
> >
> >
> >
> > st 19. 8. 2020 v 5:48 odesílatel David Rowley <dgrowleyml@gmail.com>
> napsal:
> >> Current method:
> >>
> >> regression=# explain (analyze, costs off, timing off, summary off)
> >> select twenty, (select count(*) from tenk1 t2 where t1.twenty =
> >> t2.twenty) from tenk1 t1;
> >>                              QUERY PLAN
> >> ---------------------------------------------------------------------
> >>  Seq Scan on tenk1 t1 (actual rows=10000 loops=1)
> >>    SubPlan 1
> >>      ->  Result Cache (actual rows=1 loops=10000)
> >>            Cache Key: t1.twenty
> >>            Hits: 9980  Misses: 20  Evictions: 0  Overflows: 0
> >>            ->  Aggregate (actual rows=1 loops=20)
> >>                  ->  Seq Scan on tenk1 t2 (actual rows=500 loops=20)
> >>                        Filter: (t1.twenty = twenty)
> >>                        Rows Removed by Filter: 9500
> >> (9 rows)
> >>
> >> Andres' suggestion:
> >>
> >> regression=# explain (analyze, costs off, timing off, summary off)
> >> select twenty, (select count(*) from tenk1 t2 where t1.twenty =
> >> t2.twenty) from tenk1 t1;
> >>                              QUERY PLAN
> >> ---------------------------------------------------------------------
> >>  Seq Scan on tenk1 t1 (actual rows=10000 loops=1)
> >>    SubPlan 1
> >>     Cache Key: t1.twenty  Hits: 9980  Misses: 20  Evictions: 0
> Overflows: 0
> >>     ->  Aggregate (actual rows=1 loops=20)
> >>           ->  Seq Scan on tenk1 t2 (actual rows=500 loops=20)
> >>                 Filter: (t1.twenty = twenty)
> >>                 Rows Removed by Filter: 9500
> >> (7 rows)
>
> > I didn't do performance tests, that should be necessary, but I think
> Andres' variant is a little bit more readable.
>
> Thanks for chiming in on this.  I was just wondering about the
> readability part and what makes the one with the Result Cache node
> less readable?  I can think of a couple of reasons you might have this
> view and just wanted to double-check what it is.
>

It is more compact - less rows, less nesting levels


> David
>