diff options
| -rw-r--r-- | src/backend/executor/nodeSubplan.c | 6 | ||||
| -rw-r--r-- | src/test/regress/expected/subselect.out | 123 | ||||
| -rw-r--r-- | src/test/regress/sql/subselect.sql | 65 |
3 files changed, 191 insertions, 3 deletions
diff --git a/src/backend/executor/nodeSubplan.c b/src/backend/executor/nodeSubplan.c index ff953178797..298b7757f57 100644 --- a/src/backend/executor/nodeSubplan.c +++ b/src/backend/executor/nodeSubplan.c @@ -495,8 +495,6 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext) * need to store subplan output rows that contain NULL. */ MemoryContextReset(node->hashtablecxt); - node->hashtable = NULL; - node->hashnulls = NULL; node->havehashrows = false; node->havenullrows = false; @@ -533,7 +531,7 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext) } if (node->hashnulls) - ResetTupleHashTable(node->hashtable); + ResetTupleHashTable(node->hashnulls); else node->hashnulls = BuildTupleHashTableExt(node->parent, node->descRight, @@ -549,6 +547,8 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext) node->hashtempcxt, false); } + else + node->hashnulls = NULL; /* * We are probably in a short-lived expression-evaluation context. Switch diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 71a677b7680..4c6cd5f1466 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -874,6 +874,129 @@ explain (verbose, costs off) (8 rows) -- +-- Test rescan of a hashed subplan (the use of random() is to prevent the +-- sub-select from being pulled up, which would result in not hashing) +-- +explain (verbose, costs off) +select sum(ss.tst::int) from + onek o cross join lateral ( + select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst, + random() as r + from onek i where i.unique1 = o.unique1 ) ss +where o.ten = 0; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + Aggregate + Output: sum((((hashed SubPlan 1)))::integer) + -> Nested Loop + Output: ((hashed SubPlan 1)) + -> Seq Scan on public.onek o + Output: o.unique1, o.unique2, o.two, o.four, o.ten, o.twenty, o.hundred, o.thousand, o.twothousand, o.fivethous, o.tenthous, o.odd, o.even, o.stringu1, o.stringu2, o.string4 + Filter: (o.ten = 0) + -> Index Scan using onek_unique1 on public.onek i + Output: (hashed SubPlan 1), random() + Index Cond: (i.unique1 = o.unique1) + SubPlan 1 + -> Seq Scan on public.int4_tbl + Output: int4_tbl.f1 + Filter: (int4_tbl.f1 <= $0) +(14 rows) + +select sum(ss.tst::int) from + onek o cross join lateral ( + select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst, + random() as r + from onek i where i.unique1 = o.unique1 ) ss +where o.ten = 0; + sum +----- + 100 +(1 row) + +-- +-- Test rescan of a SetOp node +-- +explain (costs off) +select count(*) from + onek o cross join lateral ( + select * from onek i1 where i1.unique1 = o.unique1 + except + select * from onek i2 where i2.unique1 = o.unique2 + ) ss +where o.ten = 1; + QUERY PLAN +------------------------------------------------------------------------------ + Aggregate + -> Nested Loop + -> Seq Scan on onek o + Filter: (ten = 1) + -> Subquery Scan on ss + -> HashSetOp Except + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Index Scan using onek_unique1 on onek i1 + Index Cond: (unique1 = o.unique1) + -> Subquery Scan on "*SELECT* 2" + -> Index Scan using onek_unique1 on onek i2 + Index Cond: (unique1 = o.unique2) +(13 rows) + +select count(*) from + onek o cross join lateral ( + select * from onek i1 where i1.unique1 = o.unique1 + except + select * from onek i2 where i2.unique1 = o.unique2 + ) ss +where o.ten = 1; + count +------- + 100 +(1 row) + +-- +-- Test rescan of a RecursiveUnion node +-- +explain (costs off) +select sum(o.four), sum(ss.a) from + onek o cross join lateral ( + with recursive x(a) as + (select o.four as a + union + select a + 1 from x + where a < 10) + select * from x + ) ss +where o.ten = 1; + QUERY PLAN +--------------------------------------------------- + Aggregate + -> Nested Loop + -> Seq Scan on onek o + Filter: (ten = 1) + -> CTE Scan on x + CTE x + -> Recursive Union + -> Result + -> WorkTable Scan on x x_1 + Filter: (a < 10) +(10 rows) + +select sum(o.four), sum(ss.a) from + onek o cross join lateral ( + with recursive x(a) as + (select o.four as a + union + select a + 1 from x + where a < 10) + select * from x + ) ss +where o.ten = 1; + sum | sum +------+------ + 1700 | 5350 +(1 row) + +-- -- Check we don't misoptimize a NOT IN where the subquery returns no rows. -- create temp table notinouter (a int); diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index bd8d2f63d80..893d8d0f621 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -494,6 +494,71 @@ explain (verbose, costs off) (select (select random() where y=y) as x from (values(1),(2)) v(y)) ss; -- +-- Test rescan of a hashed subplan (the use of random() is to prevent the +-- sub-select from being pulled up, which would result in not hashing) +-- +explain (verbose, costs off) +select sum(ss.tst::int) from + onek o cross join lateral ( + select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst, + random() as r + from onek i where i.unique1 = o.unique1 ) ss +where o.ten = 0; + +select sum(ss.tst::int) from + onek o cross join lateral ( + select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst, + random() as r + from onek i where i.unique1 = o.unique1 ) ss +where o.ten = 0; + +-- +-- Test rescan of a SetOp node +-- +explain (costs off) +select count(*) from + onek o cross join lateral ( + select * from onek i1 where i1.unique1 = o.unique1 + except + select * from onek i2 where i2.unique1 = o.unique2 + ) ss +where o.ten = 1; + +select count(*) from + onek o cross join lateral ( + select * from onek i1 where i1.unique1 = o.unique1 + except + select * from onek i2 where i2.unique1 = o.unique2 + ) ss +where o.ten = 1; + +-- +-- Test rescan of a RecursiveUnion node +-- +explain (costs off) +select sum(o.four), sum(ss.a) from + onek o cross join lateral ( + with recursive x(a) as + (select o.four as a + union + select a + 1 from x + where a < 10) + select * from x + ) ss +where o.ten = 1; + +select sum(o.four), sum(ss.a) from + onek o cross join lateral ( + with recursive x(a) as + (select o.four as a + union + select a + 1 from x + where a < 10) + select * from x + ) ss +where o.ten = 1; + +-- -- Check we don't misoptimize a NOT IN where the subquery returns no rows. -- create temp table notinouter (a int); |