Re: hash join vs nested loop join

On Tue, Dec 11, 2012 at 8:25 PM, Huan Ruan <leohuanruan@gmail.com> wrote:
> Hello All
>
> While investigating switching to Postgres, we come across a query plan that
> uses hash join and is a lot slower than a nested loop join.
>
> I don't understand why the optimiser chooses the hash join in favor of the
> nested loop. What can I do to get the optimiser to make a better decision
> (nested loop in this case)? I have run analyze on both tables.
>
> The query is,
>
> /*
>    smalltable has about 48,000 records.
>    bigtable has about 168,000,000 records.
>    invtranref is char(10) and is the primary key for both tables
> */
> SELECT
>   *
> FROM IM_Match_Table smalltable
>   inner join invtran bigtable on
>     bigtable.invtranref = smalltable.invtranref

..

> "  ->  Index Scan using pk_invtran on public.invtran bigtable (cost=0.00..267.03 rows=1 width=108)"


This looks like the same large-index over-penalty as discussed in the
recent thread "[PERFORM] Slow query: bitmap scan troubles".

Back-patching the log(npages) change is starting to look like a good idea.

Cheers,

Jeff