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

Alexandre Felipe <o(dot)alexandre(dot)felipe(at)gmail(dot)com> writes:
> Your query grows with the 4th power of the number of rows, and the table
> statistics show 2910 rows for that table.

Actually there aren't any statistics. Rather than trust the observed
fact that the table is of size zero, the planner assumes it's 10
pages, and then 2910 rows is what could be expected to fit with
zero-column rows. (The alternative of trusting the table to be empty
is not better: it leads to planning failures in the other direction
where we make a plan for trivial amounts of data and then it runs
forever because there's more data than the planner thought.)

> So the plan estimates 211 quadrillion rows,

Yeah. Specifically, the CTE is estimated to produce 2910*2910*768
rows, and then the planner thinks it's dealing with a darn big hash
join, so it instructs the executor to set up for that:

-> Hash (cost=130070016.00..130070016.00 rows=6503500800 width=4) (actual time=5.853..5.853 rows=768.00 loops=1)
Buckets: 4194304 Batches: 4096 Memory Usage: 32768kB

It's the overhead of setting up and tearing down all those batches
that is making the query take so long. (If you don't suppress the
timing figures, you'll see that that overhead is charged to the Hash
Join node not the Hash node, which is a bit of an implementation
artifact.) If we actually did have that much data to contend with,
of course the setup overhead would be negligible, but with a trivial
amount of actual data it dominates the runtime. Reducing work_mem
reduces this overhead by constraining how much memory the executor
is allowed to allocate --- but that would be a pretty bad idea if
there actually were a lot of rows to join.

> If you run an analyse here you get an accurate estimate of the number rows
> in the table.

Indeed. So I think this is an uninteresting contrived case.

regards, tom lane