diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 41521f275c8..d2a3adeb6d8 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -10060,3 +10060,38 @@ SELECT COUNT(*) FROM onek t1 LEFT JOIN tenk1 t2 19000 (1 row) +-- +-- Test that estimate_hash_bucket_stats correctly handles skewed data +-- with restriction clauses. +-- +create table hjbucket (val int not null, filt int not null); +insert into hjbucket +select + case when g <= 200 then 0 else ((g - 201) % 100) + 1 end, + g % 10 +from generate_series(1, 2000) g; +create index on hjbucket(filt); +analyze hjbucket; +set work_mem to '64kB'; +set enable_nestloop to off; +set enable_mergejoin to off; +explain (costs off) +select * from hjbucket h1 + join hjbucket h2 on h1.val = h2.val + where h1.filt = 5; + QUERY PLAN +---------------------------------------------------------- + Hash Join + Hash Cond: (h2.val = h1.val) + -> Seq Scan on hjbucket h2 + -> Hash + -> Bitmap Heap Scan on hjbucket h1 + Recheck Cond: (filt = 5) + -> Bitmap Index Scan on hjbucket_filt_idx + Index Cond: (filt = 5) +(8 rows) + +reset work_mem; +reset enable_nestloop; +reset enable_mergejoin; +drop table hjbucket; diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 4acd2512004..abcbf13d379 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -3841,3 +3841,31 @@ SELECT COUNT(*) FROM onek t1 LEFT JOIN tenk1 t2 ON (t2.thousand = t1.tenthous OR t2.thousand = t1.thousand); SELECT COUNT(*) FROM onek t1 LEFT JOIN tenk1 t2 ON (t2.thousand = t1.tenthous OR t2.thousand = t1.thousand); + +-- +-- Test that estimate_hash_bucket_stats correctly handles skewed data +-- with restriction clauses. +-- +create table hjbucket (val int not null, filt int not null); +insert into hjbucket +select + case when g <= 200 then 0 else ((g - 201) % 100) + 1 end, + g % 10 +from generate_series(1, 2000) g; +create index on hjbucket(filt); +analyze hjbucket; + +set work_mem to '64kB'; +set enable_nestloop to off; +set enable_mergejoin to off; + +explain (costs off) +select * from hjbucket h1 + join hjbucket h2 on h1.val = h2.val + where h1.filt = 5; + +reset work_mem; +reset enable_nestloop; +reset enable_mergejoin; + +drop table hjbucket;