Hi Kevin
On 13 December 2012 10:47, Kevin Grittner <kgrittn@xxxxxxxx> wrote:
Huan Ruan wrote:Giving actual numbers is more useful than terms like "a lot". Even
> is a lot slower than a nested loop join.
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"
> Config changes are
> 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.
>
> - 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
