Re: hash join vs nested loop join

Hi Kevin

On 13 December 2012 10:47, Kevin Grittner <kgrittn(at)mail(dot)com> wrote:

> Huan Ruan wrote:
>
> > is a lot slower than a nested loop join.
>
> Giving actual numbers is more useful than terms like "a lot". Even
> better is to provide the output of EXPLAIN ANALYZZE rather than
> just EXPLAIN. This shows estimates against actual numbers, and give
> timings. For more suggestions see this page:
>
> http://wiki.postgresql.org/wiki/SlowQueryQuestions

You are right. I realised my information wasn't accurate. Was a bit slack
and canceled the slower one. The full outputs are

Hash 1st run

"QUERY PLAN"
"Hash Join (cost=1681.87..6414169.04 rows=48261 width=171) (actual
time=2182.450..88158.645 rows=48257 loops=1)"
" Hash Cond: (bigtable.invtranref = smalltable.invtranref)"
" Buffers: shared hit=3950 read=3046219"
" -> Seq Scan on invtran bigtable (cost=0.00..4730787.28 rows=168121728
width=108) (actual time=0.051..32581.052 rows=168121657 loops=1)"
" Buffers: shared hit=3351 read=3046219"
" -> Hash (cost=1078.61..1078.61 rows=48261 width=63) (actual
time=21.751..21.751 rows=48261 loops=1)"
" Buckets: 8192 Batches: 1 Memory Usage: 4808kB"
" Buffers: shared hit=596"
" -> Seq Scan on im_match_table smalltable (cost=0.00..1078.61
rows=48261 width=63) (actual time=0.007..8.299 rows=48261 loops=1)"
" Buffers: shared hit=596"
"Total runtime: 88162.417 ms"

Hash 2nd run (after disconnect and reconnect)

"QUERY PLAN"
"Hash Join (cost=1681.87..6414169.04 rows=48261 width=171) (actual
time=2280.390..87934.540 rows=48257 loops=1)"
" Hash Cond: (bigtable.invtranref = smalltable.invtranref)"
" Buffers: shared hit=3982 read=3046187"
" -> Seq Scan on invtran bigtable (cost=0.00..4730787.28 rows=168121728
width=108) (actual time=0.052..32747.805 rows=168121657 loops=1)"
" Buffers: shared hit=3383 read=3046187"
" -> Hash (cost=1078.61..1078.61 rows=48261 width=63) (actual
time=62.161..62.161 rows=48261 loops=1)"
" Buckets: 8192 Batches: 1 Memory Usage: 4808kB"
" Buffers: shared hit=596"
" -> Seq Scan on im_match_table smalltable (cost=0.00..1078.61
rows=48261 width=63) (actual time=0.006..8.209 rows=48261 loops=1)"
" Buffers: shared hit=596"
"Total runtime: 87938.584 ms"

NL 1st run

"QUERY PLAN"
"Nested Loop (cost=0.00..6451637.88 rows=48261 width=171) (actual
time=0.056..551.438 rows=48257 loops=1)"
" Buffers: shared hit=242267"
" -> Seq Scan on im_match_table smalltable (cost=0.00..1078.61
rows=48261 width=63) (actual time=0.009..7.353 rows=48261 loops=1)"
" Buffers: shared hit=596"
" -> Index Scan using pk_invtran on invtran bigtable (cost=0.00..133.65
rows=1 width=108) (actual time=0.010..0.010 rows=1 loops=48261)"
" Index Cond: (invtranref = smalltable.invtranref)"
" Buffers: shared hit=241671"
"Total runtime: 555.336 ms"

NL 2nd run (after disconnect and reconnect)

"QUERY PLAN"
"Nested Loop (cost=0.00..6451637.88 rows=48261 width=171) (actual
time=0.058..554.215 rows=48257 loops=1)"
" Buffers: shared hit=242267"
" -> Seq Scan on im_match_table smalltable (cost=0.00..1078.61
rows=48261 width=63) (actual time=0.009..7.416 rows=48261 loops=1)"
" Buffers: shared hit=596"
" -> Index Scan using pk_invtran on invtran bigtable (cost=0.00..133.65
rows=1 width=108) (actual time=0.010..0.010 rows=1 loops=48261)"
" Index Cond: (invtranref = smalltable.invtranref)"
" Buffers: shared hit=241671"
"Total runtime: 558.095 ms"

>
>
> > 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.
>
> > Config changes are
> >
> > - shared_buffers = 6GB
> > - effective_cache_size = 18GB
> > - work_mem = 10MB
> > - maintenance_work_mem = 3GB
>
> As already suggested, there was a change made in 9.2 which may have
> over-penalized nested loops using index scans. This may be fixed in
> the next minor release.
>

Will keep this in mind.

>
> Also, as already suggested, you may want to reduce random_page
> cost, to bring it in line with the actual cost relative to
> seq_page_cost based on your cache hit ratio.
>
> Additionally, I just routinely set cpu_tuple_cost higher than the
> default of 0.01. I find that 0.03 to 0.05 better models the actual
> relative cost of processing a tuple.
>

I originally reduced random_page_cost to 2 to achieve the nested loop join.
Now I set cpu_tuple_cost to 0.05 and reset random_page_cost back to 4, I
can also achieve a nested loop join.

I'm still new in Postgres, but I'm worried about random_page_cost being 2
is too low, so maybe increasing cpu_tuple_cost is a better choice. All
these tuning probably also depends on the above mentioned possible fix as
well. Can you see any obvious issues with the other memory settings I
changed?

Thanks for your help.

Cheers
Huan

> -Kevin
>