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

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.

David