MIT 6 830 - Problem Set 2 (8 pages)

Previewing pages 1, 2, 3 of 8 page document View the full content.
View Full Document

Problem Set 2



Previewing pages 1, 2, 3 of actual document.

View the full content.
View Full Document
View Full Document

Problem Set 2

41 views

Other


Pages:
8
School:
Massachusetts Institute of Technology
Course:
6 830 - Database Systems

Unformatted text preview:

6 830 Problem Set 2 1 6 830 Problem Set 2 2014 Assigned Monday Sep 22 2014 Due Wednesday Oct 8 2014 11 59 PM Submit to Stellar https stellar mit edu S course 6 fa14 6 830 homework The purpose of this problem set is to give you some practice with concepts related to schema design query planning and query processing Start early as this assignment is long Part 1 Query Plans and Cost Models In this part of the problem set you will examine query plans that PostgreSQL uses to execute queries and try to understand why it produces the plan it does for a certain query The data set you will use has the same schema as the MIMIC II dataset you used in problem set 1 Rather than running your own instance of SQLite however you will be connecting to our PostgreSQL server since PostgreSQL produces more interesting query plans than SQLite To understand what query plan is being used PostgreSQL includes the EXPLAIN command It prints the plan for a query including all of the physical operators and access methods being used For example the following SQL command displays the query plan for the SELECT EXPLAIN SELECT FROM d meditems WHERE label LIKE Folate In this problem you will find di and d tablename commands useful In order to use these you must install PostgreSQL command line client Make sure you use PostgreSQL 8 3 so that your results are consistent with the solutions Athena already has version 9 3 5 installed so you can simply ssh into athena dialup mit edu and get started In case you want to work on your own Debian Ubuntu machine you can install the postgresql client package by running the following command in your shell sudo apt get install postgresql client You can then connect to our PostgreSQL server by running the following command psql h vise3 csail mit edu U mimic2 d mimic2 Note that we currently only allow connections from MIT IPs so you will need to connect from on campus or by ssh ing into Athena To understand the output of EXPLAIN you will probably want to read the



View Full Document

Access the best Study Guides, Lecture Notes and Practice Exams

Loading Unlocking...
Login

Join to view Problem Set 2 and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Problem Set 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?