From be3b265c94443b75262e89149f148ace0fdf75da Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 5 Aug 2008 02:43:18 +0000 Subject: Improve SELECT DISTINCT to consider hash aggregation, as well as sort/uniq, as methods for implementing the DISTINCT step. This eliminates the former performance gap between DISTINCT and GROUP BY, and also makes it possible to do SELECT DISTINCT on datatypes that only support hashing not sorting. SELECT DISTINCT ON is still always implemented by sorting; it would take executor changes to support hashing that, and it's not clear it's worth the trouble. This is a release-note-worthy incompatibility from previous PG versions, since SELECT DISTINCT can no longer be counted on to deliver sorted output without explicitly saying ORDER BY. (Anyone who can't cope with that can consider turning off enable_hashagg.) Several regression test queries needed to have ORDER BY added to preserve stable output order. I fixed the ones that manifested here, but there might be some other cases that show up on other platforms. --- src/test/regress/expected/numerology.out | 2 +- src/test/regress/expected/opr_sanity.out | 27 ++++++++++++++++++--------- src/test/regress/expected/select_distinct.out | 4 ++-- src/test/regress/input/misc.source | 3 ++- src/test/regress/output/misc.source | 3 ++- src/test/regress/sql/numerology.sql | 2 +- src/test/regress/sql/opr_sanity.sql | 27 ++++++++++++++++++--------- src/test/regress/sql/select_distinct.sql | 4 ++-- 8 files changed, 46 insertions(+), 26 deletions(-) (limited to 'src/test') diff --git a/src/test/regress/expected/numerology.out b/src/test/regress/expected/numerology.out index c5ad36fdd32..d404d9db681 100644 --- a/src/test/regress/expected/numerology.out +++ b/src/test/regress/expected/numerology.out @@ -79,7 +79,7 @@ INSERT INTO TEMP_GROUP INSERT INTO TEMP_GROUP SELECT 2, i.f1, f.f1 FROM INT4_TBL i, FLOAT8_TBL f; -SELECT DISTINCT f1 AS two FROM TEMP_GROUP; +SELECT DISTINCT f1 AS two FROM TEMP_GROUP ORDER BY 1; two ----- 1 diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 69f0efb8d42..533bac3ab67 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -129,7 +129,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.prorettype < p2.prorettype); + (p1.prorettype < p2.prorettype) +ORDER BY 1, 2; prorettype | prorettype ------------+------------ 25 | 1043 @@ -142,7 +143,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[0] < p2.proargtypes[0]); + (p1.proargtypes[0] < p2.proargtypes[0]) +ORDER BY 1, 2; proargtypes | proargtypes -------------+------------- 25 | 1042 @@ -158,7 +160,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[1] < p2.proargtypes[1]); + (p1.proargtypes[1] < p2.proargtypes[1]) +ORDER BY 1, 2; proargtypes | proargtypes -------------+------------- 23 | 28 @@ -173,7 +176,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[2] < p2.proargtypes[2]); + (p1.proargtypes[2] < p2.proargtypes[2]) +ORDER BY 1, 2; proargtypes | proargtypes -------------+------------- 1114 | 1184 @@ -185,7 +189,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[3] < p2.proargtypes[3]); + (p1.proargtypes[3] < p2.proargtypes[3]) +ORDER BY 1, 2; proargtypes | proargtypes -------------+------------- 1114 | 1184 @@ -197,7 +202,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[4] < p2.proargtypes[4]); + (p1.proargtypes[4] < p2.proargtypes[4]) +ORDER BY 1, 2; proargtypes | proargtypes -------------+------------- (0 rows) @@ -208,7 +214,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[5] < p2.proargtypes[5]); + (p1.proargtypes[5] < p2.proargtypes[5]) +ORDER BY 1, 2; proargtypes | proargtypes -------------+------------- (0 rows) @@ -219,7 +226,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[6] < p2.proargtypes[6]); + (p1.proargtypes[6] < p2.proargtypes[6]) +ORDER BY 1, 2; proargtypes | proargtypes -------------+------------- (0 rows) @@ -230,7 +238,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[7] < p2.proargtypes[7]); + (p1.proargtypes[7] < p2.proargtypes[7]) +ORDER BY 1, 2; proargtypes | proargtypes -------------+------------- (0 rows) diff --git a/src/test/regress/expected/select_distinct.out b/src/test/regress/expected/select_distinct.out index abe34ae7ae5..fe64fe0d9c1 100644 --- a/src/test/regress/expected/select_distinct.out +++ b/src/test/regress/expected/select_distinct.out @@ -14,7 +14,7 @@ SELECT DISTINCT two FROM tmp; -- -- awk '{print $5;}' onek.data | sort -n | uniq -- -SELECT DISTINCT ten FROM tmp; +SELECT DISTINCT ten FROM tmp ORDER BY 1; ten ----- 0 @@ -32,7 +32,7 @@ SELECT DISTINCT ten FROM tmp; -- -- awk '{print $16;}' onek.data | sort -d | uniq -- -SELECT DISTINCT string4 FROM tmp; +SELECT DISTINCT string4 FROM tmp ORDER BY 1; string4 --------- AAAAxx diff --git a/src/test/regress/input/misc.source b/src/test/regress/input/misc.source index c82b1405978..c5813630625 100644 --- a/src/test/regress/input/misc.source +++ b/src/test/regress/input/misc.source @@ -183,7 +183,8 @@ SELECT p.name, name(p.hobbies) FROM person* p; -- the next two queries demonstrate how functions generate bogus duplicates. -- this is a "feature" .. -- -SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r; +SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r + ORDER BY 1,2; SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r; diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source index e409c0a1001..91e0d2b04d7 100644 --- a/src/test/regress/output/misc.source +++ b/src/test/regress/output/misc.source @@ -469,7 +469,8 @@ SELECT p.name, name(p.hobbies) FROM person* p; -- the next two queries demonstrate how functions generate bogus duplicates. -- this is a "feature" .. -- -SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r; +SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r + ORDER BY 1,2; name | name -------------+--------------- basketball | hightops diff --git a/src/test/regress/sql/numerology.sql b/src/test/regress/sql/numerology.sql index 2220fdba385..6626cf20ebc 100644 --- a/src/test/regress/sql/numerology.sql +++ b/src/test/regress/sql/numerology.sql @@ -63,7 +63,7 @@ INSERT INTO TEMP_GROUP SELECT 2, i.f1, f.f1 FROM INT4_TBL i, FLOAT8_TBL f; -SELECT DISTINCT f1 AS two FROM TEMP_GROUP; +SELECT DISTINCT f1 AS two FROM TEMP_GROUP ORDER BY 1; SELECT f1 AS two, max(f3) AS max_float, min(f3) as min_float FROM TEMP_GROUP diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index e2ab6e57d4a..5017849830a 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -121,7 +121,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.prorettype < p2.prorettype); + (p1.prorettype < p2.prorettype) +ORDER BY 1, 2; SELECT DISTINCT p1.proargtypes[0], p2.proargtypes[0] FROM pg_proc AS p1, pg_proc AS p2 @@ -129,7 +130,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[0] < p2.proargtypes[0]); + (p1.proargtypes[0] < p2.proargtypes[0]) +ORDER BY 1, 2; SELECT DISTINCT p1.proargtypes[1], p2.proargtypes[1] FROM pg_proc AS p1, pg_proc AS p2 @@ -137,7 +139,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[1] < p2.proargtypes[1]); + (p1.proargtypes[1] < p2.proargtypes[1]) +ORDER BY 1, 2; SELECT DISTINCT p1.proargtypes[2], p2.proargtypes[2] FROM pg_proc AS p1, pg_proc AS p2 @@ -145,7 +148,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[2] < p2.proargtypes[2]); + (p1.proargtypes[2] < p2.proargtypes[2]) +ORDER BY 1, 2; SELECT DISTINCT p1.proargtypes[3], p2.proargtypes[3] FROM pg_proc AS p1, pg_proc AS p2 @@ -153,7 +157,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[3] < p2.proargtypes[3]); + (p1.proargtypes[3] < p2.proargtypes[3]) +ORDER BY 1, 2; SELECT DISTINCT p1.proargtypes[4], p2.proargtypes[4] FROM pg_proc AS p1, pg_proc AS p2 @@ -161,7 +166,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[4] < p2.proargtypes[4]); + (p1.proargtypes[4] < p2.proargtypes[4]) +ORDER BY 1, 2; SELECT DISTINCT p1.proargtypes[5], p2.proargtypes[5] FROM pg_proc AS p1, pg_proc AS p2 @@ -169,7 +175,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[5] < p2.proargtypes[5]); + (p1.proargtypes[5] < p2.proargtypes[5]) +ORDER BY 1, 2; SELECT DISTINCT p1.proargtypes[6], p2.proargtypes[6] FROM pg_proc AS p1, pg_proc AS p2 @@ -177,7 +184,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[6] < p2.proargtypes[6]); + (p1.proargtypes[6] < p2.proargtypes[6]) +ORDER BY 1, 2; SELECT DISTINCT p1.proargtypes[7], p2.proargtypes[7] FROM pg_proc AS p1, pg_proc AS p2 @@ -185,7 +193,8 @@ WHERE p1.oid != p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND NOT p1.proisagg AND NOT p2.proisagg AND - (p1.proargtypes[7] < p2.proargtypes[7]); + (p1.proargtypes[7] < p2.proargtypes[7]) +ORDER BY 1, 2; -- Look for functions that return type "internal" and do not have any -- "internal" argument. Such a function would be a security hole since diff --git a/src/test/regress/sql/select_distinct.sql b/src/test/regress/sql/select_distinct.sql index c4a63aaf16f..7416e0194e1 100644 --- a/src/test/regress/sql/select_distinct.sql +++ b/src/test/regress/sql/select_distinct.sql @@ -10,12 +10,12 @@ SELECT DISTINCT two FROM tmp; -- -- awk '{print $5;}' onek.data | sort -n | uniq -- -SELECT DISTINCT ten FROM tmp; +SELECT DISTINCT ten FROM tmp ORDER BY 1; -- -- awk '{print $16;}' onek.data | sort -d | uniq -- -SELECT DISTINCT string4 FROM tmp; +SELECT DISTINCT string4 FROM tmp ORDER BY 1; -- -- awk '{print $3,$16,$5;}' onek.data | sort -d | uniq | -- cgit v1.2.3