pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Joel Jacobson <joel@compiler.org>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: [BUG?] estimate_hash_bucket_stats uses wrong ndistinct for avgfreq
Date: Tue, 24 Feb 2026 19:21:06 +0100
Message-ID: <341b723c-da45-4058-9446-1514dedb17c1@app.fastmail.com> (raw )
Hi hackers,
I think I might have stumbled upon a planner bug in
src/backend/utils/adt/selfuncs.c, while working on something else.
I've traced it down to bd3e3e9 (Ensure sanity of hash-join costing when
there are no MCV statistics.) that added a fallback to compute
mcv_freq, which surfaced an existing bug, due to a branch that was not
taken when mcv_freq was zero.
if (avgfreq > 0.0 && *mcv_freq > avgfreq)
estfract *= *mcv_freq / avgfreq;
Here, since this expression is a ratio, it seems important that both the
numerator and divisor are of the same base dimension.
mcv_freq is [freq of most common values in filtered relation]
However, avgfreq is currently [avg freq of all distinct data values in
raw relation]:
/* Compute avg freq of all distinct data values in raw relation */
avgfreq = (1.0 - stanullfrac) / ndistinct;
After avgfreq has been assigned, ndistinct is then adjusted, to account
for restriction clauses:
if (vardata.rel && vardata.rel->tuples > 0)
{
ndistinct *= vardata.rel->rows / vardata.rel->tuples;
ndistinct = clamp_row_est(ndistinct);
}
The expression further down
estfract *= *mcv_freq / avgfreq;
therefore divides two frequencies of different bases, when
I think they should be of the same base.
If we simply move the computation of avgfreq to after the adjustment
of ndistinct, avgfreq will be of the same base as mcv_freq.
This bug seems to sometimes cause the wrong table, the larger table, to
be hashed in a Hash Join, and the smaller table to be used for probing.
Here is a demo:
CREATE TABLE orders (
order_id bigint NOT NULL,
tracking_code bigint,
region int NOT NULL,
status text NOT NULL,
order_data text
);
CREATE TABLE shipments (
shipment_id bigint NOT NULL,
tracking_code bigint NOT NULL,
carrier text NOT NULL,
ship_data text
);
INSERT INTO orders (order_id, tracking_code, region, status, order_data)
SELECT
g,
CASE WHEN g <= 150000 THEN g ELSE NULL END,
(g % 1000) + 1,
CASE WHEN g <= 150000 THEN 'shipped' ELSE 'pending' END,
'order-' || g
FROM generate_series(1, 200000) g;
INSERT INTO shipments (shipment_id, tracking_code, carrier, ship_data)
SELECT
g,
g,
CASE (g % 3) WHEN 0 THEN 'FedEx' WHEN 1 THEN 'UPS' ELSE 'DHL' END,
repeat('x', 200) || g::text
FROM generate_series(1, 150000) g;
CREATE INDEX ON orders (region);
ANALYZE orders;
ANALYZE shipments;
EXPLAIN ANALYZE
SELECT o.order_id, o.tracking_code, s.carrier, s.ship_data
FROM orders o
JOIN shipments s ON s.tracking_code = o.tracking_code
WHERE o.region = 42;
master (65707ed):
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=9138.25..14237.55 rows=149 width=229) (actual time=70.984..78.977 rows=150.00 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=6557, temp read=3987 written=4068
-> Parallel Hash Join (cost=8138.25..13222.65 rows=88 width=229) (actual time=68.237..75.806 rows=75.00 loops=2)
Hash Cond: (o.tracking_code = s.tracking_code)
Buffers: shared hit=6557, temp read=3987 written=4068
-> Parallel Seq Scan on orders o (cost=0.00..3188.59 rows=117 width=16) (actual time=0.076..12.053 rows=100.00 loops=2)
Filter: (region = 42)
Rows Removed by Filter: 99900
Buffers: shared hit=1718
-> Parallel Hash (cost=5464.00..5464.00 rows=62500 width=221) (actual time=53.216..53.217 rows=75000.00 loops=2)
Buckets: 32768 Batches: 8 Memory Usage: 5120kB
Buffers: shared hit=4839, temp written=4004
-> Parallel Seq Scan on shipments s (cost=0.00..5464.00 rows=62500 width=221) (actual time=0.009..16.114 rows=75000.00 loops=2)
Buffers: shared hit=4839
Planning:
Buffers: shared hit=86 read=1
Planning Time: 0.350 ms
Execution Time: 79.011 ms
0001-Fix-estimate_hash_bucket_stats-to-use-filtered-ndist.patch:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1578.75..7292.64 rows=149 width=229) (actual time=0.521..15.592 rows=150.00 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=5447 read=3
-> Hash Join (cost=578.75..6277.74 rows=62 width=229) (actual time=0.667..12.342 rows=50.00 loops=3)
Hash Cond: (s.tracking_code = o.tracking_code)
Buffers: shared hit=5447 read=3
-> Parallel Seq Scan on shipments s (cost=0.00..5464.00 rows=62500 width=221) (actual time=0.006..5.581 rows=50000.00 loops=3)
Buffers: shared hit=4839
-> Hash (cost=576.26..576.26 rows=199 width=16) (actual time=0.429..0.429 rows=150.00 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
Buffers: shared hit=608 read=3
-> Bitmap Heap Scan on orders o (cost=5.84..576.26 rows=199 width=16) (actual time=0.083..0.396 rows=200.00 loops=3)
Recheck Cond: (region = 42)
Heap Blocks: exact=200
Buffers: shared hit=608 read=3
-> Bitmap Index Scan on orders_region_idx (cost=0.00..5.79 rows=199 width=0) (actual time=0.039..0.039 rows=200.00 loops=3)
Index Cond: (region = 42)
Index Searches: 3
Buffers: shared hit=8 read=3
Planning:
Buffers: shared hit=86 read=1
Planning Time: 0.371 ms
Execution Time: 15.634 ms
The fix causes quite a lot of plans in
src/test/regress/expected/partition_join.out to change, which makes me a
bit worried I might have misunderstood something here. I haven't
verified if all the new plans are improvements, I just copied the result
file to the expected dir.
/Joel
Attachments:
0001-Fix-estimate_hash_bucket_stats-to-use-filtered-ndist.patch (application/octet-stream, 35.1K) download
From fc1f659755943530a1fe8173c707acf267ab4c01 Mon Sep 17 00:00:00 2001
From: Joel Jacobson <joel@compiler.org>
Date: Tue, 24 Feb 2026 18:51:06 +0100
Subject: [PATCH] Fix estimate_hash_bucket_stats to use filtered ndistinct
avgfreq was computed from raw ndistinct (before the filtering
adjustment), but mcv_freq uses the filtered relation's row
count. Since the skew ratio mcv_freq / avgfreq is supposed
to divide two values of the same dimension, this caused the
ratio to measure 1/selectivity rather than actual skew.
This was exposed by bd3e3e9e568 which added a fallback to
compute mcv_freq when no MCV statistics exist, surfacing an
existing bug in a branch that was not taken when mcv_freq
was zero.
Fix by moving the computation of avgfreq to after the
ndistinct filtering adjustment, so avgfreq is of the same
base dimension as mcv_freq.
---
src/backend/utils/adt/selfuncs.c | 6 +-
src/test/regress/expected/join.out | 20 +-
src/test/regress/expected/partition_join.out | 432 ++++++++++---------
3 files changed, 231 insertions(+), 227 deletions(-)
diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c
index 29fec655593..d19c4b5d968 100644
--- a/src/backend/utils/adt/selfuncs.c
+++ b/src/backend/utils/adt/selfuncs.c
@@ -4456,9 +4456,6 @@ estimate_hash_bucket_stats(PlannerInfo *root, Node *hashkey, double nbuckets,
else
stanullfrac = 0.0;
- /* Compute avg freq of all distinct data values in raw relation */
- avgfreq = (1.0 - stanullfrac) / ndistinct;
-
/*
* Adjust ndistinct to account for restriction clauses. Observe we are
* assuming that the data distribution is affected uniformly by the
@@ -4473,6 +4470,9 @@ estimate_hash_bucket_stats(PlannerInfo *root, Node *hashkey, double nbuckets,
ndistinct = clamp_row_est(ndistinct);
}
+ /* Compute avg freq of all distinct data values in the filtered relation */
+ avgfreq = (1.0 - stanullfrac) / ndistinct;
+
/*
* Initial estimate of bucketsize fraction is 1/nbuckets as long as the
* number of buckets is less than the expected number of distinct values;
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 072a7347b81..41521f275c8 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -9429,19 +9429,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: (f1.x = f3.x)
+ Hash Cond: ((f2.x = f1.x) AND (f2.x10b = f1.x10))
-> Hash Join
- Hash Cond: ((f2.x = f1.x) AND (f2.x10b = f1.x10))
- -> Seq Scan on fkest f2
- Filter: (x100 = 2)
- -> Hash
- -> Seq Scan on fkest f1
- Filter: (x100 = 2)
- -> Hash
+ Hash Cond: (f3.x = f2.x)
-> Seq Scan on fkest f3
+ -> Hash
+ -> Seq Scan on fkest f2
+ Filter: (x100 = 2)
+ -> Hash
+ -> Seq Scan on fkest f1
+ Filter: (x100 = 2)
(11 rows)
rollback;
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index f6d3ade368a..38643d41fd7 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 Left Join
- Hash Cond: (t1.a = t2.b)
+ -> Hash Right Join
+ Hash Cond: (t2.b = t1.a)
-> 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
-> Hash
-> 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)
(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) t2 ON 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 Left Join
- Hash Cond: (prt1.a = prt2.b)
+ -> Hash Right Join
+ Hash Cond: (prt2.b = prt1.a)
-> 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)
-> Hash
-> 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))
(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) t2 ON 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: (((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 Cond: (((t2_1.b + t2_1.a) / 2) = ((t1_1.a + t1_1.b) / 2))
+ -> Seq Scan on prt2_e_p1 t2_1
-> Hash
- -> Seq Scan on prt2_e_p1 t2_1
+ -> Seq Scan on prt1_e_p1 t1_1
+ Filter: (c = 0)
-> Hash Join
- 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 Cond: (((t2_2.b + t2_2.a) / 2) = ((t1_2.a + t1_2.b) / 2))
+ -> Seq Scan on prt2_e_p2 t2_2
-> Hash
- -> Seq Scan on prt2_e_p2 t2_2
+ -> Seq Scan on prt1_e_p2 t1_2
+ Filter: (c = 0)
-> Hash Join
- 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 Cond: (((t2_3.b + t2_3.a) / 2) = ((t1_3.a + t1_3.b) / 2))
+ -> Seq Scan on prt2_e_p3 t2_3
-> Hash
- -> Seq Scan on prt2_e_p3 t2_3
+ -> Seq Scan on prt1_e_p3 t1_3
+ Filter: (c = 0)
(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 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 Right Join
+ Hash Cond: (((t3_1.a + t3_1.b) / 2) = t1_1.a)
+ -> Seq Scan on prt1_e_p1 t3_1
-> Hash
- -> 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 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
-> Hash
- -> Seq Scan on prt1_e_p2 t3_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 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 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 Right Join
+ Hash Cond: (t1_1.a = ((t3_1.a + t3_1.b) / 2))
+ -> Seq Scan on prt1_p1 t1_1
-> Hash
- -> Seq Scan on prt1_p1 t1_1
+ -> Seq Scan on prt1_e_p1 t3_1
+ Filter: (c = 0)
-> Index Scan using iprt2_p1_b on prt2_p1 t2_1
Index Cond: (b = t1_1.a)
-> Nested Loop Left Join
- -> 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 Right Join
+ Hash Cond: (t1_2.a = ((t3_2.a + t3_2.b) / 2))
+ -> Seq Scan on prt1_p2 t1_2
-> Hash
- -> Seq Scan on prt1_p2 t1_2
+ -> Seq Scan on prt1_e_p2 t3_2
+ Filter: (c = 0)
-> 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))
- -> 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
+ -> 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_1.a = prt2_1.b)
-> Seq Scan on prt1_p1 prt1_1
@@ -1095,6 +1095,12 @@ 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
@@ -1102,6 +1108,12 @@ 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
@@ -1109,15 +1121,10 @@ 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
- -> 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)
+ -> Hash
-> Seq Scan on prt1_e_p3 prt1_e_3
Filter: (c = 0)
-(35 rows)
+(42 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
@@ -1139,11 +1146,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: (t1_5.b = ((t2_1.a + t2_1.b) / 2))
- -> Seq Scan on prt2_p1 t1_5
- Filter: (a = 0)
+ Hash Cond: (((t2_1.a + t2_1.b) / 2) = t1_5.b)
+ -> Seq Scan on prt1_e_p1 t2_1
-> Hash
- -> Seq Scan on prt1_e_p1 t2_1
+ -> Seq Scan on prt2_p1 t1_5
+ Filter: (a = 0)
-> Index Scan using iprt1_p1_a on prt1_p1 t1_2
Index Cond: (a = ((t2_1.a + t2_1.b) / 2))
Filter: (b = 0)
@@ -1153,11 +1160,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: (t1_6.b = ((t2_2.a + t2_2.b) / 2))
- -> Seq Scan on prt2_p2 t1_6
- Filter: (a = 0)
+ Hash Cond: (((t2_2.a + t2_2.b) / 2) = t1_6.b)
+ -> Seq Scan on prt1_e_p2 t2_2
-> Hash
- -> Seq Scan on prt1_e_p2 t2_2
+ -> Seq Scan on prt2_p2 t1_6
+ Filter: (a = 0)
-> Index Scan using iprt1_p2_a on prt1_p2 t1_3
Index Cond: (a = ((t2_2.a + t2_2.b) / 2))
Filter: (b = 0)
@@ -1937,12 +1944,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 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 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
- -> Seq Scan on prt2_l_p1 t2_1
+ -> Seq Scan on prt1_l_p1 t1_1
+ Filter: (b = 0)
-> 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
@@ -2961,26 +2968,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 Left Join
- Hash Cond: (t1.b = t2.a)
+ -> Hash Right Join
+ Hash Cond: (t2.a = t1.b)
-> 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
-> Hash
-> 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)
(18 rows)
-- anti join
@@ -3024,26 +3031,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 Anti Join
- Hash Cond: (t1.b = t2.a)
+ -> Hash Right Anti Join
+ Hash Cond: (t2.a = t1.b)
-> 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
-> Hash
-> 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)
(18 rows)
-- full join; currently we can't do partitioned join if there are no matched
@@ -3139,97 +3146,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: (t1.a = t2.b)
+ Hash Cond: (t2.b = t1.a)
-> 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
-> Hash
-> 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)
(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 Semi Join
- Hash Cond: (t1.a = t2.b)
+ -> Hash Right Semi Join
+ Hash Cond: (t2.b = t1.a)
-> 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
-> Hash
-> 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)
(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 Left Join
- Hash Cond: (t1.a = t2.b)
+ -> Hash Right Join
+ Hash Cond: (t2.b = t1.a)
-> 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
-> Hash
-> 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)
(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 Anti Join
- Hash Cond: (t1.a = t2.b)
+ -> Hash Right Anti Join
+ Hash Cond: (t2.b = t1.a)
-> 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
-> Hash
-> 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)
(17 rows)
-- full join
@@ -3319,19 +3326,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: (t1.a = t2.b)
+ Hash Cond: (t2.b = t1.a)
-> 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
-> Hash
-> 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)
(16 rows)
ALTER TABLE prt2_adv DETACH PARTITION prt2_adv_p3;
@@ -3347,19 +3354,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: (t1.a = t2.b)
+ Hash Cond: (t2.b = t1.a)
-> 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
-> Hash
-> 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)
(16 rows)
DROP TABLE prt1_adv_p3;
@@ -5011,11 +5018,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: ((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 Cond: ((t2_2.a = t1_2.a) AND (t2_2.b = t1_2.b))
+ -> Seq Scan on beta_neg_p2 t2_2
-> Hash
- -> Seq Scan on beta_neg_p2 t2_2
+ -> Seq Scan on alpha_neg_p2 t1_2
+ Filter: ((b >= 125) AND (b < 225))
-> Hash Join
Hash Cond: ((t2_4.a = t1_4.a) AND (t2_4.b = t1_4.b))
-> Append
@@ -5134,28 +5141,25 @@ 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 >= 100 AND 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)) AND t1.c IN ('0004', '0009') ORDER BY t1.a, t1.b;
- QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------
Sort
Sort Key: t1.a, t1.b
-> Append
- -> 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
+ -> 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
-> Seq Scan on beta_neg_p1 t2_1
Filter: (((b >= 100) AND (b < 110)) OR ((b >= 200) AND (b < 210)))
-> Hash Join
- 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 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
- -> 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))))
+ -> Seq Scan on beta_neg_p2 t2_2
+ Filter: (((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
@@ -5168,7 +5172,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)))
-(32 rows)
+(29 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 >= 100 AND 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)) AND t1.c IN ('0004', '0009') ORDER BY t1.a, t1.b;
a | b | c | a | b | c
--
2.52.0
view thread (20+ messages) latest in thread
Message-ID: <341b723c-da45-4058-9446-1514dedb17c1@app.fastmail.com>
Permalink: ../341b723c-da45-4058-9446-1514dedb17c1@app.fastmail.com/
Also on: postgresql.org/message-id/341b723c-da45-4058-9446-1514dedb17c1@app.fastmail.com
copy link · copy postgr.es · copy git am
reply 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: joel@compiler.org
Subject: Re: [BUG?] estimate_hash_bucket_stats uses wrong ndistinct for avgfreq
In-Reply-To: <341b723c-da45-4058-9446-1514dedb17c1@app.fastmail.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