Re: hash join vs nested loop join

Поиск
Список
Период
Сортировка
От Huan Ruan
Тема Re: hash join vs nested loop join
Дата
Msg-id CAD1stZvXsyXNjga4JHuBeBnwMKsxmSCN9JsROouw7+jgJkesjQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: hash join vs nested loop join  ("Kevin Grittner" <kgrittn@mail.com>)
Список pgsql-performance
Дерево обсуждения

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 по дате отправления:

Предыдущее
От: "Kevin Grittner"
Дата:
Сообщение: Re: Slow queries after vacuum analyze
Следующее
От: "Kevin Grittner"
Дата:
Сообщение: Re: hash join vs nested loop join