Access method hints v14

Suggest edits

The following hints influence how the optimizer accesses relations to create the result set.

HintDescription
FULL(table)Perform a full sequential scan on table.
INDEX(table [ index ] [...])Use index on table to access the relation.
NO_INDEX(table [ index ] [...])Don't use index on table to access the relation.

In addition, you can use the ALL_ROWS, FIRST_ROWS, and FIRST_ROWS(n) hints.

Examples

The sample application doesn't have enough data to show the effects of optimizer hints. Thus the remainder of these examples use a banking database created by the pgbench application located in the EDB Postgres Advanced Server bin subdirectory.

This example creates a database named bank populated by the tables pgbench_accounts, pgbench_branches, pgbench_tellers, and pgbench_history. The –s 20 option specifies a scaling factor of 20, which creates 20 branches, each with 100,000 accounts. This results in a total of 2,000,000 rows in the pgbench_accounts table and 20 rows in the pgbench_branches table. Ten tellers are assigned to each branch, resulting in a total of 200 rows in the pgbench_tellers table.

The following initializes the pgbench application in the bank database.

createdb -U enterprisedb bank
CREATE DATABASE

pgbench -i -s 20 -U enterprisedb bank

NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 2000000 tuples (5%) done (elapsed 0.11 s, remaining 2.10 s)
200000 of 2000000 tuples (10%) done (elapsed 0.22 s, remaining 1.98 s)
300000 of 2000000 tuples (15%) done (elapsed 0.33 s, remaining 1.84 s)
400000 of 2000000 tuples (20%) done (elapsed 0.42 s, remaining 1.67 s)
500000 of 2000000 tuples (25%) done (elapsed 0.52 s, remaining 1.57 s)
600000 of 2000000 tuples (30%) done (elapsed 0.62 s, remaining 1.45 s)
700000 of 2000000 tuples (35%) done (elapsed 0.73 s, remaining 1.35 s)
800000 of 2000000 tuples (40%) done (elapsed 0.87 s, remaining 1.31 s)
900000 of 2000000 tuples (45%) done (elapsed 0.98 s, remaining 1.19 s)
1000000 of 2000000 tuples (50%) done (elapsed 1.09 s, remaining 1.09 s)
1100000 of 2000000 tuples (55%) done (elapsed 1.22 s, remaining 1.00 s)
1200000 of 2000000 tuples (60%) done (elapsed 1.36 s, remaining 0.91 s)
1300000 of 2000000 tuples (65%) done (elapsed 1.51 s, remaining 0.82 s)
1400000 of 2000000 tuples (70%) done (elapsed 1.65 s, remaining 0.71 s)
1500000 of 2000000 tuples (75%) done (elapsed 1.78 s, remaining 0.59 s)
1600000 of 2000000 tuples (80%) done (elapsed 1.93 s, remaining 0.48 s)
1700000 of 2000000 tuples (85%) done (elapsed 2.10 s, remaining 0.37 s)
1800000 of 2000000 tuples (90%) done (elapsed 2.23 s, remaining 0.25 s)
1900000 of 2000000 tuples (95%) done (elapsed 2.37 s, remaining 0.12 s)
2000000 of 2000000 tuples (100%) done (elapsed 2.48 s, remaining 0.00 s)
vacuum...
set primary keys...
done.

A total of 500,00 transactions are then processed. These transactions populate the pgbench_history table with 500,000 rows.

pgbench -U enterprisedb -t 500000 bank

starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 20
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 500000
number of transactions actually processed: 500000/500000
latency average: 0.000 ms
tps = 1464.338375 (including connections establishing)
tps = 1464.350357 (excluding connections establishing)

The following are the table definitions:

\d pgbench_accounts

   Table "public.pgbench_accounts"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 aid      | integer       | not null
 bid      | integer       |
 abalance | integer       |
 filler   | character(84) |
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

\d pgbench_branches

   Table "public.pgbench_branches"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 bid      | integer       | not null
 bbalance | integer       |
 filler   | character(88) |
Indexes:
    "pgbench_branches_pkey" PRIMARY KEY, btree (bid)

\d pgbench_tellers

   Table "public.pgbench_tellers"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 tid      | integer       | not null
 bid      | integer       |
 tbalance | integer       |
 filler   | character(84) |
Indexes:
    "pgbench_tellers_pkey" PRIMARY KEY, btree (tid)

\d pgbench_history

          Table "public.pgbench_history"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 tid    | integer                     |
 bid    | integer                     |
 aid    | integer                     |
 delta  | integer                     |
 mtime  | timestamp without time zone |
 filler | character(22)               |

The EXPLAIN command shows the plan selected by the query planner. In this example, aid is the primary key column, so an indexed search is used on index pgbench_accounts_pkey:

EXPLAIN SELECT * FROM pgbench_accounts WHERE aid = 100;

                                       QUERY PLAN
-----------------------------------------------------------------------------
------------------
Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..8.45
rows=1 width=97)
   Index Cond: (aid = 100)
(2 rows)

The FULL hint is used to force a full sequential scan instead of using the index:

EXPLAIN SELECT /*+ FULL(pgbench_accounts) */ * FROM pgbench_accounts WHERE
aid = 100;

                          QUERY PLAN
---------------------------------------------------------------------
Seq Scan on pgbench_accounts  (cost=0.00..58781.69 rows=1 width=97)
  Filter: (aid = 100)
(2 rows)

The NO_INDEX hint forces a parallel sequential scan instead of using the index:

EXPLAIN SELECT /*+ NO_INDEX(pgbench_accounts pgbench_accounts_pkey) */ *
FROM pgbench_accounts WHERE aid = 100;

                                  QUERY PLAN
-----------------------------------------------------------------------------
-------
 Gather  (cost=1000.00..45094.80 rows=1 width=97)
   Workers Planned: 2
   ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..44094.70 rows=1
   width=97)
           Filter: (aid = 100)
(4 rows)

In addition to using the EXPLAIN command, you can get more detailed information about whether a hint was used by the planner by setting the trace_hints configuration parameter:

SET trace_hints TO on;

The SELECT command with the NO_INDEX hint shows the additional information produced when you set the trace_hints configuration parameters:

EXPLAIN SELECT /*+ NO_INDEX(pgbench_accounts pgbench_accounts_pkey) */ *
FROM pgbench_accounts WHERE aid = 100;

INFO:  [HINTS] Index Scan of [pgbench_accounts].[pgbench_accounts_pkey]
rejected due to NO_INDEX hint.
                                  QUERY PLAN
-----------------------------------------------------------------------------
-------
 Gather  (cost=1000.00..45094.80 rows=1 width=97)
   Workers Planned: 2
   ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..44094.70 rows=1
width=97)
         Filter: (aid = 100)
(4 rows)

If a hint is ignored, the INFO: [HINTS] line doesn't appear. This might be indicate a syntax error or some other misspelling in the hint as shown in this example. The index name is misspelled.

EXPLAIN SELECT /*+ NO_INDEX(pgbench_accounts pgbench_accounts_xxx) */ * FROM
pgbench_accounts WHERE aid = 100;

                                       QUERY PLAN
-----------------------------------------------------------------------------
------------------
Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..8.45
rows=1 width=97)
   Index Cond: (aid = 100)
(2 rows)
← Prev

Default optimization modes

Next →

Specifying a join order