Help Center/ GaussDB/ Developer Guide(Distributed_3.x)/ SQL Optimization/ Optimization Cases/ Case: Creating an Appropriate Index
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Case: Creating an Appropriate Index

Symptom

Query the information about all personnel in the sales department.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
-- Create a table.
CREATE TABLE staffs (staff_id NUMBER(6) NOT NULL, first_name VARCHAR2(20), last_name VARCHAR2(25), employment_id VARCHAR2(10), section_id NUMBER(4), state_name VARCHAR2(10), city VARCHAR2(10));
CREATE TABLE sections(section_id NUMBER(4), place_id NUMBER(4), section_name VARCHAR2(20));
CREATE TABLE states(state_id NUMBER(4));
CREATE TABLE places(place_id NUMBER(4), state_id  NUMBER(4));
-- Query before optimization.
EXPLAIN SELECT staff_id,first_name,last_name,employment_id,state_name,city 
FROM staffs,sections,states,places 
WHERE sections.section_name='Sales' 
AND staffs.section_id = sections.section_id 
AND sections.place_id = places.place_id 
AND places.state_id = states.state_id 
ORDER BY staff_id;
-- Query after optimization.
CREATE INDEX loc_id_pk ON places(place_id);
CREATE INDEX state_c_id_pk ON states(state_id);

EXPLAIN SELECT staff_id,first_name,last_name,employment_id,state_name,city 
FROM staffs,sections,states,places 
WHERE sections.section_name='Sales' 
AND staffs.section_id = sections.section_id 
AND sections.place_id = places.place_id 
AND places.state_id = states.state_id 
ORDER BY staff_id;

Optimization Analysis

The original execution plan is as follows before creating the places.place_id and states.state_id indexes:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
 id |                          operation                           | E-rows | E-width | E-costs
----+--------------------------------------------------------------+--------+---------+---------
  1 | ->  Streaming (type: GATHER)                                 |      2 |     254 | 54.08
  2 |    ->  Sort                                                  |      2 |     254 | 53.90
  3 |       ->  Nested Loop (4,5)                                  |      2 |     254 | 53.88
  4 |          ->  Seq Scan on staffs                              |     20 |     266 | 13.13
  5 |          ->  Materialize                                     |      4 |      12 | 40.37
  6 |             ->  Streaming(type: BROADCAST)                   |      4 |      12 | 40.36
  7 |                ->  Nested Loop (8,9)                         |      2 |      12 | 40.20
  8 |                   ->  Seq Scan on states                     |     20 |      12 | 13.13
  9 |                   ->  Materialize                            |      2 |      24 | 26.69
 10 |                      ->  Streaming(type: REDISTRIBUTE)       |      2 |      24 | 26.68
 11 |                         ->  Nested Loop (12,14)              |      2 |      24 | 26.57
 12 |                            ->  Streaming(type: REDISTRIBUTE) |      1 |      24 | 13.28
 13 |                               ->  Seq Scan on sections       |      1 |      24 | 13.16
 14 |                            ->  Seq Scan on places            |     20 |      24 | 13.13
(14 rows)

         Predicate Information (identified by plan id)
----------------------------------------------------------------
   3 --Nested Loop (4,5)
         Join Filter: (sections.section_id = staffs.section_id)
   7 --Nested Loop (8,9)
         Join Filter: (places.state_id = states.state_id)
  11 --Nested Loop (12,14)
         Join Filter: (sections.place_id = places.place_id)
  13 --Seq Scan on sections
         Filter: ((section_name)::text = 'Sales'::text)
(8 rows)

The optimized execution plan is as follows (two indexes have been created on the places.place_id and states.state_id columns):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
 id |                              operation                              | E-rows | E-width | E-costs
----+---------------------------------------------------------------------+--------+---------+---------
  1 | ->  Streaming (type: GATHER)                                        |      2 |     254 | 42.26
  2 |    ->  Sort                                                         |      2 |     254 | 42.08
  3 |       ->  Nested Loop (4,5)                                         |      2 |     254 | 42.06
  4 |          ->  Seq Scan on staffs                                     |     20 |     266 | 13.13
  5 |          ->  Materialize                                            |      4 |      12 | 28.55
  6 |             ->  Streaming(type: BROADCAST)                          |      4 |      12 | 28.54
  7 |                ->  Nested Loop (8,13)                               |      2 |      12 | 28.38
  8 |                   ->  Streaming(type: REDISTRIBUTE)                 |      2 |      24 | 21.66
  9 |                      ->  Nested Loop (10,12)                        |      2 |      24 | 21.56
 10 |                         ->  Streaming(type: REDISTRIBUTE)           |      1 |      24 | 13.28
 11 |                            ->  Seq Scan on sections                 |      1 |      24 | 13.16
 12 |                         ->  Index Scan using loc_id_pk on places    |      1 |      24 | 8.27
 13 |                   ->  Index Only Scan using state_c_id_pk on states |      1 |      12 | 3.35
(13 rows)

         Predicate Information (identified by plan id)
----------------------------------------------------------------
   3 --Nested Loop (4,5)
         Join Filter: (sections.section_id = staffs.section_id)
  11 --Seq Scan on sections
         Filter: ((section_name)::text = 'Sales'::text)
  12 --Index Scan using loc_id_pk on places
         Index Cond: (place_id = sections.place_id)
  13 --Index Only Scan using state_c_id_pk on states
         Index Cond: (state_id = places.state_id)
(8 rows)
Parent topic: Optimization Cases