Log in|Book a meeting
Navigation

pg_hint_plan Extension

Postgres

Overview

This extension allows you to influence the query planner's decisions by providing hints. It can be used to optimize query performance by guiding the planner towards more efficient execution plans.

Dashboard Configuration

This extension requires activation via the PlanetScale Dashboard before it can be used. It must be enabled through shared libraries and requires a database restart.

To enable pg_hint_plan:

  1. From the PlanetScale organization dashboard, select the desired database
  2. Navigate to the Cluster configuration page from the menu on the left
  3. Choose the branch whose extensions you'd like to configure in the "Branch" dropdown
  4. Select the Extensions tab
  5. Enable pg_hint_plan and configure its parameters
  6. Click Queue extension changes to apply the configuration
  7. Once you're ready to apply the changes, click "Apply changes"

Parameters

pg_hint_plan.enable_hint

  • Type: Boolean
  • Default: true
  • Description: Enable/disable hint functionality.

pg_hint_plan.enable_hint_table

  • Type: Boolean
  • Default: false
  • Description: Enable hint table functionality.

pg_hint_plan.parse_messages

  • Type: Select
  • Options: error, warning, notice, info, log, debug
  • Default: info
  • Description: Control parsing message output.

pg_hint_plan.debug_print

  • Type: Select
  • Options: off, on, detailed, verbose
  • Default: off
  • Description: Enable debug output.

pg_hint_plan.message_level

  • Type: Select
  • Options: error, warning, notice, info, log, debug
  • Default: info
  • Description: Set message verbosity level.

Usage

Note

Unlike most PostgreSQL extensions, pg_hint_plan does not require CREATE EXTENSION to function. Once enabled through the dashboard, it's automatically loaded and available for use. You only need to run CREATE EXTENSION if you plan to use the hint table functionality (when pg_hint_plan.enable_hint_table is enabled).

After enabling the extension through the dashboard, you can optionally install it in your database for hint table functionality:

-- Only required if using hint tables
CREATE EXTENSION IF NOT EXISTS pg_hint_plan;

Then you can use hints in your queries:

/*+
    SeqScan(t1)
    IndexScan(t2 t2_pkey)
*/
SELECT * FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id;

External Documentation

For more detailed information about pg_hint_plan usage and hint syntax, see the official pg_hint_plan documentation.

Need help?

Get help from the PlanetScale Support team, or join our GitHub discussion board to see how others are using PlanetScale.