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

Commit6efca23

Browse files
committed
Add regression tests exercising the non-hashed code paths in nodeSetop.c.
Perusal of the code coverage report shows that the existing regressiontest cases for INTERSECT and EXCEPT seemingly all prefer the SETOP_HASHEDimplementation. Add some test cases in which we force use of theSETOP_SORTED mode.
1 parentee844bb commit6efca23

File tree

2 files changed

+118
-0
lines changed

2 files changed

+118
-0
lines changed

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

Lines changed: 90 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -345,6 +345,96 @@ ERROR: FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
345345
1 | 2 | 3
346346
(1 row)
347347

348+
-- exercise both hashed and sorted implementations of INTERSECT/EXCEPT
349+
set enable_hashagg to on;
350+
explain (costs off)
351+
select count(*) from
352+
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
353+
QUERY PLAN
354+
------------------------------------------------------------------------------------
355+
Aggregate
356+
-> Subquery Scan on ss
357+
-> HashSetOp Intersect
358+
-> Append
359+
-> Subquery Scan on "*SELECT* 2"
360+
-> Seq Scan on tenk1
361+
-> Subquery Scan on "*SELECT* 1"
362+
-> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1
363+
(8 rows)
364+
365+
select count(*) from
366+
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
367+
count
368+
-------
369+
5000
370+
(1 row)
371+
372+
explain (costs off)
373+
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
374+
QUERY PLAN
375+
------------------------------------------------------------------------
376+
HashSetOp Except
377+
-> Append
378+
-> Subquery Scan on "*SELECT* 1"
379+
-> Index Only Scan using tenk1_unique1 on tenk1
380+
-> Subquery Scan on "*SELECT* 2"
381+
-> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1
382+
Filter: (unique2 <> 10)
383+
(7 rows)
384+
385+
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
386+
unique1
387+
---------
388+
10
389+
(1 row)
390+
391+
set enable_hashagg to off;
392+
explain (costs off)
393+
select count(*) from
394+
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
395+
QUERY PLAN
396+
------------------------------------------------------------------------------------------
397+
Aggregate
398+
-> Subquery Scan on ss
399+
-> SetOp Intersect
400+
-> Sort
401+
Sort Key: "*SELECT* 2".fivethous
402+
-> Append
403+
-> Subquery Scan on "*SELECT* 2"
404+
-> Seq Scan on tenk1
405+
-> Subquery Scan on "*SELECT* 1"
406+
-> Index Only Scan using tenk1_unique1 on tenk1 tenk1_1
407+
(10 rows)
408+
409+
select count(*) from
410+
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
411+
count
412+
-------
413+
5000
414+
(1 row)
415+
416+
explain (costs off)
417+
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
418+
QUERY PLAN
419+
------------------------------------------------------------------------------
420+
SetOp Except
421+
-> Sort
422+
Sort Key: "*SELECT* 1".unique1
423+
-> Append
424+
-> Subquery Scan on "*SELECT* 1"
425+
-> Index Only Scan using tenk1_unique1 on tenk1
426+
-> Subquery Scan on "*SELECT* 2"
427+
-> Index Only Scan using tenk1_unique2 on tenk1 tenk1_1
428+
Filter: (unique2 <> 10)
429+
(9 rows)
430+
431+
select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
432+
unique1
433+
---------
434+
10
435+
(1 row)
436+
437+
reset enable_hashagg;
348438
--
349439
-- Mixed types
350440
--

‎src/test/regress/sql/union.sql

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -118,6 +118,34 @@ SELECT q1 FROM int8_tbl EXCEPT ALL SELECT q1 FROM int8_tbl FOR NO KEY UPDATE;
118118
(SELECT1,2,3UNIONSELECT4,5,6) EXCEPTSELECT4,5,6;
119119
(SELECT1,2,3UNIONSELECT4,5,6ORDER BY1,2) EXCEPTSELECT4,5,6;
120120

121+
-- exercise both hashed and sorted implementations of INTERSECT/EXCEPT
122+
123+
set enable_hashagg toon;
124+
125+
explain (costs off)
126+
selectcount(*)from
127+
(select unique1from tenk1 intersectselect fivethousfrom tenk1 ) ss;
128+
selectcount(*)from
129+
(select unique1from tenk1 intersectselect fivethousfrom tenk1 ) ss;
130+
131+
explain (costs off)
132+
select unique1from tenk1 exceptselect unique2from tenk1where unique2!=10;
133+
select unique1from tenk1 exceptselect unique2from tenk1where unique2!=10;
134+
135+
set enable_hashagg to off;
136+
137+
explain (costs off)
138+
selectcount(*)from
139+
(select unique1from tenk1 intersectselect fivethousfrom tenk1 ) ss;
140+
selectcount(*)from
141+
(select unique1from tenk1 intersectselect fivethousfrom tenk1 ) ss;
142+
143+
explain (costs off)
144+
select unique1from tenk1 exceptselect unique2from tenk1where unique2!=10;
145+
select unique1from tenk1 exceptselect unique2from tenk1where unique2!=10;
146+
147+
reset enable_hashagg;
148+
121149
--
122150
-- Mixed types
123151
--

0 commit comments

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