The following hints influence how the optimizer accesses relations to create the result set.
Table 3-11 Access Method Hints
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 ] [...]) |
Do not use index on table to access the relation. |
In addition, the ALL_ROWS, FIRST_ROWS, and FIRST_ROWS(n) hints of Table 3-10 can be used.
Examples
The sample application does not have sufficient data to illustrate the effects of optimizer hints so the remainder of the examples in this section will use a banking database created by the pgbench application located in the Postgres Plus Advanced Server dbserver\bin subdirectory.
The following steps create a database named, bank, populated by the tables, accounts, branches, tellers, and history. The –s 5 option specifies a scaling factor of five which results in the creation of five branches, each with 100,000 accounts, resulting in a total of 500,000 rows in the accounts table and five rows in the branches table. Ten tellers are assigned to each branch resulting in a total of 50 rows in the tellers table.
Note, if using Linux use the export command instead of the SET PATH command as shown below.
export PATH=/opt/EnterpriseDB/8.3/dbserver/bin:$PATH
The following example was run in Windows.
SET PATH=C:\EnterpriseDB\8.3\dbserver\bin;%PATH% createdb -U enterprisedb bank CREATE DATABASE pgbench -i -s 5 -U enterprisedb -d bank creating tables... 10000 tuples done. 20000 tuples done. 30000 tuples done. . . . 470000 tuples done. 480000 tuples done. 490000 tuples done. 500000 tuples done. set primary key... vacuum...done.
Ten transactions per client are then processed for eight clients for a total of 80 transactions. This will populate the history table with 80 rows.
pgbench –U enterprisedb –d bank –c 8 –t 10 . . . transaction type: TPC-B (sort of) scaling factor: 5 number of clients: 8 number of transactions per client: 10 number of transactions actually processed: 80/80 tps = 6.023189 (including connections establishing) tps = 7.140944 (excluding connections establishing)
The table definitions are shown below:
\d accounts Table "public.accounts" Column | Type | Modifiers ----------+---------------+----------- aid | integer | not null bid | integer | abalance | integer | filler | character(84) | Indexes: "accounts_pkey" PRIMARY KEY, btree (aid) \d branches Table "public.branches" Column | Type | Modifiers ----------+---------------+----------- bid | integer | not null bbalance | integer | filler | character(88) | Indexes: "branches_pkey" PRIMARY KEY, btree (bid) \d tellers Table "public.tellers" Column | Type | Modifiers ----------+---------------+----------- tid | integer | not null bid | integer | tbalance | integer | filler | character(84) | Indexes: "tellers_pkey" PRIMARY KEY, btree (tid) \d history Table "public.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 the following example, aid is the primary key column, so an indexed search is used on index, accounts_pkey.
EXPLAIN SELECT * FROM accounts WHERE aid = 100; QUERY PLAN ------------------------------------------------------------------------------- Index Scan using accounts_pkey on accounts (cost=0.00..8.32 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 as shown below:
EXPLAIN SELECT /*+ FULL(accounts) */ * FROM accounts WHERE aid = 100; QUERY PLAN ------------------------------------------------------------- Seq Scan on accounts (cost=0.00..14461.10 rows=1 width=97) Filter: (aid = 100) (2 rows)
The NO_INDEX hint also forces a sequential scan as shown below:
EXPLAIN SELECT /*+ NO_INDEX(accounts accounts_pkey) */ * FROM accounts WHERE aid = 100; QUERY PLAN ------------------------------------------------------------- Seq Scan on accounts (cost=0.00..14461.10 rows=1 width=97) Filter: (aid = 100) (2 rows)
In addition to using the EXPLAIN command as shown in the prior examples, more detailed information regarding whether or not a hint was used by the planner can be obtained by setting the client_min_messages and trace_hints configuration parameters as follows:
SET client_min_messages TO info; SET trace_hints TO true;
The SELECT command with the NO_INDEX hint is repeated below to illustrate the additional information produced when the aforementioned configuration parameters are set.
EXPLAIN SELECT /*+ NO_INDEX(accounts accounts_pkey) */ * FROM accounts WHERE aid = 100; INFO: [HINTS] Index Scan of [accounts].[accounts_pkey] rejected because of NO_INDEX hint. INFO: [HINTS] Bitmap Heap Scan of [accounts].[accounts_pkey] rejected because of NO_INDEX hint. QUERY PLAN ------------------------------------------------------------- Seq Scan on accounts (cost=0.00..14461.10 rows=1 width=97) Filter: (aid = 100) (2 rows)
Note that if a hint is ignored, the INFO: [HINTS] line will not appear. This may be an indication that there was a syntax error or some other misspelling in the hint as shown in the following example where the index name is misspelled.
EXPLAIN SELECT /*+ NO_INDEX(accounts accounts_xxx) */ * FROM accounts WHERE aid = 100; QUERY PLAN ------------------------------------------------------------------------------- Index Scan using accounts_pkey on accounts (cost=0.00..8.32 rows=1 width=97) Index Cond: (aid = 100) (2 rows)
