Thanks, Tom. This helps.
If I don't change the database structures (like index), and only
modify "Planner cost constants", is it possible to make DB change the
query plan? I've tried, but looks like only chaning these constants
don't affect the query plan. Or maybe I'm wrong.
I appreciate your time and help.
-Yangyang
On Wed, Nov 30, 2011 at 11:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Yangyang <yangyangbm@gmail.com> writes:
>> I'm trying to force the database to select another query plan for a
>> query, rather than the optimal one.
>> I changed query tuning related parameters, but the query plan stays
>> the same. Although the costs for nodes changed correspondingly,
>> What parameters can I modify to force the DB to select another plan?
>
> There are the enable_xxx flags, or if the problem is a bad join order
> you could reduce the join_collapse_limit setting to 1 and use JOIN
> syntax to specify the join order. If you'd like it to not use a
> particular index you could transiently drop that index:
>
> begin;
> drop index ...;
> explain ...;
> rollback;
>
> I recently rewrote the manual's discussion of using explain, so
> you might care to read this:
> http://developer.postgresql.org/pgdocs/postgres/using-explain.html
> although it does refer to a couple of features that don't exist in
> production releases yet.
>
> regards, tom lane