· 8 years ago · Jan 15, 2018, 03:16 PM
1DROP TABLE if exists students;
2DROP TABLE if exists grades;
3
4CREATE TABLE students(
5 s_id integer NOT NULL PRIMARY KEY,
6 s_name text,
7 s_last_name text,
8 curr_year integer
9);
10
11CREATE TABLE grades(
12 s_id integer NOT NULL PRIMARY KEY,
13 course text,
14 c_year integer,
15 grade integer,
16 FOREIGN KEY (s_id) REFERENCES students
17);
18
19INSERT INTO students (s_id, s_name, s_last_name, curr_year)
20VALUES (1, 'Ariel', 'Sidis', 3);
21
22INSERT INTO students (s_id, s_name, s_last_name, curr_year)
23VALUES (2, 'Adi', 'Alon', 2);
24
25INSERT INTO students (s_id, s_name, s_last_name, curr_year)
26VALUES (3, 'Amir', 'Ben-Menahen', 1);
27
28INSERT INTO students (s_id, s_name, s_last_name, curr_year)
29VALUES (4, 'Naama', 'Levi', 2);
30
31INSERT INTO grades (s_id, course, c_year, grade)
32VALUES (1, 'DB', 2, 98);
33
34INSERT INTO grades (s_id, course, c_year, grade)
35VALUES (2, 'OS', 3, 90);
36
37INSERT INTO grades (s_id, course, c_year, grade)
38VALUES (3, 'DB', 2, 94);
39
40EXPLAIN ANALYZE
41 SELECT *
42 FROM students s JOIN grades gr
43 ON s.s_id = gr.s_id
44 WHERE curr_year > 0;
45
46
47
48CREATE INDEX student_details ON students(s_id, s_name, s_last_name);
49CREATE INDEX student_courses ON grades(s_id, course);
50
51EXPLAIN ANALYZE
52 SELECT *
53 FROM students s JOIN grades gr
54 ON s.s_id = gr.s_id
55 WHERE curr_year > 0;
56
57
58DROP INDEX student_details;
59DROP INDEX student_courses;
60DROP TABLE students CASCADE;
61DROP TABLE grades CASCADE;
62
63Hash Join (cost=23.50..51.74 rows=270 width=116) (actual time=0.039..0.050 rows=3 loops=1)
64 Hash Cond: (gr.s_id = s.s_id)
65 -> Seq Scan on grades gr (cost=0.00..21.30 rows=1130 width=44) (actual time=0.005..0.008 rows=3 loops=1)
66 -> Hash (cost=20.12..20.12 rows=270 width=72) (actual time=0.021..0.021 rows=4 loops=1)
67 Buckets: 1024 Batches: 1 Memory Usage: 9kB
68 -> Seq Scan on students s (cost=0.00..20.12 rows=270 width=72) (actual time=0.006..0.011 rows=4 loops=1)
69 Filter: (curr_year > 0)
70 Planning time: 0.147 ms
71 Execution time: 0.089 ms
72(9 rows)
73
74Nested Loop (cost=0.00..2.12 rows=1 width=116) (actual time=0.031..0.116 rows=3 loops=1)
75 Join Filter: (s.s_id = gr.s_id)
76 Rows Removed by Join Filter: 9
77 -> Seq Scan on students s (cost=0.00..1.05 rows=1 width=72) (actual time=0.012..0.018 rows=4 loops=1)
78 Filter: (curr_year > 0)
79 -> Seq Scan on grades gr (cost=0.00..1.03 rows=3 width=44) (actual time=0.003..0.009 rows=3 loops=4)
80 Planning time: 0.396 ms
81 Execution time: 0.170 ms