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

Commit6dd8b00

Browse files
committed
Add more tests for hashing and hash-based plans
- Test hashing of an array of a non-hashable element type.- Test UNION [DISTINCT] with hash- and sort-based plans. (Previously, only INTERSECT and EXCEPT where tested there.)- Test UNION [DISTINCT] with a non-hashable column type. This currently reverts to a sort-based plan even if enable_hashagg is on.- Test UNION/INTERSECT/EXCEPT hash- and sort-based plans with arrays as column types. Also test an array with a non-hashable element type.- Test UNION/INTERSECT/EXCEPT similarly with row types as column types. Currently, this uses only sort-based plans because there is no hashing support for row types.- Add a test case that shows that recursive queries using UNION [DISTINCT] require hashable column types.- Add a currently failing test that uses UNION DISTINCT in a cycle-detection use case using row types as column types.Discussion:https://www.postgresql.org/message-id/flat/38eccd35-4e2d-6767-1b3c-dada1eac3124%402ndquadrant.com
1 parentbf0aa7c commit6dd8b00

File tree

6 files changed

+498
-2
lines changed

6 files changed

+498
-2
lines changed

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

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -177,6 +177,13 @@ WHERE hash_array(v)::bit(32) != hash_array_extended(v, 0)::bit(32)
177177
-------+----------+-----------+-----------
178178
(0 rows)
179179

180+
-- array hashing with non-hashable element type
181+
SELECT v as value, hash_array(v)::bit(32) as standard
182+
FROM (VALUES ('{0}'::money[])) x(v);
183+
ERROR: could not identify a hash function for type money
184+
SELECT v as value, hash_array_extended(v, 0)::bit(32) as extended0
185+
FROM (VALUES ('{0}'::money[])) x(v);
186+
ERROR: could not identify an extended hash function for type money
180187
SELECT v as value, hashbpchar(v)::bit(32) as standard,
181188
hashbpcharextended(v, 0)::bit(32) as extended0,
182189
hashbpcharextended(v, 1)::bit(32) as extended1

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

