Fixing some ancient errors in hash join costing - Mailing list pgsql-hackers
| From | Tom Lane |
|---|---|
| Subject | Fixing some ancient errors in hash join costing |
| Date | |
| Msg-id | 2380165.1766871097@sss.pgh.pa.us Whole thread Raw |
| List | pgsql-hackers |
I spent some time digging into bug #19363 [1], and figured out the
reason why the planner is failing to reject a horribly bad plan.
Even without any stats, it should be able to figure out that building
a hash table estimated at 10 billion rows is less good than building
one estimated at 1000 rows ... but it didn't. The cause is
(1) estimate_hash_bucket_stats is defined to return zero to *mcv_freq
if it cannot obtain a value for the frequency of the most common
value.
(2) final_cost_hashjoin neglected this specification, and would
blindly adopt zero for the innermcvfreq.
(3) This results in calculating zero for the largest hash bucket size,
and thus the code that intends to disable hashjoin when that bucket
size exceeds get_hash_memory_limit() is turned into a no-op.
This is the exact opposite of what we want, I think. The intent in
the planner has always been to avoid hashing unless we are pretty
confident that the inner relation's hash key is well-distributed.
Turning off the disable-for-giant-hash-table check when we have
no stats is the polar opposite of sane.
So I said to myself "this is a one-liner fix, we just have to
disregard any mcv_freq reported as zero". And that did fix the
case shown in the bug report, but it also broke a bunch of
regression test cases. Upon closer investigation, there is also
an old oversight within estimate_hash_bucket_stats itself. It
returns zero for mcv_freq if there's no STATISTIC_KIND_MCV entry,
but that neglects the fact that ANALYZE does not make an MCV entry
if it doesn't find any data values that are noticeably more common
than any others. So the correct behavior really should be to
assume the column is unique and set the mcv_freq to 1 / rows.
In the attached draft patch I made it do this if there's no MCV
stats entry but there is a STATISTIC_KIND_HISTOGRAM entry.
If there's neither, we are probably dealing with a weird datatype
that doesn't have meaningful scalar stats, so I'm hesitant to
just apply the 1 / rows rule blindly.
Even after that, there were more changes in regression test outputs
than I'd expected. Poking into it further, the first diff in join.out
is precisely a case like the bug report's, where we have no stats
about a potentially large self-join. The repaired code decides that a
hash join is too risky, so it disables it and we select a merge join
instead, as desired. However, none of the other places that visibly
change plans are triggering that disable logic. Instead what is
happening is that the improved mcv_freq estimate is getting used
within estimate_hash_bucket_stats to refine its bucket-size result:
/*
* Adjust estimated bucketsize upward to account for skewed distribution.
*/
if (avgfreq > 0.0 && *mcv_freq > avgfreq)
estfract *= *mcv_freq / avgfreq;
This code does nothing if *mcv_freq is zero, but if we have a
more-than-zero estimate it can increase the bucket size fraction,
and that is indeed happening in the other places in the core
regression tests where we see plans change. AFAICT these changes
are all perfectly sane and not anything to worry about.
I also had to stick an additional ANALYZE step into join_hash.sql
to keep plans from changing there.
I remain a bit confused by the change in postgres_fdw.out though.
It's deciding to push an ORDER BY down to the remote side when
it didn't before, which seems like an improvement; but I fail to
see how a marginal change in hash join costing would lead to that.
Perhaps that is worth looking into more closely.
regards, tom lane
[1] https://www.postgresql.org/message-id/19363-8dd32fc7600a1153%40postgresql.org
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 48e3185b227..a01aedc84c2 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -4216,16 +4216,13 @@ RESET enable_sort;
-- subquery using immutable function (can be sent to remote)
PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND
date(c5)= '1970-01-17'::date) ORDER BY c1;
EXPLAIN (VERBOSE, COSTS OFF) EXECUTE st3(10, 20);
-
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
- Sort
+
QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
- Sort Key: t1.c1
- -> Foreign Scan
- Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
- Relations: (public.ft1 t1) SEMI JOIN (public.ft2 t2)
- Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE
((r1."C1" < 20)) AND EXISTS (SELECT NULL FROM "S 1"."T 1" r3 WHERE ((r3."C 1" > 10)) AND ((date(r3.c5) =
'1970-01-17'::date))AND ((r3.c3 = r1.c3)))
-(7 rows)
+ Relations: (public.ft1 t1) SEMI JOIN (public.ft2 t2)
+ Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8 FROM "S 1"."T 1" r1 WHERE ((r1."C 1" <
20))AND EXISTS (SELECT NULL FROM "S 1"."T 1" r3 WHERE ((r3."C 1" > 10)) AND ((date(r3.c5) = '1970-01-17'::date)) AND
((r3.c3= r1.c3))) ORDER BY r1."C 1" ASC NULLS LAST
+(4 rows)
EXECUTE st3(10, 20);
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index a39cc793b4d..822c696572b 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -4360,7 +4360,7 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path,
path->jpath.jointype))
{
innerbucketsize = 1.0 / virtualbuckets;
- innermcvfreq = 0.0;
+ innermcvfreq = 1.0 / inner_path_rows_total;
}
else
{
@@ -4428,7 +4428,8 @@ final_cost_hashjoin(PlannerInfo *root, HashPath *path,
if (innerbucketsize > thisbucketsize)
innerbucketsize = thisbucketsize;
- if (innermcvfreq > thismcvfreq)
+ /* Disregard zero for MCV freq, it means we have no data */
+ if (innermcvfreq > thismcvfreq && thismcvfreq > 0.0)
innermcvfreq = thismcvfreq;
}
}
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index c760b19db55..c0dc26b4647 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -4414,6 +4414,18 @@ estimate_hash_bucket_stats(PlannerInfo *root, Node *hashkey, double nbuckets,
*mcv_freq = sslot.numbers[0];
free_attstatsslot(&sslot);
}
+ else if (get_attstatsslot(&sslot, vardata.statsTuple,
+ STATISTIC_KIND_HISTOGRAM, InvalidOid,
+ 0))
+ {
+ /*
+ * If there are no recorded MCVs, but we do have a histogram, then
+ * assume that ANALYZE determined that the column is unique.
+ */
+ if (vardata.rel && vardata.rel->rows > 0)
+ *mcv_freq = 1.0 / vardata.rel->rows;
+ free_attstatsslot(&sslot);
+ }
}
/* Get number of distinct values */
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index edde9e99893..bf11d48bb3a 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3303,10 +3303,13 @@ where not exists (
);
QUERY PLAN
---------------------------------------------------------
- Hash Anti Join
- Hash Cond: (t1.c1 = t2.c2)
- -> Seq Scan on tt4x t1
- -> Hash
+ Merge Anti Join
+ Merge Cond: (t1.c1 = t2.c2)
+ -> Sort
+ Sort Key: t1.c1
+ -> Seq Scan on tt4x t1
+ -> Sort
+ Sort Key: t2.c2
-> Merge Right Join
Merge Cond: (t5.c1 = t3.c2)
-> Merge Join
@@ -3327,7 +3330,7 @@ where not exists (
-> Sort
Sort Key: t3.c1
-> Seq Scan on tt4x t3
-(24 rows)
+(27 rows)
--
-- regression test for problems of the sort depicted in bug #3494
@@ -9313,19 +9316,19 @@ select * from fkest f1
join fkest f2 on (f1.x = f2.x and f1.x10 = f2.x10b and f1.x100 = f2.x100)
join fkest f3 on f1.x = f3.x
where f1.x100 = 2;
- QUERY PLAN
------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Hash Join
- Hash Cond: ((f2.x = f1.x) AND (f2.x10b = f1.x10))
+ Hash Cond: (f1.x = f3.x)
-> Hash Join
- Hash Cond: (f3.x = f2.x)
- -> Seq Scan on fkest f3
+ Hash Cond: ((f2.x = f1.x) AND (f2.x10b = f1.x10))
+ -> Seq Scan on fkest f2
+ Filter: (x100 = 2)
-> Hash
- -> Seq Scan on fkest f2
+ -> Seq Scan on fkest f1
Filter: (x100 = 2)
-> Hash
- -> Seq Scan on fkest f1
- Filter: (x100 = 2)
+ -> Seq Scan on fkest f3
(11 rows)
rollback;
diff --git a/src/test/regress/expected/join_hash.out b/src/test/regress/expected/join_hash.out
index a45e1450040..4749f6ed70d 100644
--- a/src/test/regress/expected/join_hash.out
+++ b/src/test/regress/expected/join_hash.out
@@ -559,6 +559,7 @@ create table join_foo as select generate_series(1, 3) as id, 'xxxxx'::text as t;
alter table join_foo set (parallel_workers = 0);
create table join_bar as select generate_series(1, 10000) as id, 'xxxxx'::text as t;
alter table join_bar set (parallel_workers = 2);
+analyze join_foo, join_bar;
-- multi-batch with rescan, parallel-oblivious
savepoint settings;
set enable_parallel_hash = off;
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 17d27ef3d46..559b87ec66e 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -158,20 +158,20 @@ SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER
--------------------------------------------------
Sort
Sort Key: t1.a, t2.b
- -> Hash Right Join
- Hash Cond: (t2.b = t1.a)
+ -> Hash Left Join
+ Hash Cond: (t1.a = t2.b)
-> Append
- -> Seq Scan on prt2_p1 t2_1
- -> Seq Scan on prt2_p2 t2_2
- -> Seq Scan on prt2_p3 t2_3
+ -> Seq Scan on prt1_p1 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on prt1_p3 t1_3
+ Filter: (b = 0)
-> Hash
-> Append
- -> Seq Scan on prt1_p1 t1_1
- Filter: (b = 0)
- -> Seq Scan on prt1_p2 t1_2
- Filter: (b = 0)
- -> Seq Scan on prt1_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt2_p1 t2_1
+ -> Seq Scan on prt2_p2 t2_2
+ -> Seq Scan on prt2_p3 t2_3
(16 rows)
SELECT t1, t2 FROM prt1 t1 LEFT JOIN prt2 t2 ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
@@ -297,23 +297,23 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1 t1, prt2 t2 WHERE t1.a = t2.b AND t1.a <
-- Currently we can't do partitioned join if nullable-side partitions are pruned
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250)
t2ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------
Sort
Sort Key: prt1.a, prt2.b
- -> Hash Right Join
- Hash Cond: (prt2.b = prt1.a)
+ -> Hash Left Join
+ Hash Cond: (prt1.a = prt2.b)
-> Append
- -> Seq Scan on prt2_p2 prt2_1
- Filter: (b > 250)
- -> Seq Scan on prt2_p3 prt2_2
- Filter: (b > 250)
+ -> Seq Scan on prt1_p1 prt1_1
+ Filter: ((a < 450) AND (b = 0))
+ -> Seq Scan on prt1_p2 prt1_2
+ Filter: ((a < 450) AND (b = 0))
-> Hash
-> Append
- -> Seq Scan on prt1_p1 prt1_1
- Filter: ((a < 450) AND (b = 0))
- -> Seq Scan on prt1_p2 prt1_2
- Filter: ((a < 450) AND (b = 0))
+ -> Seq Scan on prt2_p2 prt2_1
+ Filter: (b > 250)
+ -> Seq Scan on prt2_p3 prt2_2
+ Filter: (b > 250)
(15 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 LEFT JOIN (SELECT * FROM prt2 WHERE b > 250)
t2ON t1.a = t2.b WHERE t1.b = 0 ORDER BY t1.a, t2.b;
@@ -778,23 +778,23 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_e t1, prt2_e t2 WHERE (t1.a + t1.b)/2 =
Sort Key: t1.a, t2.b
-> Append
-> Hash Join
- Hash Cond: (((t2_1.b + t2_1.a) / 2) = ((t1_1.a + t1_1.b) / 2))
- -> Seq Scan on prt2_e_p1 t2_1
+ Hash Cond: (((t1_1.a + t1_1.b) / 2) = ((t2_1.b + t2_1.a) / 2))
+ -> Seq Scan on prt1_e_p1 t1_1
+ Filter: (c = 0)
-> Hash
- -> Seq Scan on prt1_e_p1 t1_1
- Filter: (c = 0)
+ -> Seq Scan on prt2_e_p1 t2_1
-> Hash Join
- Hash Cond: (((t2_2.b + t2_2.a) / 2) = ((t1_2.a + t1_2.b) / 2))
- -> Seq Scan on prt2_e_p2 t2_2
+ Hash Cond: (((t1_2.a + t1_2.b) / 2) = ((t2_2.b + t2_2.a) / 2))
+ -> Seq Scan on prt1_e_p2 t1_2
+ Filter: (c = 0)
-> Hash
- -> Seq Scan on prt1_e_p2 t1_2
- Filter: (c = 0)
+ -> Seq Scan on prt2_e_p2 t2_2
-> Hash Join
- Hash Cond: (((t2_3.b + t2_3.a) / 2) = ((t1_3.a + t1_3.b) / 2))
- -> Seq Scan on prt2_e_p3 t2_3
+ Hash Cond: (((t1_3.a + t1_3.b) / 2) = ((t2_3.b + t2_3.a) / 2))
+ -> Seq Scan on prt1_e_p3 t1_3
+ Filter: (c = 0)
-> Hash
- -> Seq Scan on prt1_e_p3 t1_3
- Filter: (c = 0)
+ -> Seq Scan on prt2_e_p3 t2_3
(21 rows)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_e t1, prt2_e t2 WHERE (t1.a + t1.b)/2 = (t2.b + t2.a)/2 AND t1.c = 0 ORDER BY
t1.a,t2.b;
@@ -864,26 +864,26 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
Sort
Sort Key: t1.a, t2.b, ((t3.a + t3.b))
-> Append
- -> Hash Right Join
- Hash Cond: (((t3_1.a + t3_1.b) / 2) = t1_1.a)
- -> Seq Scan on prt1_e_p1 t3_1
+ -> Hash Left Join
+ Hash Cond: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
+ -> Hash Right Join
+ Hash Cond: (t2_1.b = t1_1.a)
+ -> Seq Scan on prt2_p1 t2_1
+ -> Hash
+ -> Seq Scan on prt1_p1 t1_1
+ Filter: (b = 0)
-> Hash
- -> Hash Right Join
- Hash Cond: (t2_1.b = t1_1.a)
- -> Seq Scan on prt2_p1 t2_1
- -> Hash
- -> Seq Scan on prt1_p1 t1_1
- Filter: (b = 0)
- -> Hash Right Join
- Hash Cond: (((t3_2.a + t3_2.b) / 2) = t1_2.a)
- -> Seq Scan on prt1_e_p2 t3_2
+ -> Seq Scan on prt1_e_p1 t3_1
+ -> Hash Left Join
+ Hash Cond: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
+ -> Hash Right Join
+ Hash Cond: (t2_2.b = t1_2.a)
+ -> Seq Scan on prt2_p2 t2_2
+ -> Hash
+ -> Seq Scan on prt1_p2 t1_2
+ Filter: (b = 0)
-> Hash
- -> Hash Right Join
- Hash Cond: (t2_2.b = t1_2.a)
- -> Seq Scan on prt2_p2 t2_2
- -> Hash
- -> Seq Scan on prt1_p2 t1_2
- Filter: (b = 0)
+ -> Seq Scan on prt1_e_p2 t3_2
-> Hash Right Join
Hash Cond: (((t3_3.a + t3_3.b) / 2) = t1_3.a)
-> Seq Scan on prt1_e_p3 t3_3
@@ -921,21 +921,21 @@ SELECT t1.a, t1.c, t2.b, t2.c, t3.a + t3.b, t3.c FROM (prt1 t1 LEFT JOIN prt2 t2
Sort Key: t1.a, t2.b, ((t3.a + t3.b))
-> Append
-> Nested Loop Left Join
- -> Hash Right Join
- Hash Cond: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
- -> Seq Scan on prt1_p1 t1_1
+ -> Hash Left Join
+ Hash Cond: (((t3_1.a + t3_1.b) / 2) = t1_1.a)
+ -> Seq Scan on prt1_e_p1 t3_1
+ Filter: (c = 0)
-> Hash
- -> Seq Scan on prt1_e_p1 t3_1
- Filter: (c = 0)
+ -> Seq Scan on prt1_p1 t1_1
-> Index Scan using iprt2_p1_b on prt2_p1 t2_1
Index Cond: (b = t1_1.a)
-> Nested Loop Left Join
- -> Hash Right Join
- Hash Cond: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
- -> Seq Scan on prt1_p2 t1_2
+ -> Hash Left Join
+ Hash Cond: (((t3_2.a + t3_2.b) / 2) = t1_2.a)
+ -> Seq Scan on prt1_e_p2 t3_2
+ Filter: (c = 0)
-> Hash
- -> Seq Scan on prt1_e_p2 t3_2
- Filter: (c = 0)
+ -> Seq Scan on prt1_p2 t1_2
-> Index Scan using iprt2_p2_b on prt2_p2 t2_2
Index Cond: (b = t1_2.a)
-> Nested Loop Left Join
@@ -1080,14 +1080,14 @@ SELECT COUNT(*) FROM prt1 FULL JOIN prt2 p2(b,a,c) USING(a,b) FULL JOIN prt2 p3(
-- make sure these go to null as expected
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL
JOIN(SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b)) FULL JOIN (SELECT 50 phv, * FROM prt1_e WHERE
prt1_e.c= 0) t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.a = t1.phv OR t2.b = t2.phv OR (t3.a + t3.b)/2 = t3.phv ORDER BY
t1.a,t2.b, t3.a + t3.b;
- QUERY PLAN
-------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
Sort
Sort Key: prt1.a, prt2.b, ((prt1_e.a + prt1_e.b))
- -> Append
- -> Hash Full Join
- Hash Cond: (prt1_1.a = ((prt1_e_1.a + prt1_e_1.b) / 2))
- Filter: ((prt1_1.a = (50)) OR (prt2_1.b = (75)) OR (((prt1_e_1.a + prt1_e_1.b) / 2) = (50)))
+ -> Hash Full Join
+ Hash Cond: (prt1.a = ((prt1_e.a + prt1_e.b) / 2))
+ Filter: ((prt1.a = (50)) OR (prt2.b = (75)) OR (((prt1_e.a + prt1_e.b) / 2) = (50)))
+ -> Append
-> Hash Full Join
Hash Cond: (prt1_1.a = prt2_1.b)
-> Seq Scan on prt1_p1 prt1_1
@@ -1095,12 +1095,6 @@ SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * F
-> Hash
-> Seq Scan on prt2_p1 prt2_1
Filter: (a = 0)
- -> Hash
- -> Seq Scan on prt1_e_p1 prt1_e_1
- Filter: (c = 0)
- -> Hash Full Join
- Hash Cond: (prt1_2.a = ((prt1_e_2.a + prt1_e_2.b) / 2))
- Filter: ((prt1_2.a = (50)) OR (prt2_2.b = (75)) OR (((prt1_e_2.a + prt1_e_2.b) / 2) = (50)))
-> Hash Full Join
Hash Cond: (prt1_2.a = prt2_2.b)
-> Seq Scan on prt1_p2 prt1_2
@@ -1108,12 +1102,6 @@ SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * F
-> Hash
-> Seq Scan on prt2_p2 prt2_2
Filter: (a = 0)
- -> Hash
- -> Seq Scan on prt1_e_p2 prt1_e_2
- Filter: (c = 0)
- -> Hash Full Join
- Hash Cond: (prt1_3.a = ((prt1_e_3.a + prt1_e_3.b) / 2))
- Filter: ((prt1_3.a = (50)) OR (prt2_3.b = (75)) OR (((prt1_e_3.a + prt1_e_3.b) / 2) = (50)))
-> Hash Full Join
Hash Cond: (prt1_3.a = prt2_3.b)
-> Seq Scan on prt1_p3 prt1_3
@@ -1121,10 +1109,15 @@ SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * F
-> Hash
-> Seq Scan on prt2_p3 prt2_3
Filter: (a = 0)
- -> Hash
+ -> Hash
+ -> Append
+ -> Seq Scan on prt1_e_p1 prt1_e_1
+ Filter: (c = 0)
+ -> Seq Scan on prt1_e_p2 prt1_e_2
+ Filter: (c = 0)
-> Seq Scan on prt1_e_p3 prt1_e_3
Filter: (c = 0)
-(42 rows)
+(35 rows)
SELECT t1.a, t1.phv, t2.b, t2.phv, t3.a + t3.b, t3.phv FROM ((SELECT 50 phv, * FROM prt1 WHERE prt1.b = 0) t1 FULL
JOIN(SELECT 75 phv, * FROM prt2 WHERE prt2.a = 0) t2 ON (t1.a = t2.b)) FULL JOIN (SELECT 50 phv, * FROM prt1_e WHERE
prt1_e.c= 0) t3 ON (t1.a = (t3.a + t3.b)/2) WHERE t1.a = t1.phv OR t2.b = t2.phv OR (t3.a + t3.b)/2 = t3.phv ORDER BY
t1.a,t2.b, t3.a + t3.b;
a | phv | b | phv | ?column? | phv
@@ -1146,11 +1139,11 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER
-> Sort
Sort Key: t1_5.b
-> Hash Join
- Hash Cond: (((t2_1.a + t2_1.b) / 2) = t1_5.b)
- -> Seq Scan on prt1_e_p1 t2_1
+ Hash Cond: (t1_5.b = ((t2_1.a + t2_1.b) / 2))
+ -> Seq Scan on prt2_p1 t1_5
+ Filter: (a = 0)
-> Hash
- -> Seq Scan on prt2_p1 t1_5
- Filter: (a = 0)
+ -> Seq Scan on prt1_e_p1 t2_1
-> Index Scan using iprt1_p1_a on prt1_p1 t1_2
Index Cond: (a = ((t2_1.a + t2_1.b) / 2))
Filter: (b = 0)
@@ -1160,11 +1153,11 @@ SELECT t1.* FROM prt1 t1 WHERE t1.a IN (SELECT t1.b FROM prt2 t1, prt1_e t2 WHER
-> Sort
Sort Key: t1_6.b
-> Hash Join
- Hash Cond: (((t2_2.a + t2_2.b) / 2) = t1_6.b)
- -> Seq Scan on prt1_e_p2 t2_2
+ Hash Cond: (t1_6.b = ((t2_2.a + t2_2.b) / 2))
+ -> Seq Scan on prt2_p2 t1_6
+ Filter: (a = 0)
-> Hash
- -> Seq Scan on prt2_p2 t1_6
- Filter: (a = 0)
+ -> Seq Scan on prt1_e_p2 t2_2
-> Index Scan using iprt1_p2_a on prt1_p2 t1_3
Index Cond: (a = ((t2_2.a + t2_2.b) / 2))
Filter: (b = 0)
@@ -1944,12 +1937,12 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_l t1 LEFT JOIN prt2_l t2 ON t1.a = t2.b
Sort
Sort Key: t1.a, t2.b
-> Append
- -> Hash Right Join
- Hash Cond: ((t2_1.b = t1_1.a) AND ((t2_1.c)::text = (t1_1.c)::text))
- -> Seq Scan on prt2_l_p1 t2_1
+ -> Hash Left Join
+ Hash Cond: ((t1_1.a = t2_1.b) AND ((t1_1.c)::text = (t2_1.c)::text))
+ -> Seq Scan on prt1_l_p1 t1_1
+ Filter: (b = 0)
-> Hash
- -> Seq Scan on prt1_l_p1 t1_1
- Filter: (b = 0)
+ -> Seq Scan on prt2_l_p1 t2_1
-> Hash Right Join
Hash Cond: ((t2_2.b = t1_2.a) AND ((t2_2.c)::text = (t1_2.c)::text))
-> Seq Scan on prt2_l_p2_p1 t2_2
@@ -2968,26 +2961,26 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 LEFT JOIN prt2_adv t2 ON (t1.a =
-- partitions on the nullable side
EXPLAIN (COSTS OFF)
SELECT t1.b, t1.c, t2.a, t2.c FROM prt2_adv t1 LEFT JOIN prt1_adv t2 ON (t1.b = t2.a) WHERE t1.a = 0 ORDER BY t1.b,
t2.a;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Sort
Sort Key: t1.b, t2.a
- -> Hash Right Join
- Hash Cond: (t2.a = t1.b)
+ -> Hash Left Join
+ Hash Cond: (t1.b = t2.a)
-> Append
- -> Seq Scan on prt1_adv_p1 t2_1
- -> Seq Scan on prt1_adv_p2 t2_2
- -> Seq Scan on prt1_adv_p3 t2_3
+ -> Seq Scan on prt2_adv_p1 t1_1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_p2 t1_2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_p3 t1_3
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_extra t1_4
+ Filter: (a = 0)
-> Hash
-> Append
- -> Seq Scan on prt2_adv_p1 t1_1
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_p2 t1_2
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_p3 t1_3
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_extra t1_4
- Filter: (a = 0)
+ -> Seq Scan on prt1_adv_p1 t2_1
+ -> Seq Scan on prt1_adv_p2 t2_2
+ -> Seq Scan on prt1_adv_p3 t2_3
(18 rows)
-- anti join
@@ -3031,26 +3024,26 @@ SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t
-- partitions on the nullable side
EXPLAIN (COSTS OFF)
SELECT t1.* FROM prt2_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt1_adv t2 WHERE t1.b = t2.a) AND t1.a = 0 ORDER BY
t1.b;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Sort
Sort Key: t1.b
- -> Hash Right Anti Join
- Hash Cond: (t2.a = t1.b)
+ -> Hash Anti Join
+ Hash Cond: (t1.b = t2.a)
-> Append
- -> Seq Scan on prt1_adv_p1 t2_1
- -> Seq Scan on prt1_adv_p2 t2_2
- -> Seq Scan on prt1_adv_p3 t2_3
+ -> Seq Scan on prt2_adv_p1 t1_1
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_p2 t1_2
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_p3 t1_3
+ Filter: (a = 0)
+ -> Seq Scan on prt2_adv_extra t1_4
+ Filter: (a = 0)
-> Hash
-> Append
- -> Seq Scan on prt2_adv_p1 t1_1
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_p2 t1_2
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_p3 t1_3
- Filter: (a = 0)
- -> Seq Scan on prt2_adv_extra t1_4
- Filter: (a = 0)
+ -> Seq Scan on prt1_adv_p1 t2_1
+ -> Seq Scan on prt1_adv_p2 t2_2
+ -> Seq Scan on prt1_adv_p3 t2_3
(18 rows)
-- full join; currently we can't do partitioned join if there are no matched
@@ -3146,97 +3139,97 @@ ANALYZE prt2_adv;
-- inner join
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a,
t2.b;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Sort
Sort Key: t1.a
-> Hash Join
- Hash Cond: (t2.b = t1.a)
+ Hash Cond: (t1.a = t2.b)
-> Append
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Seq Scan on prt2_adv_p3_1 t2_3
- -> Seq Scan on prt2_adv_p3_2 t2_4
+ -> Seq Scan on prt1_adv_p1 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p2 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p3 t1_3
+ Filter: (b = 0)
-> Hash
-> Append
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p2 t1_2
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt2_adv_p1 t2_1
+ -> Seq Scan on prt2_adv_p2 t2_2
+ -> Seq Scan on prt2_adv_p3_1 t2_3
+ -> Seq Scan on prt2_adv_p3_2 t2_4
(17 rows)
-- semi join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM prt1_adv t1 WHERE EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY t1.a;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Sort
Sort Key: t1.a
- -> Hash Right Semi Join
- Hash Cond: (t2.b = t1.a)
+ -> Hash Semi Join
+ Hash Cond: (t1.a = t2.b)
-> Append
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Seq Scan on prt2_adv_p3_1 t2_3
- -> Seq Scan on prt2_adv_p3_2 t2_4
+ -> Seq Scan on prt1_adv_p1 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p2 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p3 t1_3
+ Filter: (b = 0)
-> Hash
-> Append
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p2 t1_2
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt2_adv_p1 t2_1
+ -> Seq Scan on prt2_adv_p2 t2_2
+ -> Seq Scan on prt2_adv_p3_1 t2_3
+ -> Seq Scan on prt2_adv_p3_2 t2_4
(17 rows)
-- left join
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 LEFT JOIN prt2_adv t2 ON (t1.a = t2.b) WHERE t1.b = 0 ORDER BY t1.a,
t2.b;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Sort
Sort Key: t1.a, t2.b
- -> Hash Right Join
- Hash Cond: (t2.b = t1.a)
+ -> Hash Left Join
+ Hash Cond: (t1.a = t2.b)
-> Append
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Seq Scan on prt2_adv_p3_1 t2_3
- -> Seq Scan on prt2_adv_p3_2 t2_4
+ -> Seq Scan on prt1_adv_p1 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p2 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p3 t1_3
+ Filter: (b = 0)
-> Hash
-> Append
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p2 t1_2
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt2_adv_p1 t2_1
+ -> Seq Scan on prt2_adv_p2 t2_2
+ -> Seq Scan on prt2_adv_p3_1 t2_3
+ -> Seq Scan on prt2_adv_p3_2 t2_4
(17 rows)
-- anti join
EXPLAIN (COSTS OFF)
SELECT t1.* FROM prt1_adv t1 WHERE NOT EXISTS (SELECT 1 FROM prt2_adv t2 WHERE t1.a = t2.b) AND t1.b = 0 ORDER BY
t1.a;
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------
Sort
Sort Key: t1.a
- -> Hash Right Anti Join
- Hash Cond: (t2.b = t1.a)
+ -> Hash Anti Join
+ Hash Cond: (t1.a = t2.b)
-> Append
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Seq Scan on prt2_adv_p3_1 t2_3
- -> Seq Scan on prt2_adv_p3_2 t2_4
+ -> Seq Scan on prt1_adv_p1 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p2 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p3 t1_3
+ Filter: (b = 0)
-> Hash
-> Append
- -> Seq Scan on prt1_adv_p1 t1_1
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p2 t1_2
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p3 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt2_adv_p1 t2_1
+ -> Seq Scan on prt2_adv_p2 t2_2
+ -> Seq Scan on prt2_adv_p3_1 t2_3
+ -> Seq Scan on prt2_adv_p3_2 t2_4
(17 rows)
-- full join
@@ -3326,19 +3319,19 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a =
Sort
Sort Key: t1.a
-> Hash Join
- Hash Cond: (t2.b = t1.a)
+ Hash Cond: (t1.a = t2.b)
-> Append
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Seq Scan on prt2_adv_p3 t2_3
+ -> Seq Scan on prt1_adv_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p1 t1_3
+ Filter: (b = 0)
-> Hash
-> Append
- -> Seq Scan on prt1_adv_p2 t1_1
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p3 t1_2
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p1 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt2_adv_p1 t2_1
+ -> Seq Scan on prt2_adv_p2 t2_2
+ -> Seq Scan on prt2_adv_p3 t2_3
(16 rows)
ALTER TABLE prt2_adv DETACH PARTITION prt2_adv_p3;
@@ -3354,19 +3347,19 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_adv t1 INNER JOIN prt2_adv t2 ON (t1.a =
Sort
Sort Key: t1.a
-> Hash Join
- Hash Cond: (t2.b = t1.a)
+ Hash Cond: (t1.a = t2.b)
-> Append
- -> Seq Scan on prt2_adv_p1 t2_1
- -> Seq Scan on prt2_adv_p2 t2_2
- -> Seq Scan on prt2_adv_p3 t2_3
+ -> Seq Scan on prt1_adv_p2 t1_1
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p3 t1_2
+ Filter: (b = 0)
+ -> Seq Scan on prt1_adv_p1 t1_3
+ Filter: (b = 0)
-> Hash
-> Append
- -> Seq Scan on prt1_adv_p2 t1_1
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p3 t1_2
- Filter: (b = 0)
- -> Seq Scan on prt1_adv_p1 t1_3
- Filter: (b = 0)
+ -> Seq Scan on prt2_adv_p1 t2_1
+ -> Seq Scan on prt2_adv_p2 t2_2
+ -> Seq Scan on prt2_adv_p3 t2_3
(16 rows)
DROP TABLE prt1_adv_p3;
@@ -5018,11 +5011,11 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2
-> Hash
-> Seq Scan on beta_neg_p1 t2_1
-> Hash Join
- Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.b = t1_2.b))
- -> Seq Scan on beta_neg_p2 t2_2
+ Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b))
+ -> Seq Scan on alpha_neg_p2 t1_2
+ Filter: ((b >= 125) AND (b < 225))
-> Hash
- -> Seq Scan on alpha_neg_p2 t1_2
- Filter: ((b >= 125) AND (b < 225))
+ -> Seq Scan on beta_neg_p2 t2_2
-> Hash Join
Hash Cond: ((t2_4.a = t1_4.a) AND (t2_4.b = t1_4.b))
-> Append
@@ -5141,25 +5134,28 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.c = t2
EXPLAIN (COSTS OFF)
SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) WHERE ((t1.b >=
100AND t1.b < 110) OR (t1.b >= 200 AND t1.b < 210)) AND ((t2.b >= 100 AND t2.b < 110) OR (t2.b >= 200 AND t2.b < 210))
ANDt1.c IN ('0004', '0009') ORDER BY t1.a, t1.b;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------
Sort
Sort Key: t1.a, t1.b
-> Append
- -> Hash Join
- Hash Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
- -> Seq Scan on alpha_neg_p1 t1_1
- Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b
<210))))
- -> Hash
+ -> Merge Join
+ Merge Cond: ((t1_1.a = t2_1.a) AND (t1_1.b = t2_1.b) AND (t1_1.c = t2_1.c))
+ -> Sort
+ Sort Key: t1_1.a, t1_1.b, t1_1.c
+ -> Seq Scan on alpha_neg_p1 t1_1
+ Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200)
AND(b < 210))))
+ -> Sort
+ Sort Key: t2_1.a, t2_1.b, t2_1.c
-> Seq Scan on beta_neg_p1 t2_1
Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
-> Hash Join
- Hash Cond: ((t1_2.a = t2_2.a) AND (t1_2.b = t2_2.b) AND (t1_2.c = t2_2.c))
- -> Seq Scan on alpha_neg_p2 t1_2
- Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b
<210))))
+ Hash Cond: ((t2_2.a = t1_2.a) AND (t2_2.b = t1_2.b) AND (t2_2.c = t1_2.c))
+ -> Seq Scan on beta_neg_p2 t2_2
+ Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
-> Hash
- -> Seq Scan on beta_neg_p2 t2_2
- Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
+ -> Seq Scan on alpha_neg_p2 t1_2
+ Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200)
AND(b < 210))))
-> Nested Loop
Join Filter: ((t1_3.a = t2_3.a) AND (t1_3.b = t2_3.b) AND (t1_3.c = t2_3.c))
-> Seq Scan on alpha_pos_p2 t1_3
@@ -5172,7 +5168,7 @@ SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2
Filter: ((c = ANY ('{0004,0009}'::text[])) AND (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b
<210))))
-> Seq Scan on beta_pos_p3 t2_4
Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
-(29 rows)
+(32 rows)
SELECT t1.*, t2.* FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) WHERE ((t1.b >=
100AND t1.b < 110) OR (t1.b >= 200 AND t1.b < 210)) AND ((t2.b >= 100 AND t2.b < 110) OR (t2.b >= 200 AND t2.b < 210))
ANDt1.c IN ('0004', '0009') ORDER BY t1.a, t1.b;
a | b | c | a | b | c
diff --git a/src/test/regress/sql/join_hash.sql b/src/test/regress/sql/join_hash.sql
index 6b0688ab0a6..49d3fd61856 100644
--- a/src/test/regress/sql/join_hash.sql
+++ b/src/test/regress/sql/join_hash.sql
@@ -314,6 +314,7 @@ create table join_foo as select generate_series(1, 3) as id, 'xxxxx'::text as t;
alter table join_foo set (parallel_workers = 0);
create table join_bar as select generate_series(1, 10000) as id, 'xxxxx'::text as t;
alter table join_bar set (parallel_workers = 2);
+analyze join_foo, join_bar;
-- multi-batch with rescan, parallel-oblivious
savepoint settings;
pgsql-hackers by date: