Re: hash join vs nested loop join

> Quite possibly, but it could be any of a number of other things,
> like a type mismatch. It might be best to rule out other causes. If
> you post the new query and EXPLAIN ANALYZE output, along with the
> settings you have now adopted, someone may be able to spot
> something. It wouldn't hurt to repeat OS and hardware info with it
> so people have it handy for reference.
>
>
Sorry for the late reply. To summarise,

The version is PostgreSQL 9.2.0 on x86_64-unknown-linux-gnu, compiled by
gcc (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit. Server specs are:

- Centos, ext4
- 24GB memory
- 6 cores hyper-threaded (Intel(R) Xeon(R) CPU E5645).
- raid 10 on 4 sata disks

Config changes are

- shared_buffers = 6GB
- work_mem = 80MB
- maintenance_work_mem = 3GB
- effective_cache_size = 22GB
- seq_page_cost = 0.1
- random_page_cost = 0.1
- cpu_tuple_cost = 0.05
- geqo = off

The query is,

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

The result is,

"QUERY PLAN"
"Nested Loop (cost=0.00..341698.92 rows=48261 width=171) (actual
time=0.042..567.980 rows=48257 loops=1)"
" Buffers: shared hit=242267"
" -> Seq Scan on im_match_table smalltable (cost=0.00..2472.65
rows=48261 width=63) (actual time=0.006..8.230 rows=48261 loops=1)"
" Buffers: shared hit=596"
" -> Index Scan using pk_invtran on invtran bigtable (cost=0.00..6.98
rows=1 width=108) (actual time=0.010..0.011 rows=1 loops=48261)"
" Index Cond: (invtranref = smalltable.invtranref)"
" Buffers: shared hit=241671"
"Total runtime: 571.662 ms"