From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Andres Freund <andres@anarazel.de>
Subject: Re: Hash support for row types
Date: Tue, 17 Nov 2020 14:25:59 +0100
Message-ID: <d1a898b2-cd3d-0519-def9-5f38163c68e8@2ndquadrant.com> (raw)
In-Reply-To: <395784.1605297108@sss.pgh.pa.us>
References: <38eccd35-4e2d-6767-1b3c-dada1eac3124@2ndquadrant.com>
<20201019233234.r6lyxbvdg5s77rvd@alap3.anarazel.de>
<e88b1974-5987-9ae8-7e54-7d66f54751cb@2ndquadrant.com>
<f7195e93-ab9d-01f0-1140-361aece79248@2ndquadrant.com>
<395784.1605297108@sss.pgh.pa.us>
I wrote a new patch to add a lot more tests around hash-based plans.
This is intended to apply separately from the other patch, and the other
patch would then "flip" some of the test cases.
On 2020-11-13 20:51, Tom Lane wrote:
> * The new test in with.sql claims to be testing row hashing, but
> it's not very apparent that any such thing actually happens. Maybe
> EXPLAIN the query, as well as execute it, to confirm that a
> hash-based plan is used.
The recursive union requires hashing, but this is not visible in the
plan. You only get an error if there is no hashing support for a type.
I have added a test for this.
For the non-recursive union, I have added more tests that show this in
the plans.
> * Is it worth devising a test case in which hashing is not possible
> because one of the columns isn't hashable? I have mixed feelings
> about this because the set of suitable column types may decrease
> to empty over time, making it hard to maintain the test case.
I used the money type for now. If someone adds hash support for that,
we'll change it. I don't think this will change too rapidly, though.
--
Peter Eisentraut
2ndQuadrant, an EDB company
https://www.2ndquadrant.com/
From dface8b1a8a5714001b2f6cd7f949d2f78047306 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Tue, 17 Nov 2020 13:54:52 +0100
Subject: [PATCH] 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
---
src/test/regress/expected/hash_func.out | 7 +
src/test/regress/expected/union.out | 357 +++++++++++++++++++++++-
src/test/regress/expected/with.out | 20 ++
src/test/regress/sql/hash_func.sql | 6 +
src/test/regress/sql/union.sql | 92 +++++-
src/test/regress/sql/with.sql | 18 ++
6 files changed, 498 insertions(+), 2 deletions(-)
diff --git a/src/test/regress/expected/hash_func.out b/src/test/regress/expected/hash_func.outindex e6e3410aaa..e7d615fde5 100644--- a/src/test/regress/expected/hash_func.out+++ b/src/test/regress/expected/hash_func.out@@ -177,6 +177,13 @@ WHERE hash_array(v)::bit(32) != hash_array_extended(v, 0)::bit(32)
-------+----------+-----------+-----------
(0 rows)
+-- array hashing with non-hashable element type+SELECT v as value, hash_array(v)::bit(32) as standard+FROM (VALUES ('{0}'::money[])) x(v);+ERROR: could not identify a hash function for type money+SELECT v as value, hash_array_extended(v, 0)::bit(32) as extended0+FROM (VALUES ('{0}'::money[])) x(v);+ERROR: could not identify an extended hash function for type money
SELECT v as value, hashbpchar(v)::bit(32) as standard,
hashbpcharextended(v, 0)::bit(32) as extended0,
hashbpcharextended(v, 1)::bit(32) as extended1
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.outindex 6e72e92d80..22e1ff5c42 100644--- a/src/test/regress/expected/union.out+++ b/src/test/regress/expected/union.out@@ -345,8 +345,28 @@ ERROR: FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
1 | 2 | 3
(1 row)
--- exercise both hashed and sorted implementations of INTERSECT/EXCEPT+-- exercise both hashed and sorted implementations of UNION/INTERSECT/EXCEPT
set enable_hashagg to on;
+explain (costs off)+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+ QUERY PLAN +----------------------------------------------------------------+ Aggregate+ -> HashAggregate+ Group Key: tenk1.unique1+ -> Append+ -> Index Only Scan using tenk1_unique1 on tenk1+ -> Seq Scan on tenk1 tenk1_1+(6 rows)++select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+ count +-------+ 10000+(1 row)+
explain (costs off)
select count(*) from
( 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;
(1 row)
set enable_hashagg to off;
+explain (costs off)+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+ QUERY PLAN +----------------------------------------------------------------------+ Aggregate+ -> Unique+ -> Sort+ Sort Key: tenk1.unique1+ -> Append+ -> Index Only Scan using tenk1_unique1 on tenk1+ -> Seq Scan on tenk1 tenk1_1+(7 rows)++select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+ count +-------+ 10000+(1 row)+
explain (costs off)
select count(*) from
( 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;
10
(1 row)
+reset enable_hashagg;+-- non-hashable type+set enable_hashagg to on;+explain (costs off)+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++set enable_hashagg to off;+explain (costs off)+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++reset enable_hashagg;+-- arrays+set enable_hashagg to on;+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------+ HashAggregate+ Group Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(5 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,4}+ {1,2}+ {1,3}+(3 rows)++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------------+ HashSetOp Intersect+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,2}+(1 row)++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------------+ HashSetOp Except+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,3}+(1 row)++-- non-hashable type+explain (costs off)+select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);+ x +-----------+ {$100.00}+ {$200.00}+ {$300.00}+(3 rows)++set enable_hashagg to off;+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,2}+ {1,3}+ {1,4}+(3 rows)++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Intersect+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,2}+(1 row)++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Except+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,3}+(1 row)++reset enable_hashagg;+-- records+set enable_hashagg to on;+-- currently no hashing support for record, so these will still run with sort plans:+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,2)+ (1,3)+ (1,4)+(3 rows)++explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Intersect+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,2)+(1 row)++explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Except+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,3)+(1 row)++-- non-hashable type+explain (costs off)+select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);+ x +-----------+ ($100.00)+ ($200.00)+ ($300.00)+(3 rows)++set enable_hashagg to off;+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,2)+ (1,3)+ (1,4)+(3 rows)++explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Intersect+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,2)+(1 row)++explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Except+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,3)+(1 row)+
reset enable_hashagg;
--
-- Mixed types
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.outindex 457f3bf04f..1f984a9fa4 100644--- a/src/test/regress/expected/with.out+++ b/src/test/regress/expected/with.out@@ -49,6 +49,15 @@ SELECT * FROM t;
5
(5 rows)
+-- UNION DISTINCT requires hashable type+WITH RECURSIVE t(n) AS (+ VALUES (1::money)+UNION+ SELECT n+1::money FROM t WHERE n < 100::money+)+SELECT sum(n) FROM t;+ERROR: could not implement recursive UNION+DETAIL: All column datatypes must be hashable.
-- recursive view
CREATE RECURSIVE VIEW nums (n) AS
VALUES (1)
@@ -616,6 +625,17 @@ select * from search_graph;
2 | 3 | arc 2 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
(25 rows)
+-- UNION DISTINCT currently not supported here because row types not hashable+with recursive search_graph(f, t, label, is_cycle, path) as (+ select *, false, array[row(g.f, g.t)] from graph g+ union distinct+ select g.*, row(g.f, g.t) = any(path), path || row(g.f, g.t)+ from graph g, search_graph sg+ where g.f = sg.t and not is_cycle+)+select * from search_graph;+ERROR: could not implement recursive UNION+DETAIL: All column datatypes must be hashable.
-- ordering by the path column has same effect as SEARCH DEPTH FIRST
with recursive search_graph(f, t, label, is_cycle, path) as (
select *, false, array[row(g.f, g.t)] from graph g
diff --git a/src/test/regress/sql/hash_func.sql b/src/test/regress/sql/hash_func.sqlindex a3e2decc2c..de84e68ba3 100644--- a/src/test/regress/sql/hash_func.sql+++ b/src/test/regress/sql/hash_func.sql@@ -130,6 +130,12 @@
WHERE hash_array(v)::bit(32) != hash_array_extended(v, 0)::bit(32)
OR hash_array(v)::bit(32) = hash_array_extended(v, 1)::bit(32);
+-- array hashing with non-hashable element type+SELECT v as value, hash_array(v)::bit(32) as standard+FROM (VALUES ('{0}'::money[])) x(v);+SELECT v as value, hash_array_extended(v, 0)::bit(32) as extended0+FROM (VALUES ('{0}'::money[])) x(v);+
SELECT v as value, hashbpchar(v)::bit(32) as standard,
hashbpcharextended(v, 0)::bit(32) as extended0,
hashbpcharextended(v, 1)::bit(32) as extended1
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sqlindex 5f4881d594..6cee454a4c 100644--- a/src/test/regress/sql/union.sql+++ b/src/test/regress/sql/union.sql@@ -118,10 +118,16 @@
(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
--- exercise both hashed and sorted implementations of INTERSECT/EXCEPT+-- exercise both hashed and sorted implementations of UNION/INTERSECT/EXCEPT
set enable_hashagg to on;
+explain (costs off)+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+
explain (costs off)
select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
@@ -134,6 +140,12 @@
set enable_hashagg to off;
+explain (costs off)+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+
explain (costs off)
select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
@@ -146,6 +158,84 @@
reset enable_hashagg;
+-- non-hashable type+set enable_hashagg to on;++explain (costs off)+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);++set enable_hashagg to off;++explain (costs off)+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);++reset enable_hashagg;++-- arrays+set enable_hashagg to on;++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);++-- non-hashable type+explain (costs off)+select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);+select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);++set enable_hashagg to off;++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);++reset enable_hashagg;++-- records+set enable_hashagg to on;++-- currently no hashing support for record, so these will still run with sort plans:+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);++-- non-hashable type+explain (costs off)+select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);+select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);++set enable_hashagg to off;++explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);++reset enable_hashagg;+
--
-- Mixed types
--
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sqlindex 2eea297a71..c6ce01a2d1 100644--- a/src/test/regress/sql/with.sql+++ b/src/test/regress/sql/with.sql@@ -31,6 +31,14 @@
)
SELECT * FROM t;
+-- UNION DISTINCT requires hashable type+WITH RECURSIVE t(n) AS (+ VALUES (1::money)+UNION+ SELECT n+1::money FROM t WHERE n < 100::money+)+SELECT sum(n) FROM t;+
-- recursive view
CREATE RECURSIVE VIEW nums (n) AS
VALUES (1)
@@ -317,6 +325,16 @@ CREATE TEMPORARY TABLE tree(
)
select * from search_graph;
+-- UNION DISTINCT currently not supported here because row types not hashable+with recursive search_graph(f, t, label, is_cycle, path) as (+ select *, false, array[row(g.f, g.t)] from graph g+ union distinct+ select g.*, row(g.f, g.t) = any(path), path || row(g.f, g.t)+ from graph g, search_graph sg+ where g.f = sg.t and not is_cycle+)+select * from search_graph;+
-- ordering by the path column has same effect as SEARCH DEPTH FIRST
with recursive search_graph(f, t, label, is_cycle, path) as (
select *, false, array[row(g.f, g.t)] from graph g
--
2.29.2
Attachments:
[text/plain] 0001-Add-more-tests-for-hashing-and-hash-based-plans.patch (23.7K, ../d1a898b2-cd3d-0519-def9-5f38163c68e8@2ndquadrant.com/2-0001-Add-more-tests-for-hashing-and-hash-based-plans.patch)
download | inline diff:
From dface8b1a8a5714001b2f6cd7f949d2f78047306 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Tue, 17 Nov 2020 13:54:52 +0100
Subject: [PATCH] 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
---
src/test/regress/expected/hash_func.out | 7 +
src/test/regress/expected/union.out | 357 +++++++++++++++++++++++-
src/test/regress/expected/with.out | 20 ++
src/test/regress/sql/hash_func.sql | 6 +
src/test/regress/sql/union.sql | 92 +++++-
src/test/regress/sql/with.sql | 18 ++
6 files changed, 498 insertions(+), 2 deletions(-)
diff --git a/src/test/regress/expected/hash_func.out b/src/test/regress/expected/hash_func.outindex e6e3410aaa..e7d615fde5 100644--- a/src/test/regress/expected/hash_func.out+++ b/src/test/regress/expected/hash_func.out@@ -177,6 +177,13 @@ WHERE hash_array(v)::bit(32) != hash_array_extended(v, 0)::bit(32)
-------+----------+-----------+-----------
(0 rows)
+-- array hashing with non-hashable element type+SELECT v as value, hash_array(v)::bit(32) as standard+FROM (VALUES ('{0}'::money[])) x(v);+ERROR: could not identify a hash function for type money+SELECT v as value, hash_array_extended(v, 0)::bit(32) as extended0+FROM (VALUES ('{0}'::money[])) x(v);+ERROR: could not identify an extended hash function for type money
SELECT v as value, hashbpchar(v)::bit(32) as standard,
hashbpcharextended(v, 0)::bit(32) as extended0,
hashbpcharextended(v, 1)::bit(32) as extended1
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.outindex 6e72e92d80..22e1ff5c42 100644--- a/src/test/regress/expected/union.out+++ b/src/test/regress/expected/union.out@@ -345,8 +345,28 @@ ERROR: FOR NO KEY UPDATE is not allowed with UNION/INTERSECT/EXCEPT
1 | 2 | 3
(1 row)
--- exercise both hashed and sorted implementations of INTERSECT/EXCEPT+-- exercise both hashed and sorted implementations of UNION/INTERSECT/EXCEPT
set enable_hashagg to on;
+explain (costs off)+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+ QUERY PLAN +----------------------------------------------------------------+ Aggregate+ -> HashAggregate+ Group Key: tenk1.unique1+ -> Append+ -> Index Only Scan using tenk1_unique1 on tenk1+ -> Seq Scan on tenk1 tenk1_1+(6 rows)++select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+ count +-------+ 10000+(1 row)+
explain (costs off)
select count(*) from
( 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;
(1 row)
set enable_hashagg to off;
+explain (costs off)+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+ QUERY PLAN +----------------------------------------------------------------------+ Aggregate+ -> Unique+ -> Sort+ Sort Key: tenk1.unique1+ -> Append+ -> Index Only Scan using tenk1_unique1 on tenk1+ -> Seq Scan on tenk1 tenk1_1+(7 rows)++select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+ count +-------+ 10000+(1 row)+
explain (costs off)
select count(*) from
( 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;
10
(1 row)
+reset enable_hashagg;+-- non-hashable type+set enable_hashagg to on;+explain (costs off)+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++set enable_hashagg to off;+explain (costs off)+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++reset enable_hashagg;+-- arrays+set enable_hashagg to on;+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------+ HashAggregate+ Group Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(5 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,4}+ {1,2}+ {1,3}+(3 rows)++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------------+ HashSetOp Intersect+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,2}+(1 row)++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------------+ HashSetOp Except+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,3}+(1 row)++-- non-hashable type+explain (costs off)+select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);+ x +-----------+ {$100.00}+ {$200.00}+ {$300.00}+(3 rows)++set enable_hashagg to off;+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,2}+ {1,3}+ {1,4}+(3 rows)++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Intersect+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,2}+(1 row)++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Except+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+ x +-------+ {1,3}+(1 row)++reset enable_hashagg;+-- records+set enable_hashagg to on;+-- currently no hashing support for record, so these will still run with sort plans:+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,2)+ (1,3)+ (1,4)+(3 rows)++explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Intersect+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,2)+(1 row)++explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Except+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,3)+(1 row)++-- non-hashable type+explain (costs off)+select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);+ x +-----------+ ($100.00)+ ($200.00)+ ($300.00)+(3 rows)++set enable_hashagg to off;+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------+ Unique+ -> Sort+ Sort Key: "*VALUES*".column1+ -> Append+ -> Values Scan on "*VALUES*"+ -> Values Scan on "*VALUES*_1"+(6 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,2)+ (1,3)+ (1,4)+(3 rows)++explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Intersect+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,2)+(1 row)++explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+ QUERY PLAN +-----------------------------------------------------+ SetOp Except+ -> Sort+ Sort Key: "*SELECT* 1".x+ -> Append+ -> Subquery Scan on "*SELECT* 1"+ -> Values Scan on "*VALUES*"+ -> Subquery Scan on "*SELECT* 2"+ -> Values Scan on "*VALUES*_1"+(8 rows)++select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+ x +-------+ (1,3)+(1 row)+
reset enable_hashagg;
--
-- Mixed types
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.outindex 457f3bf04f..1f984a9fa4 100644--- a/src/test/regress/expected/with.out+++ b/src/test/regress/expected/with.out@@ -49,6 +49,15 @@ SELECT * FROM t;
5
(5 rows)
+-- UNION DISTINCT requires hashable type+WITH RECURSIVE t(n) AS (+ VALUES (1::money)+UNION+ SELECT n+1::money FROM t WHERE n < 100::money+)+SELECT sum(n) FROM t;+ERROR: could not implement recursive UNION+DETAIL: All column datatypes must be hashable.
-- recursive view
CREATE RECURSIVE VIEW nums (n) AS
VALUES (1)
@@ -616,6 +625,17 @@ select * from search_graph;
2 | 3 | arc 2 -> 3 | f | {"(1,4)","(4,5)","(5,1)","(1,2)","(2,3)"}
(25 rows)
+-- UNION DISTINCT currently not supported here because row types not hashable+with recursive search_graph(f, t, label, is_cycle, path) as (+ select *, false, array[row(g.f, g.t)] from graph g+ union distinct+ select g.*, row(g.f, g.t) = any(path), path || row(g.f, g.t)+ from graph g, search_graph sg+ where g.f = sg.t and not is_cycle+)+select * from search_graph;+ERROR: could not implement recursive UNION+DETAIL: All column datatypes must be hashable.
-- ordering by the path column has same effect as SEARCH DEPTH FIRST
with recursive search_graph(f, t, label, is_cycle, path) as (
select *, false, array[row(g.f, g.t)] from graph g
diff --git a/src/test/regress/sql/hash_func.sql b/src/test/regress/sql/hash_func.sqlindex a3e2decc2c..de84e68ba3 100644--- a/src/test/regress/sql/hash_func.sql+++ b/src/test/regress/sql/hash_func.sql@@ -130,6 +130,12 @@
WHERE hash_array(v)::bit(32) != hash_array_extended(v, 0)::bit(32)
OR hash_array(v)::bit(32) = hash_array_extended(v, 1)::bit(32);
+-- array hashing with non-hashable element type+SELECT v as value, hash_array(v)::bit(32) as standard+FROM (VALUES ('{0}'::money[])) x(v);+SELECT v as value, hash_array_extended(v, 0)::bit(32) as extended0+FROM (VALUES ('{0}'::money[])) x(v);+
SELECT v as value, hashbpchar(v)::bit(32) as standard,
hashbpcharextended(v, 0)::bit(32) as extended0,
hashbpcharextended(v, 1)::bit(32) as extended1
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sqlindex 5f4881d594..6cee454a4c 100644--- a/src/test/regress/sql/union.sql+++ b/src/test/regress/sql/union.sql@@ -118,10 +118,16 @@
(SELECT 1,2,3 UNION SELECT 4,5,6) EXCEPT SELECT 4,5,6;
(SELECT 1,2,3 UNION SELECT 4,5,6 ORDER BY 1,2) EXCEPT SELECT 4,5,6;
--- exercise both hashed and sorted implementations of INTERSECT/EXCEPT+-- exercise both hashed and sorted implementations of UNION/INTERSECT/EXCEPT
set enable_hashagg to on;
+explain (costs off)+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+
explain (costs off)
select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
@@ -134,6 +140,12 @@
set enable_hashagg to off;
+explain (costs off)+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+select count(*) from+ ( select unique1 from tenk1 union select fivethous from tenk1 ) ss;+
explain (costs off)
select count(*) from
( select unique1 from tenk1 intersect select fivethous from tenk1 ) ss;
@@ -146,6 +158,84 @@
reset enable_hashagg;
+-- non-hashable type+set enable_hashagg to on;++explain (costs off)+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);++set enable_hashagg to off;++explain (costs off)+select x from (values (100::money), (200::money)) _(x) union select x from (values (100::money), (300::money)) _(x);++reset enable_hashagg;++-- arrays+set enable_hashagg to on;++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);++-- non-hashable type+explain (costs off)+select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);+select x from (values (array[100::money]), (array[200::money])) _(x) union select x from (values (array[100::money]), (array[300::money])) _(x);++set enable_hashagg to off;++explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) union select x from (values (array[1, 2]), (array[1, 4])) _(x);+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) intersect select x from (values (array[1, 2]), (array[1, 4])) _(x);+explain (costs off)+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);+select x from (values (array[1, 2]), (array[1, 3])) _(x) except select x from (values (array[1, 2]), (array[1, 4])) _(x);++reset enable_hashagg;++-- records+set enable_hashagg to on;++-- currently no hashing support for record, so these will still run with sort plans:+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);++-- non-hashable type+explain (costs off)+select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);+select x from (values (row(100::money)), (row(200::money))) _(x) union select x from (values (row(100::money)), (row(300::money))) _(x);++set enable_hashagg to off;++explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) union select x from (values (row(1, 2)), (row(1, 4))) _(x);+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) intersect select x from (values (row(1, 2)), (row(1, 4))) _(x);+explain (costs off)+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);+select x from (values (row(1, 2)), (row(1, 3))) _(x) except select x from (values (row(1, 2)), (row(1, 4))) _(x);++reset enable_hashagg;+
--
-- Mixed types
--
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sqlindex 2eea297a71..c6ce01a2d1 100644--- a/src/test/regress/sql/with.sql+++ b/src/test/regress/sql/with.sql@@ -31,6 +31,14 @@
)
SELECT * FROM t;
+-- UNION DISTINCT requires hashable type+WITH RECURSIVE t(n) AS (+ VALUES (1::money)+UNION+ SELECT n+1::money FROM t WHERE n < 100::money+)+SELECT sum(n) FROM t;+
-- recursive view
CREATE RECURSIVE VIEW nums (n) AS
VALUES (1)
@@ -317,6 +325,16 @@ CREATE TEMPORARY TABLE tree(
)
select * from search_graph;
+-- UNION DISTINCT currently not supported here because row types not hashable+with recursive search_graph(f, t, label, is_cycle, path) as (+ select *, false, array[row(g.f, g.t)] from graph g+ union distinct+ select g.*, row(g.f, g.t) = any(path), path || row(g.f, g.t)+ from graph g, search_graph sg+ where g.f = sg.t and not is_cycle+)+select * from search_graph;+
-- ordering by the path column has same effect as SEARCH DEPTH FIRST
with recursive search_graph(f, t, label, is_cycle, path) as (
select *, false, array[row(g.f, g.t)] from graph g
--
2.29.2
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: peter.eisentraut@2ndquadrant.com, tgl@sss.pgh.pa.us, andres@anarazel.de
Subject: Re: Hash support for row types
In-Reply-To: <d1a898b2-cd3d-0519-def9-5f38163c68e8@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox