Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit58c47cc

Browse files
committed
Correctly re-use hash tables in buildSubPlanHash().
Commit356687b omitted to remove leftover code for destroyinga hashed subplan's hash tables, with the result that the tableswere always rebuilt not reused; this leads to severe memoryleakage if a hashed subplan is re-executed enough times.Moreover, the code for reusing the hashnulls table had a typothat would have made it do the wrong thing if it were reached.Looking at the code coverage report shows severe under-coverageof the potential callers of ResetTupleHashTable, so add some testcases that exercise them.Andreas Karlsson and Tom Lane, per reports from Ranier Vilelaand Justin Pryzby.Backpatch to v11, as the faulty commit was.Discussion:https://postgr.es/m/edb62547-c453-c35b-3ed6-a069e4d6b937@proxel.seDiscussion:https://postgr.es/m/CAEudQAo=DCebm1RXtig9OH+QivpS97sMkikt0A9qHmMUs+g6ZA@mail.gmail.comDiscussion:https://postgr.es/m/20200210032547.GA1412@telsasoft.com
1 parent6afc8ae commit58c47cc

File tree

3 files changed

+191
-3
lines changed

3 files changed

+191
-3
lines changed

‎src/backend/executor/nodeSubplan.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -495,8 +495,6 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
495495
* need to store subplan output rows that contain NULL.
496496
*/
497497
MemoryContextReset(node->hashtablecxt);
498-
node->hashtable=NULL;
499-
node->hashnulls=NULL;
500498
node->havehashrows= false;
501499
node->havenullrows= false;
502500

@@ -533,7 +531,7 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
533531
}
534532

535533
if (node->hashnulls)
536-
ResetTupleHashTable(node->hashtable);
534+
ResetTupleHashTable(node->hashnulls);
537535
else
538536
node->hashnulls=BuildTupleHashTableExt(node->parent,
539537
node->descRight,
@@ -549,6 +547,8 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
549547
node->hashtempcxt,
550548
false);
551549
}
550+
else
551+
node->hashnulls=NULL;
552552

