Re: BUG #19708: Hash Join becomes about 300x slower with higher work_mem

Hi Alexandre,

Here it is. Two tables of 5M rows. Half the rows come from 1000 repeat
customers and half from one time customers. The repeat customers are
different people in the two tables, so the join returns the one time matches
only.

DROP TABLE IF EXISTS orders, tickets;

CREATE TABLE orders AS
SELECT CASE WHEN g % 2 = 0 THEN (g / 2) % 1000 + 1
ELSE 1000000 + g END AS customer_id,
g AS order_id
FROM generate_series(1, 5000000) g;

CREATE TABLE tickets AS
SELECT CASE WHEN g % 2 = 0 THEN (g / 2) % 1000 + 2001
ELSE 1000000 + g END AS customer_id,
g AS ticket_id
FROM generate_series(1, 5000000) g;

ANALYZE orders, tickets;

SET max_parallel_workers_per_gather = 0;
SET enable_mergejoin = off;
SET enable_nestloop = off;

EXPLAIN (ANALYZE, TIMING OFF, BUFFERS OFF)
SELECT count(*) FROM orders o JOIN tickets t USING (customer_id);

n_distinct comes out at 31626 for orders and 31744 for tickets, against a
true value of 2501000 for both. The plan:

Aggregate (actual rows=1.00 loops=1)
-> Hash Join (cost=154176.00..36256745.52 rows=783432952 width=0)
(actual rows=2500000.00 loops=1)
Hash Cond: (o.customer_id = t.customer_id)
-> Seq Scan on orders o (cost=0.00..72144.00 rows=5000000
width=4) (actual rows=5000000.00 loops=1)
-> Hash (cost=72144.00..72144.00 rows=5000000 width=4) (actual
rows=5000000.00 loops=1)
Buckets: 262144 Batches: 64 Memory Usage: 5015kB
-> Seq Scan on tickets t (cost=0.00..72144.00 rows=5000000
width=4) (actual rows=5000000.00 loops=1)
Execution Time: 1334.874 ms

That is 313 times too high on statistics that are one second old. The exact
numbers move a little between runs because ANALYZE samples.

I should be clear that this query on its own does not show the hash join
problem. The hashed side here is a base table and its row count is estimated
correctly, so 64 batches is the right answer. It takes one more join for the
inflated estimate to land on the inner side of a hash, and that is where the
batch count runs away.

Thanks,
Shihao