CSC 350

Database Systems

Lab 11 - Query Plans

You can use the EXPLAIN keyword to see the plan postgres will use to execute a query. (See the Postgres EXPLAIN documention for more details.)

Assignment

  1. For each of the queries you wrote to answer the questions in Assignment 03, use the EXPLAIN command to calculate the cost of those queries.

  2. In each query plan, look at the most expensive type of operation. Predict whether adding an index to the table for one or more of the columns would increase performance.

  3. For each of your predictions, add the index to the table, and test your prediction. If the cost was improved, explain why and by how much.

Note: Remember to remove any indicies you add in step 3 before testing the next query.

This page contains a good overview of the syntax for creating and removing an index.

Submission

Submit this assignment as a pdf to mySVU. Do not submit an image.