Thanks Tom, except for setting parameters, is there any other ways to show debug information about plan generating? For example, can pg show all the candidate plans it searched, and the cost of each candidate plan?
From: Tom Lane <tgl@xxxxxxxxxxxxx>
Sent: Wednesday, December 27, 2023 4:21:51 PM
To: jian xu <jamesxu@xxxxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: how to troubleshoot a wrong query plan
Sent: Wednesday, December 27, 2023 4:21:51 PM
To: jian xu <jamesxu@xxxxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: how to troubleshoot a wrong query plan
jian xu <jamesxu@xxxxxxxxxxx> writes:
> 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.
In supported releases of PG, there's the EXPLAIN (SETTINGS) option, which
will print the values of any non-default settings that affect planning.
Of course, you have to remember to use that, but it does respond to
your point.
regards, tom lane
> 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.
In supported releases of PG, there's the EXPLAIN (SETTINGS) option, which
will print the values of any non-default settings that affect planning.
Of course, you have to remember to use that, but it does respond to
your point.
regards, tom lane
