-
Notifications
You must be signed in to change notification settings - Fork 6
/
Copy pathoptimization_queries.sql
82 lines (74 loc) · 7.15 KB
/
optimization_queries.sql
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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
--
describe employees;
select *
from employees
limit 10;
-- index range scan
explain analyze
select *
from employees
where emp_no > 10;
# | EXPLAIN |
# +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Filter: (employees.emp_no > 10) (cost=30121.09 rows=149756) (actual time=0.066..116.182 rows=300024 loops=1)
# -> Index range scan on employees using PRIMARY (cost=30121.09 rows=149756) (actual time=0.065..90.215 rows=300024 loops=1)
# |
# +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# 1 row in set (0.15 sec)
explain analyze
select *
from employees
where emp_no > 10
and emp_no < 300;
# | EXPLAIN |
# +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Filter: ((employees.emp_no > 10) and (employees.emp_no < 300)) (cost=1.21 rows=1) (actual time=0.014..0.014 rows=0 loops=1)
# -> Index range scan on employees using PRIMARY (cost=1.21 rows=1) (actual time=0.013..0.013 rows=0 loops=1)
# |
# +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# 1 row in set (0.00 sec)
explain analyze
select *
from employees
where emp_no in (1, 2, 3, 4, 5, 6, 7);
# | EXPLAIN |
# +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Filter: (employees.emp_no in (1,2,3,4,5,6,7)) (cost=8.41 rows=7) (actual time=0.028..0.028 rows=0 loops=1)
# -> Index range scan on employees using PRIMARY (cost=8.41 rows=7) (actual time=0.027..0.027 rows=0 loops=1)
# |
# +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# 1 row in set (0.00 sec)
-- Inner hash join
explain analyze
select *
from employees,
departments
where emp_no > 10
and gender = 'F';
# EXPLAIN |
# +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Inner hash join (no condition) (cost=271090.92 rows=336951) (actual time=0.070..203.409 rows=1080459 loops=1)
# -> Filter: ((employees.gender = 'F') and (employees.emp_no > 10)) (cost=23465.26 rows=74878) (actual time=0.041..121.760 rows=120051 loops=1)
# -> Index range scan on employees using PRIMARY (cost=23465.26 rows=149756) (actual time=0.036..89.963 rows=300024 loops=1)
# -> Hash
# -> Index scan on departments using dept_name (cost=1.15 rows=9) (actual time=0.014..0.017 rows=9 loops=1)
# |
# +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# 1 row in set (0.33 sec)
-- Nested loop left join
explain analyze
select e.emp_no, s.salary
from employees e
left join salaries s on e.emp_no = s.emp_no
where e.emp_no > 10;
# | EXPLAIN |
# +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# | -> Nested loop left join (cost=451216.19 rows=2780761) (actual time=0.042..1762.936 rows=2844047 loops=1)
# -> Index scan on e using PRIMARY (cost=31007.31 rows=299512) (actual time=0.030..59.194 rows=300024 loops=1)
# -> Index lookup on s using PRIMARY (emp_no=e.emp_no) (cost=0.47 rows=9) (actual time=0.003..0.005 rows=9 loops=300024)
# |
# +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# 1 row in set (2.00 sec)
explain analyze
select *
from employees where emp_no = 10001 or emp_no = 10002;