Access method hints v17
The following hints influence how the optimizer accesses relations to create the result set.
| Hint | Description |
|---|---|
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.
INDEX and NO_INDEX hints for the partitioned table internally expand to include the corresponding inherited child indexes and apply in later processing.
About the 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.
Example: Create a sample database and tables
The following steps create 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 results in the creation of 20 branches. Each branch has 100,000 accounts. The result is 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)
Example: FULL hint
The FULL hint forces 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)
Example: NO_INDEX hint
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)
Example: Tracing optimizer hints
You can obtain more detailed information than the EXPLAIN command provides about whether the planner used a hint. To do so, set the trace_hints configuration parameter as follows:
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)
Example: Hint ignored
If a hint is ignored, the INFO: [HINTS] line doesn't appear. This might indicate a syntax error or some other misspelling in the hint. 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)
Example: INDEX hint for the partitioned table
CREATE TABLE t_1384(col1 int, col2 int, col3 int) PARTITION BY RANGE(col1) (PARTITION p1 VALUES LESS THAN(500), PARTITION p2 VALUES LESS THAN(1000)); ALTER TABLE t_1384 ADD PRIMARY KEY(col1); CREATE INDEX idx1 ON t_1384(col2); CREATE INDEX idx2 ON t_1384(col1, col2); SET enable_hints = true; SET trace_hints TO on; -- Use primary index EXPLAIN (COSTS OFF) SELECT /*+ INDEX(s t_1384_pkey) */ * FROM t_1384 s WHERE col2 = 10; INFO: [HINTS] SeqScan of [s] rejected due to INDEX hint. INFO: [HINTS] Parallel SeqScan of [s] rejected due to INDEX hint. INFO: [HINTS] Index Scan of [s].[t_1384_p1_col1_col2_idx] rejected due to INDEX hint. INFO: [HINTS] Index Scan of [s].[t_1384_p1_col2_idx] rejected due to INDEX hint. INFO: [HINTS] Index Scan of [s].[t_1384_p1_pkey] accepted. INFO: [HINTS] SeqScan of [s] rejected due to INDEX hint. INFO: [HINTS] Parallel SeqScan of [s] rejected due to INDEX hint. INFO: [HINTS] Index Scan of [s].[t_1384_p2_col1_col2_idx] rejected due to INDEX hint. INFO: [HINTS] Index Scan of [s].[t_1384_p2_col2_idx] rejected due to INDEX hint. INFO: [HINTS] Index Scan of [s].[t_1384_p2_pkey] accepted. QUERY PLAN ----------------------------------------------------- Append -> Bitmap Heap Scan on t_1384_p1 s_1 Recheck Cond: (col2 = 10) -> Bitmap Index Scan on t_1384_p1_col2_idx Index Cond: (col2 = 10) -> Bitmap Heap Scan on t_1384_p2 s_2 Recheck Cond: (col2 = 10) -> Bitmap Index Scan on t_1384_p2_col2_idx Index Cond: (col2 = 10) (9 rows)