Problem $1$: Indexing $(40\%)$

In this task, you will be asked to i$)$ select suitable indexes to speed up query performance and ii$)$ examine the query plan of an SQL query.

We are going to use a new database called flight attached in A$5.$zip. In the database, there is a single table, called flights. The following shows its schema:

flights $($fid$,$ year, month$\_$id$,$ day$\_$of$\_$month, day$\_$of$\_$week$\_$id$,$ carrier$\_$id$,$ flight$\_$num, origin$\_$city, origin$\_$state, dest$\_$city, dest$\_$state, departure$\_$delay, taxi$\_$out, arrival$\_$delay, canceled,

distance$)$

Note that this task only needs to use four attributes: carrier$\_$id$,$ origin$\_$city, actual$\_$time, and dest$\_$city.

Follow the steps to create and load the flights database:

with command line:

In your terminal, enter the A$5$ folder $($where the flight$\_$pg$.$sql locates$)$

Create a database named flight $($createdb $-$U $[$your username$]$ flight$)$

Run command: psql $-$U $[$your username$]-$d flight $-$f flight$\_$pg$.$sql

It may take a long time to insert all data.