summaryrefslogtreecommitdiff
path: root/src/test/regress/expected/join.out
diff options
context:
space:
mode:
authorDavid Rowley <drowley@postgresql.org>2021-04-01 13:33:23 +1300
committerDavid Rowley <drowley@postgresql.org>2021-04-01 13:33:23 +1300
commit28b3e3905c982c42fb10ee800e6f881e9742c89d (patch)
tree5368a84ddedf0c0da61fc9ec73ea148658821f0b /src/test/regress/expected/join.out
parentb6002a796dc0bfe721db5eaa54ba9d24fd9fd416 (diff)
downloadpostgresql-28b3e3905c982c42fb10ee800e6f881e9742c89d.tar.gz
Revert b6002a796
This removes "Add Result Cache executor node". It seems that something weird is going on with the tracking of cache hits and misses as highlighted by many buildfarm animals. It's not yet clear what the problem is as other parts of the plan indicate that the cache did work correctly, it's just the hits and misses that were being reported as 0. This is especially a bad time to have the buildfarm so broken, so reverting before too many more animals go red. Discussion: https://postgr.es/m/CAApHDvq_hydhfovm4=izgWs+C5HqEeRScjMbOgbpC-jRAeK3Yw@mail.gmail.com
Diffstat (limited to 'src/test/regress/expected/join.out')
-rw-r--r--src/test/regress/expected/join.out131
1 files changed, 53 insertions, 78 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 86fd3907c5..04e802d421 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -2536,7 +2536,6 @@ reset enable_nestloop;
--
set work_mem to '64kB';
set enable_mergejoin to off;
-set enable_resultcache to off;
explain (costs off)
select count(*) from tenk1 a, tenk1 b
where a.hundred = b.thousand and (b.fivethous % 10) < 10;
@@ -2560,7 +2559,6 @@ select count(*) from tenk1 a, tenk1 b
reset work_mem;
reset enable_mergejoin;
-reset enable_resultcache;
--
-- regression test for 8.2 bug with improper re-ordering of left joins
--
@@ -3665,8 +3663,8 @@ select * from tenk1 t1 left join
(tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)
on t1.hundred = t2.hundred and t1.ten = t3.ten
where t1.unique1 = 1;
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Nested Loop Left Join
-> Index Scan using tenk1_unique1 on tenk1 t1
Index Cond: (unique1 = 1)
@@ -3676,19 +3674,17 @@ where t1.unique1 = 1;
Recheck Cond: (t1.hundred = hundred)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = t1.hundred)
- -> Result Cache
- Cache Key: t2.thousand
- -> Index Scan using tenk1_unique2 on tenk1 t3
- Index Cond: (unique2 = t2.thousand)
-(13 rows)
+ -> Index Scan using tenk1_unique2 on tenk1 t3
+ Index Cond: (unique2 = t2.thousand)
+(11 rows)
explain (costs off)
select * from tenk1 t1 left join
(tenk1 t2 join tenk1 t3 on t2.thousand = t3.unique2)
on t1.hundred = t2.hundred and t1.ten + t2.ten = t3.ten
where t1.unique1 = 1;
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Nested Loop Left Join
-> Index Scan using tenk1_unique1 on tenk1 t1
Index Cond: (unique1 = 1)
@@ -3698,11 +3694,9 @@ where t1.unique1 = 1;
Recheck Cond: (t1.hundred = hundred)
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = t1.hundred)
- -> Result Cache
- Cache Key: t2.thousand
- -> Index Scan using tenk1_unique2 on tenk1 t3
- Index Cond: (unique2 = t2.thousand)
-(13 rows)
+ -> Index Scan using tenk1_unique2 on tenk1 t3
+ Index Cond: (unique2 = t2.thousand)
+(11 rows)
explain (costs off)
select count(*) from
@@ -4216,8 +4210,8 @@ where t1.f1 = ss.f1;
QUERY PLAN
--------------------------------------------------
Nested Loop
- Output: t1.f1, i8.q1, i8.q2, q1, f1
- Join Filter: (t1.f1 = f1)
+ Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
+ Join Filter: (t1.f1 = t2.f1)
-> Nested Loop Left Join
Output: t1.f1, i8.q1, i8.q2
-> Seq Scan on public.text_tbl t1
@@ -4227,14 +4221,11 @@ where t1.f1 = ss.f1;
-> Seq Scan on public.int8_tbl i8
Output: i8.q1, i8.q2
Filter: (i8.q2 = 123)
- -> Result Cache
- Output: q1, f1
- Cache Key: i8.q1
- -> Limit
- Output: (i8.q1), t2.f1
- -> Seq Scan on public.text_tbl t2
- Output: i8.q1, t2.f1
-(19 rows)
+ -> Limit
+ Output: (i8.q1), t2.f1
+ -> Seq Scan on public.text_tbl t2
+ Output: i8.q1, t2.f1
+(16 rows)
select * from
text_tbl t1
@@ -4255,13 +4246,13 @@ select * from
lateral (select i8.q1, t2.f1 from text_tbl t2 limit 1) as ss1,
lateral (select ss1.* from text_tbl t3 limit 1) as ss2
where t1.f1 = ss2.f1;
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------
Nested Loop
- Output: t1.f1, i8.q1, i8.q2, q1, f1, q1, f1
- Join Filter: (t1.f1 = f1)
+ Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1, ((i8.q1)), (t2.f1)
+ Join Filter: (t1.f1 = (t2.f1))
-> Nested Loop
- Output: t1.f1, i8.q1, i8.q2, q1, f1
+ Output: t1.f1, i8.q1, i8.q2, (i8.q1), t2.f1
-> Nested Loop Left Join
Output: t1.f1, i8.q1, i8.q2
-> Seq Scan on public.text_tbl t1
@@ -4271,21 +4262,15 @@ where t1.f1 = ss2.f1;
-> Seq Scan on public.int8_tbl i8
Output: i8.q1, i8.q2
Filter: (i8.q2 = 123)
- -> Result Cache
- Output: q1, f1
- Cache Key: i8.q1
- -> Limit
- Output: (i8.q1), t2.f1
- -> Seq Scan on public.text_tbl t2
- Output: i8.q1, t2.f1
- -> Result Cache
- Output: q1, f1
- Cache Key: q1, f1
-> Limit
- Output: (q1), (f1)
- -> Seq Scan on public.text_tbl t3
- Output: q1, f1
-(28 rows)
+ Output: (i8.q1), t2.f1
+ -> Seq Scan on public.text_tbl t2
+ Output: i8.q1, t2.f1
+ -> Limit
+ Output: ((i8.q1)), (t2.f1)
+ -> Seq Scan on public.text_tbl t3
+ Output: (i8.q1), t2.f1
+(22 rows)
select * from
text_tbl t1
@@ -4331,17 +4316,14 @@ where tt1.f1 = ss1.c0;
-> Seq Scan on public.text_tbl tt4
Output: tt4.f1
Filter: (tt4.f1 = 'foo'::text)
- -> Result Cache
+ -> Subquery Scan on ss1
Output: ss1.c0
- Cache Key: tt4.f1
- -> Subquery Scan on ss1
- Output: ss1.c0
- Filter: (ss1.c0 = 'foo'::text)
- -> Limit
- Output: (tt4.f1)
- -> Seq Scan on public.text_tbl tt5
- Output: tt4.f1
-(32 rows)
+ Filter: (ss1.c0 = 'foo'::text)
+ -> Limit
+ Output: (tt4.f1)
+ -> Seq Scan on public.text_tbl tt5
+ Output: tt4.f1
+(29 rows)
select 1 from
text_tbl as tt1
@@ -5015,40 +4997,34 @@ select count(*) from tenk1 a, lateral generate_series(1,two) g;
explain (costs off)
select count(*) from tenk1 a, lateral generate_series(1,two) g;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Aggregate
-> Nested Loop
-> Seq Scan on tenk1 a
- -> Result Cache
- Cache Key: a.two
- -> Function Scan on generate_series g
-(6 rows)
+ -> Function Scan on generate_series g
+(4 rows)
explain (costs off)
select count(*) from tenk1 a cross join lateral generate_series(1,two) g;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Aggregate
-> Nested Loop
-> Seq Scan on tenk1 a
- -> Result Cache
- Cache Key: a.two
- -> Function Scan on generate_series g
-(6 rows)
+ -> Function Scan on generate_series g
+(4 rows)
-- don't need the explicit LATERAL keyword for functions
explain (costs off)
select count(*) from tenk1 a, generate_series(1,two) g;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------
Aggregate
-> Nested Loop
-> Seq Scan on tenk1 a
- -> Result Cache
- Cache Key: a.two
- -> Function Scan on generate_series g
-(6 rows)
+ -> Function Scan on generate_series g
+(4 rows)
-- lateral with UNION ALL subselect
explain (costs off)
@@ -5103,15 +5079,14 @@ explain (costs off)
QUERY PLAN
------------------------------------------------------------------
Aggregate
- -> Nested Loop
+ -> Hash Join
+ Hash Cond: ("*VALUES*".column1 = b.unique2)
-> Nested Loop
-> Index Only Scan using tenk1_unique1 on tenk1 a
-> Values Scan on "*VALUES*"
- -> Result Cache
- Cache Key: "*VALUES*".column1
+ -> Hash
-> Index Only Scan using tenk1_unique2 on tenk1 b
- Index Cond: (unique2 = "*VALUES*".column1)
-(9 rows)
+(8 rows)
select count(*) from tenk1 a,
tenk1 b join lateral (values(a.unique1),(-1)) ss(x) on b.unique2 = ss.x;