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

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.

David