Hello,
We found a slow query(it took minutes), which is due to the bad execution plan. The plan uses hash join with 2 tables, the problem is that it uses seq scan on a very large table(the join only returns 1k rows). Actually there is a index on the join column, If we disable hash join and merge join, and force it to run with nestloop join, the plan can use the index scan on the table, which is super fast(completes in a sec).
By comparing the bad plan(seq scan with hash join) and good plan(index scan next loop join), the estimation value of the good query plan is much smaller than the bad plan, my understanding is pg execution plan is cost based. But why it will pick a plan with a high estimation cost?
I also run vacuum and analyze, even run alter column set statistics to 10,000 , but it always picks the seq scan with hash join which has a high estimation cost. Is there any way to figure out why pg decides to use seq scan + hash join, is there any way to look inside how the plan is generated?
Thanks,
James