Re: Re: Query planner using hash join when merge join seems orders of magnitude faster

Thanks for your reply Tom.

On Mon, Aug 1, 2016 at 6:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Branden Visser <mrvisser(at)gmail(dot)com> writes:
>> I just wanted to update that I've found evidence that fixing the
>> planner row estimation may not actually influence it to use the more
>> performant merge join instead of hash join. I have found instances
>> where the row estimation is *overestimated* by a magnitude of 4x
>> (estimates 2.4m rows) and still chooses hash join over merge join,
>> where merge join is much faster (45s v.s. 12s).
>
> I wonder why the merge join is faster exactly. It doesn't usually have a
> huge benefit unless the inputs are presorted already. The one case I can
> think of where it can win quite a lot is if the range of merge keys in one
> input is such that we can skip reading most of the other input. (Extreme
> example: one input has keys 1..10, but the other input has keys 1..10000.
> We only need to read the first 1% of the second input, assuming there's an
> index on its key column so that we don't have to read the whole thing
> anyway to sort it.)
>

The nature of the data is such that rows for tables `uv`, `ci` and `r`
aliases tend to be created and linked in unison, therefore maybe their
incremental ids and references may have some natural ordering in the
DB that coincidentally helps out the merge join?

> The planner is aware of that effect, but I wonder if it's misestimating it
> for some reason.

The uv.content_item_id field has 25% null values, if that helps
uncover anything. Aside from that it's a bit of a mystery to me.

> Anyway it would be worth looking closely at your EXPLAIN
> ANALYZE results to determine whether early-stop is happening or not. It'd
> manifest as one join input node showing an actual number of rows returned
> that's less than you'd expect.
>

I think I'd need a little more detail on this to be able to
investigate it. The merge join shows a number of rows (~410k)
consistent with the sort that occurs just before and just after. Also
the join with result_items shows an expected increase in rows given my
data.

All that said adding an index on the r.content_item_id has improved
the performance of this query without having to fiddle with the
planner -- a pretty silly oversight :/

Let me know if there's anything in here that you want me to dig into
any further.

Cheers,
Branden

> regards, tom lane