pg_hint_plan

1. Overview

pg_hint_plan is a PostgreSQL extension that lets you control the execution plan chosen by the optimizer using special comments called "hints" embedded in SQL statements. It is fully compatible with IvorySQL.

2. Installation

2.1. Prerequisites

pg_hint_plan must be loaded at server start. Add it to shared_preload_libraries in postgresql.conf:

shared_preload_libraries = 'pg_hint_plan'

2.2. Source Code Installation

Please ensure that IvorySQL 5.4 or above is installed and pg_config is available in PATH.

$ git clone https://github.com/ossc-db/pg_hint_plan.git
$ cd pg_hint_plan
$ make
$ sudo make install

3. Create Extension and Verify

Connect to the database with psql and execute the following commands:

ivorysql=# CREATE EXTENSION pg_hint_plan;
CREATE EXTENSION

ivorysql=# SELECT * FROM pg_available_extensions WHERE name = 'pg_hint_plan';
  name        | default_version | installed_version | comment
--------------+-----------------+-------------------+---------------------------------
 pg_hint_plan | 1.7.0           | 1.7.0             | Give optimizer hints

4. Usage Example

Hints are written as special comments at the start of a query. For example, to force a sequential scan on table t:

ivorysql=# EXPLAIN SELECT /*+ SeqScan(t) */ * FROM t;

See the pg_hint_plan documentation for the full list of supported hints.