---

1.17 表访问方法建议

表3-2 表访问方法建议

查询优化标记	描述
FULL(table)	执行全表顺序扫描
INDEX(table [ index ] [...])	在通过索引进行访问表
NO_INDEX(table [ index ] [...])	不能使用索引访问表

除此之外，也可以使用表3-10中列出的ALL_ROWS, FIRST_ROWS, 和FIRST_ROWS(n)这几种查询优化标记。

示例

由于示例程序中没有足够的数据来演示优化建议的效果，所以本章节中示例的剩余部分使用由Postgres Plus Advanced Server的dbserver\bin目录下pgbench程序创建的银行系统数据库。

在下面的步骤中首先创建一个数据库bank，在这个数据库上创建表accounts, branches, tellers, 和 history。 选项-s 5指定规模大小因子是5，这样创建5个分行，而每个分行有100000个账户，因此在表account中有500,000条记录，而在表branch中有5条记录。每个分行被分配10个出纳员，这样在表tellers中就有50条的记录。

注意，如果使用linux平台，那么要使用export命令。 而不是下面显示的SET PATH命令。

export PATH=/opt/EnterpriseDB/8.3/dbserver/bin:$PATH

下面的示例在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.

每个用户产生8个交易，则8个客户有总共80个交易。这样将在表history中插入80条记录。

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)

表定义如下所示

\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) |

EXPLAIN 命令显示了查询优化器选择的查询计划。在下面的示例中，aid是主键列，所以可以在索引account_key上进行索引搜索。

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)

如下所示，我们使用了FULL（表名）这个查询优化标记用来强迫进行全表顺序扫描，而不是使用索引进行访问：

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)

如下所示，NO_INDEX建议也强迫使用全表顺序扫描:

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)

除了使用前面示例的EXPLAIN命令外，与计划器是否使用查询优化标记相关的更详细信息可以通过设定配置参数client_min_messages和trace_hints来获得。

SET client_min_messages TO info;
SET trace_hints TO true;

当设置了上面提到的配置参数后，我们使用带有查询优化标记NO_INDEX的SELECT命令，来演示所产生的附加信息。

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)

需要注意的是，如果忽略了查询优化标记，那么将不会出现开头为INFO: [HINTS]的文本行。这可能提示语法错误，或者在查询优化标记中有其他的拼写错误。如在下面的示例中，索引名称拼写错误。

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