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