Lines changed: 356 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -345,8 +345,28 @@ 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
348+
-- exercise both hashed and sorted implementations ofUNION/INTERSECT/EXCEPT
349349
set enable_hashagg to on;
350+
explain (costs off)
351+
select count(*) from
352+
( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
353+
QUERY PLAN
354+
----------------------------------------------------------------
355+
Aggregate
356+
-> HashAggregate
357+
Group Key: tenk1.unique1
358+
-> Append
359+
-> Index Only Scan using tenk1_unique1 on tenk1
360+
-> Seq Scan on tenk1 tenk1_1
361+
(6 rows)
362+
363+
select count(*) from
364+
( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
365+
count
366+
-------
367+
10000
368+
(1 row)
369+
350370
explain (costs off)
351371
select count(*) from
352372
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
@@ -389,6 +409,27 @@ select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
389409
(1 row)
390410

391411
set enable_hashagg to off;
412+
explain (costs off)
413+
select count(*) from
414+
( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
415+
QUERY PLAN
416+
----------------------------------------------------------------------
417+
Aggregate
418+
-> Unique
419+
-> Sort
420+
Sort Key: tenk1.unique1
421+
-> Append
422+
-> Index Only Scan using tenk1_unique1 on tenk1
423+
-> Seq Scan on tenk1 tenk1_1
424+
(7 rows)
425+
426+
select count(*) from
427+
( select unique1 from tenk1 union select fivethous from tenk1 ) ss;
428+
count
429+
-------
430+
10000
431+
(1 row)
432+
392433
explain (costs off)
393434
select count(*) from
394435
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
@@ -434,6 +475,320 @@ select unique1 from tenk1 except select unique2 from tenk1 where unique2 != 10;
434475
10
435476
(1 row)
436477

478+
reset enable_hashagg;
479+
-- non-hashable type
480+
set enable_hashagg to on;
481+
explain (costs off)
482+
select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);
483+
QUERY PLAN
484+
-----------------------------------------------
485+
Unique
486+
-> Sort
487+
Sort Key: "*VALUES*".column1
488+
-> Append
489+
-> Values Scan on "*VALUES*"
490+
-> Values Scan on "*VALUES*_1"
491+
(6 rows)
492+
493+
set enable_hashagg to off;
494+
explain (costs off)
495+
select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);
496+
QUERY PLAN
497+
-----------------------------------------------
498+
Unique
499+
-> Sort
500+
Sort Key: "*VALUES*".column1
501+
-> Append
502+
-> Values Scan on "*VALUES*"
503+
-> Values Scan on "*VALUES*_1"
504+
(6 rows)
505+
506+
reset enable_hashagg;
507+
-- arrays
508+
set enable_hashagg to on;
509+
explain (costs off)
510+
select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
511+
QUERY PLAN
512+
-----------------------------------------
513+
HashAggregate
514+
Group Key: "*VALUES*".column1
515+
-> Append
516+
-> Values Scan on "*VALUES*"
517+
-> Values Scan on "*VALUES*_1"
518+
(5 rows)
519+
520+
select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
521+
x
522+
-------
523+
{1,4}
524+
{1,2}
525+
{1,3}
526+
(3 rows)
527+
528+
explain (costs off)
529+
select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
530+
QUERY PLAN
531+
-----------------------------------------------
532+
HashSetOp Intersect
533+
-> Append
534+
-> Subquery Scan on "*SELECT* 1"
535+
-> Values Scan on "*VALUES*"
536+
-> Subquery Scan on "*SELECT* 2"
537+
-> Values Scan on "*VALUES*_1"
538+
(6 rows)
539+
540+
select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
541+
x
542+
-------
543+
{1,2}
544+
(1 row)
545+
546+
explain (costs off)
547+
select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
548+
QUERY PLAN
549+
-----------------------------------------------
550+
HashSetOp Except
551+
-> Append
552+
-> Subquery Scan on "*SELECT* 1"
553+
-> Values Scan on "*VALUES*"
554+
-> Subquery Scan on "*SELECT* 2"
555+
-> Values Scan on "*VALUES*_1"
556+
(6 rows)
557+
558+
select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
559+
x
560+
-------
561+
{1,3}
562+
(1 row)
563+
564+
-- non-hashable type
565+
explain (costs off)
566+
select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);
567+
QUERY PLAN
568+
-----------------------------------------------
569+
Unique
570+
-> Sort
571+
Sort Key: "*VALUES*".column1
572+
-> Append
573+
-> Values Scan on "*VALUES*"
574+
-> Values Scan on "*VALUES*_1"
575+
(6 rows)
576+
577+
select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);
578+
x
579+
-----------
580+
{$100.00}
581+
{$200.00}
582+
{$300.00}
583+
(3 rows)
584+
585+
set enable_hashagg to off;
586+
explain (costs off)
587+
select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
588+
QUERY PLAN
589+
-----------------------------------------------
590+
Unique
591+
-> Sort
592+
Sort Key: "*VALUES*".column1
593+
-> Append
594+
-> Values Scan on "*VALUES*"
595+
-> Values Scan on "*VALUES*_1"
596+
(6 rows)
597+
598+
select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);
599+
x
600+
-------
601+
{1,2}
602+
{1,3}
603+
{1,4}
604+
(3 rows)
605+
606+
explain (costs off)
607+
select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
608+
QUERY PLAN
609+
-----------------------------------------------------
610+
SetOp Intersect
611+
-> Sort
612+
Sort Key: "*SELECT* 1".x
613+
-> Append
614+
-> Subquery Scan on "*SELECT* 1"
615+
-> Values Scan on "*VALUES*"
616+
-> Subquery Scan on "*SELECT* 2"
617+
-> Values Scan on "*VALUES*_1"
618+
(8 rows)
619+
620+
select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);
621+
x
622+
-------
623+
{1,2}
624+
(1 row)
625+
626+
explain (costs off)
627+
select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
628+
QUERY PLAN
629+
-----------------------------------------------------
630+
SetOp Except
631+
-> Sort
632+
Sort Key: "*SELECT* 1".x
633+
-> Append
634+
-> Subquery Scan on "*SELECT* 1"
635+
-> Values Scan on "*VALUES*"
636+
-> Subquery Scan on "*SELECT* 2"
637+
-> Values Scan on "*VALUES*_1"
638+
(8 rows)
639+
640+
select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);
641+
x
642+
-------
643+
{1,3}
644+
(1 row)
645+
646+
reset enable_hashagg;
647+
-- records
648+
set enable_hashagg to on;
649+
-- currently no hashing support for record, so these will still run with sort plans:
650+
explain (costs off)
651+
select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
652+
QUERY PLAN
653+
-----------------------------------------------
654+
Unique
655+
-> Sort
656+
Sort Key: "*VALUES*".column1
657+
-> Append
658+
-> Values Scan on "*VALUES*"
659+
-> Values Scan on "*VALUES*_1"
660+
(6 rows)
661+
662+
select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
663+
x
664+
-------
665+
(1,2)
666+
(1,3)
667+
(1,4)
668+
(3 rows)
669+
670+
explain (costs off)
671+
select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
672+
QUERY PLAN
673+
-----------------------------------------------------
674+
SetOp Intersect
675+
-> Sort
676+
Sort Key: "*SELECT* 1".x
677+
-> Append
678+
-> Subquery Scan on "*SELECT* 1"
679+
-> Values Scan on "*VALUES*"
680+
-> Subquery Scan on "*SELECT* 2"
681+
-> Values Scan on "*VALUES*_1"
682+
(8 rows)
683+
684+
select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
685+
x
686+
-------
687+
(1,2)
688+
(1 row)
689+
690+
explain (costs off)
691+
select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
692+
QUERY PLAN
693+
-----------------------------------------------------
694+
SetOp Except
695+
-> Sort
696+
Sort Key: "*SELECT* 1".x
697+
-> Append
698+
-> Subquery Scan on "*SELECT* 1"
699+
-> Values Scan on "*VALUES*"
700+
-> Subquery Scan on "*SELECT* 2"
701+
-> Values Scan on "*VALUES*_1"
702+
(8 rows)
703+
704+
select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
705+
x
706+
-------
707+
(1,3)
708+
(1 row)
709+
710+
-- non-hashable type
711+
explain (costs off)
712+
select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);
713+
QUERY PLAN
714+
-----------------------------------------------
715+
Unique
716+
-> Sort
717+
Sort Key: "*VALUES*".column1
718+
-> Append
719+
-> Values Scan on "*VALUES*"
720+
-> Values Scan on "*VALUES*_1"
721+
(6 rows)
722+
723+
select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);
724+
x
725+
-----------
726+
($100.00)
727+
($200.00)
728+
($300.00)
729+
(3 rows)
730+
731+
set enable_hashagg to off;
732+
explain (costs off)
733+
select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
734+
QUERY PLAN
735+
-----------------------------------------------
736+
Unique
737+
-> Sort
738+
Sort Key: "*VALUES*".column1
739+
-> Append
740+
-> Values Scan on "*VALUES*"
741+
-> Values Scan on "*VALUES*_1"
742+
(6 rows)
743+
744+
select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);
745+
x
746+
-------
747+
(1,2)
748+
(1,3)
749+
(1,4)
750+
(3 rows)
751+
752+
explain (costs off)
753+
select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
754+
QUERY PLAN
755+
-----------------------------------------------------
756+
SetOp Intersect
757+
-> Sort
758+
Sort Key: "*SELECT* 1".x
759+
-> Append
760+
-> Subquery Scan on "*SELECT* 1"
761+
-> Values Scan on "*VALUES*"
762+
-> Subquery Scan on "*SELECT* 2"
763+
-> Values Scan on "*VALUES*_1"
764+
(8 rows)
765+
766+
select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);
767+
x
768+
-------
769+
(1,2)
770+
(1 row)
771+
772+
explain (costs off)
773+
select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
774+
QUERY PLAN
775+
-----------------------------------------------------
776+
SetOp Except
777+
-> Sort
778+
Sort Key: "*SELECT* 1".x
779+
-> Append
780+
-> Subquery Scan on "*SELECT* 1"
781+
-> Values Scan on "*VALUES*"
782+
-> Subquery Scan on "*SELECT* 2"
783+
-> Values Scan on "*VALUES*_1"
784+
(8 rows)
785+
786+
select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);
787+
x
788+
-------
789+
(1,3)
790+
(1 row)
791+
437792
reset enable_hashagg;
438793
--
439794
-- Mixed types

0 commit comments

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