Обсуждение: how to troubleshoot a wrong query plan

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

Sorry, we figured out that the PostgreSQL.conf has the nestloop disabled….that’s why It picked merge join by default

But it might be still very helpful if there is a way to show how the plan is generated , or some debug information about the plan generating, for example in this case, if we can debug the plan and see nestloop is disable, it will be easy for us figure out the problem.

Thanks

James

Sent from Mail for Windows

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