案例：建立合适的索引

现象描述

查询与销售部所有员工的信息：

  1. SELECT staff_id,first_name,last_name,employment_id,state_name,city 
  2. FROM staffs,sections,states,places 
  3. WHERE sections.section_name='Sales' 
  4. AND staffs.section_id = sections.section_id 
  5. AND sections.place_id = places.place_id 
  6. AND places.state_id = states.state_id 
  7. ORDER BY staff_id;

优化分析

在优化前，没有创建places.place_id和states.state_id索引，执行计划如下：

  1.                                             QUERY PLAN
  2. ---------------------------------------------------------------------------------------------------
  3.  Sort  (cost=129.74..131.18 rows=576 width=136)
  4.    Sort Key: staffs.staff_id
  5.    ->  Hash Join  (cost=70.54..103.33 rows=576 width=136)
  6.          Hash Cond: (states.state_id = places.state_id)
  7.          ->  Seq Scan on states  (cost=0.00..22.38 rows=1238 width=36)
  8.          ->  Hash  (cost=69.38..69.38 rows=93 width=108)
  9.                ->  Hash Join  (cost=42.41..69.38 rows=93 width=108)
  10.                      Hash Cond: (places.place_id = sections.place_id)
  11.                      ->  Seq Scan on places  (cost=0.00..21.67 rows=1167 width=40)
  12.                      ->  Hash  (cost=42.21..42.21 rows=16 width=76)
  13.                            ->  Hash Join  (cost=24.66..42.21 rows=16 width=76)
  14.                                  Hash Cond: (staffs.section_id = sections.section_id)
  15.                                  ->  Seq Scan on staffs  (cost=0.00..15.37 rows=537 width=76)
  16.                                  ->  Hash  (cost=24.59..24.59 rows=6 width=8)
  17.                                        ->  Seq Scan on sections  (cost=0.00..24.59 rows=6 width=8)
  18.                                              Filter: (section_name = 'Sales'::text)
  19. (16 rows)

建议在places.place_id和states.state_id列上建立2个索引，执行计划如下：

  1.                                                 QUERY PLAN
  2. -----------------------------------------------------------------------------------------------------------
  3.  Sort  (cost=119.76..121.20 rows=576 width=136)
  4.    Sort Key: staffs.staff_id
  5.    ->  Hash Join  (cost=70.14..93.35 rows=576 width=136)
  6.          Hash Cond: (staffs.section_id = sections.section_id)
  7.          ->  Seq Scan on staffs  (cost=0.00..15.37 rows=537 width=76)
  8.          ->  Hash  (cost=67.43..67.43 rows=217 width=68)
  9.                ->  Nested Loop  (cost=24.66..67.43 rows=217 width=68)
  10.                      ->  Hash Join  (cost=24.66..51.06 rows=35 width=40)
  11.                            Hash Cond: (places.place_id = sections.place_id)
  12.                            ->  Seq Scan on places  (cost=0.00..21.67 rows=1167 width=40)
  13.                            ->  Hash  (cost=24.59..24.59 rows=6 width=8)
  14.                                  ->  Seq Scan on sections  (cost=0.00..24.59 rows=6 width=8)
  15.                                        Filter: (section_name = 'Sales'::text)
  16.                      ->  Index Scan using states_state_id_idx on states  (cost=0.00..0.41 rows=6 width=36)
  17.                            Index Cond: (state_id = places.state_id)
  18. (15 rows)