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

On Sat, 29 Aug 2020 at 02:54, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> I'm open to ideas to make the comparison fairer.

While on that, it's not just queries that don't require the cached
tuple to be deformed that are slower. Here's a couple of example that
do requite both patches to deform the cached tuple:

Some other results that do result in both patches deforming tuples
still slows that v7 is faster:

Query1:

v7 + attached patch
postgres=# explain (analyze, timing off) select count(l.a) from
hundredk hk inner join lookup100 l on hk.one = l.a;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=378570.41..378570.42 rows=1 width=8) (actual rows=1 loops=1)
-> Nested Loop Cached (cost=0.43..353601.00 rows=9987763 width=4)
(actual rows=10000000 loops=1)
Cache Key: $0
Hits: 99999 Misses: 1 Evictions: 0 Overflows: 0
-> Seq Scan on hundredk hk (cost=0.00..1637.00 rows=100000
width=4) (actual rows=100000 loops=1)
-> Index Only Scan using lookup100_a_idx on lookup100 l
(cost=0.43..2.52 rows=100 width=4) (actual rows=100 loops=1)
Index Cond: (a = hk.one)
Heap Fetches: 0
Planning Time: 0.050 ms
Execution Time: 928.698 ms
(10 rows)

v7 only:
postgres=# explain (analyze, timing off) select count(l.a) from
hundredk hk inner join lookup100 l on hk.one = l.a;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=152861.19..152861.20 rows=1 width=8) (actual rows=1 loops=1)
-> Nested Loop (cost=0.45..127891.79 rows=9987763 width=4)
(actual rows=10000000 loops=1)
-> Seq Scan on hundredk hk (cost=0.00..1637.00 rows=100000
width=4) (actual rows=100000 loops=1)
-> Result Cache (cost=0.45..2.53 rows=100 width=4) (actual
rows=100 loops=100000)
Cache Key: hk.one
Hits: 99999 Misses: 1 Evictions: 0 Overflows: 0
-> Index Only Scan using lookup100_a_idx on lookup100
l (cost=0.43..2.52 rows=100 width=4) (actual rows=100 loops=1)
Index Cond: (a = hk.one)
Heap Fetches: 0
Planning Time: 0.604 ms
Execution Time: 897.958 ms
(11 rows)

Query2:

v7 + attached patch
postgres=# explain (analyze, timing off) select * from hundredk hk
inner join lookup100 l on hk.one = l.a;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Nested Loop Cached (cost=0.43..353601.00 rows=9987763 width=28)
(actual rows=10000000 loops=1)
Cache Key: $0
Hits: 99999 Misses: 1 Evictions: 0 Overflows: 0
-> Seq Scan on hundredk hk (cost=0.00..1637.00 rows=100000
width=24) (actual rows=100000 loops=1)
-> Index Only Scan using lookup100_a_idx on lookup100 l
(cost=0.43..2.52 rows=100 width=4) (actual rows=100 loops=1)
Index Cond: (a = hk.one)
Heap Fetches: 0
Planning Time: 0.621 ms
Execution Time: 883.610 ms
(9 rows)

v7 only:
postgres=# explain (analyze, timing off) select * from hundredk hk
inner join lookup100 l on hk.one = l.a;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.45..127891.79 rows=9987763 width=28) (actual
rows=10000000 loops=1)
-> Seq Scan on hundredk hk (cost=0.00..1637.00 rows=100000
width=24) (actual rows=100000 loops=1)
-> Result Cache (cost=0.45..2.53 rows=100 width=4) (actual
rows=100 loops=100000)
Cache Key: hk.one
Hits: 99999 Misses: 1 Evictions: 0 Overflows: 0
-> Index Only Scan using lookup100_a_idx on lookup100 l
(cost=0.43..2.52 rows=100 width=4) (actual rows=100 loops=1)
Index Cond: (a = hk.one)
Heap Fetches: 0
Planning Time: 0.088 ms
Execution Time: 870.601 ms
(10 rows)

David