553553
/*
554554
* We are probably in a short-lived expression-evaluation context. Switch

‎src/test/regress/expected/subselect.out

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -873,6 +873,129 @@ explain (verbose, costs off)
873873
One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
874874
(8 rows)
875875

876+
--
877+
-- Test rescan of a hashed subplan (the use of random() is to prevent the
878+
-- sub-select from being pulled up, which would result in not hashing)
879+
--
880+
explain (verbose, costs off)
881+
select sum(ss.tst::int) from
882+
onek o cross join lateral (
883+
select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst,
884+
random() as r
885+
from onek i where i.unique1 = o.unique1 ) ss
886+
where o.ten = 0;
887+
QUERY PLAN
888+
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
889+
Aggregate
890+
Output: sum((((hashed SubPlan 1)))::integer)
891+
-> Nested Loop
892+
Output: ((hashed SubPlan 1))
893+
-> Seq Scan on public.onek o
894+
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
895+
Filter: (o.ten = 0)
896+
-> Index Scan using onek_unique1 on public.onek i
897+
Output: (hashed SubPlan 1), random()
898+
Index Cond: (i.unique1 = o.unique1)
899+
SubPlan 1
900+
-> Seq Scan on public.int4_tbl
901+
Output: int4_tbl.f1
902+
Filter: (int4_tbl.f1 <= $0)
903+
(14 rows)
904+
905+
select sum(ss.tst::int) from
906+
onek o cross join lateral (
907+
select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst,
908+
random() as r
909+
from onek i where i.unique1 = o.unique1 ) ss
910+
where o.ten = 0;
911+
sum
912+
-----
913+
100
914+
(1 row)
915+
916+
--
917+
-- Test rescan of a SetOp node
918+
--
919+
explain (costs off)
920+
select count(*) from
921+
onek o cross join lateral (
922+
select * from onek i1 where i1.unique1 = o.unique1
923+
except
924+
select * from onek i2 where i2.unique1 = o.unique2
925+
) ss
926+
where o.ten = 1;
927+
QUERY PLAN
928+
------------------------------------------------------------------------------
929+
Aggregate
930+
-> Nested Loop
931+
-> Seq Scan on onek o
932+
Filter: (ten = 1)
933+
-> Subquery Scan on ss
934+
-> HashSetOp Except
935+
-> Append
936+
-> Subquery Scan on "*SELECT* 1"
937+
-> Index Scan using onek_unique1 on onek i1
938+
Index Cond: (unique1 = o.unique1)
939+
-> Subquery Scan on "*SELECT* 2"
940+
-> Index Scan using onek_unique1 on onek i2
941+
Index Cond: (unique1 = o.unique2)
942+
(13 rows)
943+
944+
select count(*) from
945+
onek o cross join lateral (
946+
select * from onek i1 where i1.unique1 = o.unique1
947+
except
948+
select * from onek i2 where i2.unique1 = o.unique2
949+
) ss
950+
where o.ten = 1;
951+
count
952+
-------
953+
100
954+
(1 row)
955+
956+
--
957+
-- Test rescan of a RecursiveUnion node
958+
--
959+
explain (costs off)
960+
select sum(o.four), sum(ss.a) from
961+
onek o cross join lateral (
962+
with recursive x(a) as
963+
(select o.four as a
964+
union
965+
select a + 1 from x
966+
where a < 10)
967+
select * from x
968+
) ss
969+
where o.ten = 1;
970+
QUERY PLAN
971+
---------------------------------------------------
972+
Aggregate
973+
-> Nested Loop
974+
-> Seq Scan on onek o
975+
Filter: (ten = 1)
976+
-> CTE Scan on x
977+
CTE x
978+
-> Recursive Union
979+
-> Result
980+
-> WorkTable Scan on x x_1
981+
Filter: (a < 10)
982+
(10 rows)
983+
984+
select sum(o.four), sum(ss.a) from
985+
onek o cross join lateral (
986+
with recursive x(a) as
987+
(select o.four as a
988+
union
989+
select a + 1 from x
990+
where a < 10)
991+
select * from x
992+
) ss
993+
where o.ten = 1;
994+
sum | sum
995+
------+------
996+
1700 | 5350
997+
(1 row)
998+
876999
--
8771000
-- Check we don't misoptimize a NOT IN where the subquery returns no rows.
8781001
--

‎src/test/regress/sql/subselect.sql

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -493,6 +493,71 @@ explain (verbose, costs off)
493493
select x, xfrom
494494
(select (select random()where y=y)as xfrom (values(1),(2)) v(y)) ss;
495495

496+
--
497+
-- Test rescan of a hashed subplan (the use of random() is to prevent the
498+
-- sub-select from being pulled up, which would result in not hashing)
499+
--
500+
explain (verbose, costs off)
501+
selectsum(ss.tst::int)from
502+
onek ocross join lateral (
503+
selecti.tenin (select f1from int4_tblwhere f1<=o.hundred)as tst,
504+
random()as r
505+
from onek iwherei.unique1=o.unique1 ) ss
506+
whereo.ten=0;
507+
508+
selectsum(ss.tst::int)from
509+
onek ocross join lateral (
510+
selecti.tenin (select f1from int4_tblwhere f1<=o.hundred)as tst,
511+
random()as r
512+
from onek iwherei.unique1=o.unique1 ) ss
513+
whereo.ten=0;
514+
515+
--
516+
-- Test rescan of a SetOp node
517+
--
518+
explain (costs off)
519+
selectcount(*)from
520+
onek ocross join lateral (
521+
select*from onek i1wherei1.unique1=o.unique1
522+
except
523+
select*from onek i2wherei2.unique1=o.unique2
524+
) ss
525+
whereo.ten=1;
526+
527+
selectcount(*)from
528+
onek ocross join lateral (
529+
select*from onek i1wherei1.unique1=o.unique1
530+
except
531+
select*from onek i2wherei2.unique1=o.unique2
532+
) ss
533+
whereo.ten=1;
534+
535+
--
536+
-- Test rescan of a RecursiveUnion node
537+
--
538+
explain (costs off)
539+
selectsum(o.four),sum(ss.a)from
540+
onek ocross join lateral (
541+
with recursive x(a)as
542+
(selecto.fouras a
543+
union
544+
select a+1from x
545+
where a<10)
546+
select*from x
547+
) ss
548+
whereo.ten=1;
549+
550+
selectsum(o.four),sum(ss.a)from
551+
onek ocross join lateral (
552+
with recursive x(a)as
553+
(selecto.fouras a
554+
union
555+
select a+1from x
556+
where a<10)
557+
select*from x
558+
) ss
559+
whereo.ten=1;
560+
496561
--
497562
-- Check we don't misoptimize a NOT IN where the subquery returns no rows.
498563
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp