Re: merge>hash>loop

Ian Westmacott <ianw@intellivid.com> writes:
> I have this query, where PG (8.1.2) prefers Merge Join over Hash Join
> over Nested Loop.  However, this order turns out to increase in
> performance.  I was hoping someone might be able to shed some light on
> why PG chooses the plans in this order, and what I might do to
> influence it otherwise.  Thanks,

Reducing random_page_cost would influence it to prefer the nestloop.
However, I doubt you're ever going to get really ideal results for
this query --- the estimated row counts are too far off, and the
WHERE conditions are sufficiently bizarre that there's not much hope
that they'd ever be much better.

            regards, tom lane