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

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

> On Wed, 19 Aug 2020 at 16:18, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> >
> >
> >
> > st 19. 8. 2020 v 5:48 odesílatel David Rowley <dgrowleyml(at)gmail(dot)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
>