Re: hash join vs nested loop join

"Huan Ruan" <huan(dot)ruan(dot)it(at)gmail(dot)com> wrote:

> explain (analyze, buffers)
> SELECT
>  *
> FROM IM_Match_Table smalltable
>  inner join invtran bigtable on bigtable.invtranref = smalltable.invtranref

Well, one table or the other will need to be read in full, and you
would normally want that one to be the small table. When there is
no ORDER BY clause, the fastest way to do that will normally be a
seqscan. So that part of the query is as it should be. The only
question is whether the access to the big table is using the
fastest technique.

If you want to see what the planner's second choice would have
been, you could run:

SET enable_indexscan = off;

on a connection and try the explain again. If you don't like that
one, you might be able to disable another node type and see what
you get. If one of the other alternatives is faster, that would
suggest that adjustments are needed to the costing factors;
otherwise, it just takes that long to read hundreds of thousands of
rows in one table and look for related data for each of them in
another table.

> "Nested Loop (cost=0.00..341698.92 rows=48261 width=171) (actual
> time=0.042..567.980 rows=48257 loops=1)"

Frankly, at 12 microseconds per matched pair of rows, I think
you're doing OK.

-Kevin