Solved-Lab10: Clustered Index Performance- Solution

Description

In this lab, you are expected to study the di erence between clustered and unclustered indexes in PostgresSQL.

You can  nd material about clustered indexing from these links:

1. PostgresSQL documentation

For this assignment you need to download and unzip lab10.zip from iLearn (make sure you unzip it to /extra/$USER/cs166/data directory, because you can easily exceed you disk quota).

For this assignment we are using a short version of DB schema from Lab7 with only single table (part nyc). As usual create tables.sql script is used to create initial table structure and populate table with initial data.

The purpose of the lab is to study the performance di erence between clustered and unclustered indexes. To do that you need to create a secondary index and put corresponding SQL code to create indexes.sql.

Also you would need to write SQL for clustering part nyc table according to the index, you’ve just created.

Finally you have to write the query, which will be testing performance of clustered vs. unclus-tered index lookup. Note that to observe the di erence you need to use range query, which will return signi cant number of results (order of 100 thousands).

Use measure.sh to gather performance numbers.

1