CSc 417 Assignment 5, EXPLAIN

The timetable database

 On cs-linux, The database named timetable has one table (relation), named courses. It is filled with some data taken from the Bishop's website, courses in CSC, MAT, FIN and PHY, for Fall 2005, with some of this fall's courses added. (Why? I was lazy). It also has courses for students and enroll, the schemas are as follows: (Primary keys underlined)
	Courses (course, credits, days, time, room, title, prof, remarks)
        Students(name, given, linux, major, email)
	Enroll  (linux, course, grade)
There are (unique) indexes on the primary keys. There is also an index on Students(name), which is the family name.
You may insert yourself in the Students table if you wish, and then also enroll in some courses.

The students table has about 1000 rows. The others are short enough that it is unlikely that an index will actually be used.

Query plans using EXPLAIN

explain select ....  will show you the query plan for any query. From it you can deduce the relational algebra "tree", the first operations will be at the bottom, and the last one at the top of the indented list. It will also show you what algorithm is chosen to do something.
Please note how joins are processed. Also note whether indexes are used, where they exist.
"Scan" means get rows from a table. Sequential is the fastest, Index scan will get the rows in order by primary key.
Finally, there is an estimate of "cost" on each line. The top line will have total cost. (Projection operations are not explicitly shown.)

What to do:

Observe the explain of the following queries, and write a report on what you found. Discuss why you think each algorithm was chosen. You can use any name or account code you wish, we just want to see the effect of selection, and whether an index is used. If you find the same query plans in some of these, you may omit the duplicates from your report.

 SELECT * FROM
  1. Students WHERE name='Jensen'
  2. Students WHERE given='Lin'
  3. Students CROSS JOIN Courses
  4. Students NATURAL JOIN Enroll
  5. Courses NATURAL JOIN Enroll
  6. Students NATURAL JOIN Enroll WHERE linux='jensen'
  7. Courses NATURAL JOIN Enroll WHERE linux='jensen'
  8. Students NATURAL JOIN Enroll WHERE course='CSC417A01'
  9. SELECT DISTINCT given, name FROM Student
  10. Other queries of your choosing...
We would like to see use of index, Nested loops, Hashing, and Sorting.
You can do this using the psql monitor, or pgadmin3 (desktop icon). If you need instructions on psql, or SQL syntax, see the instructions I gave for CSC207.
For pgadmin3, you need to specify:
Once connected, you still have to navigate to Databases, Timetable, then schema public, then tables. An advantage here is that there is a nice graphical display of EXPLAIN, in addition to the text output.