Optimizing Queries with Planner Settings and Planner Hints

You can test optimizing queries by creating variants. In the Rewrite Query page of the new variant creation flow, you can not only rewrite a query, but also tweak the planner settings and planner hints. Each allows you to influence the query planner's decisions. Planner hints offer fine-grained control but requires a module to be loaded (see below).

Editing Planner Settings

In the Edit Planner Settings modal, you can tweak the configuration parameters that influence the query plan chosen by the query optimizer. These settings will be applied only to the current variant.

For instance, you can set enable_nestloop to off to ensure that nested loop joins will not be used in this variant. You can only save the setting if its value differs from the default. For example, for enable_nestloop, the default value is on: you can only set it to off.

Editing Planner Hints

The Edit Planner Hints modal helps you add pg_hint_plan hinting phrases to the query. Pick a hint to add, then specify the value enclosed in parentheses.

Note: Editing planner hints is only available when the database is loading the pg_hint_plan module in shared_preload_libraries. Check the Enabling pg_hint_plan page for how to enable pg_hint_plan.

When saving the hint, it is validated against the current query. It checks any syntax errors, whether it will be used (e.g., if the hint specifies a table name that does not exist in the query, it will be considered unused), or if it is redundant or conflicts with existing hints.

When a new hint is saved, the query is updated with the hinting phrases. In contrast, saving a new planner setting does not update the current query, but the changes are reflected in the sidebar.