Postgres Pro
Downloads
Home   >   mailing lists

Re: hash join vs nested loop join - Mailing list pgsql-performance

From Huan Ruan
Subject Re: hash join vs nested loop join
Date
Msg-id CAD1stZvXsyXNjga4JHuBeBnwMKsxmSCN9JsROouw7+jgJkesjQ@mail.gmail.com
Whole thread Raw
In response to Re: hash join vs nested loop join  ("Kevin Grittner" <kgrittn@mail.com>)
List pgsql-performance
Tree view

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"

 

pgsql-performance by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: Slow queries after vacuum analyze
Next
From: "Kevin Grittner"
Date:
Subject: Re: hash join vs nested loop join