Lecture 06¶

New IMDB Performance database¶

This is a variation of the IMDB database with keys defined. Note that this is a pretty big database! So if you run the below lines, please also remember to delete the imdb_perf_lecture afterwards to save space on your limited postgreSQL server.

Display indexes¶

The meta-command \d <relation> shows indexes maintained with the <relation> table.

To run a SQL command, you can also look in the system view pg_indexes (documentation 54.11):

Read the indexdef as: the Actor relation has an index named actor_pkey which is created on the attribute id. In this case, the attribute id is also the primary key of the Actor relation, hence why it has an index. More on why primary keys automatically generate indexes in a bit.

EXPLAIN ANALYZE¶

This query seems like it runs pretty quickly:

The PostgreSQL command EXPLAIN ANALYZE runs the execution plan of a statement and displays actual run time statistics. This is useful to understand what the query is actually doing.

By contrast, the below query on cast_info runs quite slowly. Why?

Explanation:

Run \d on cast_info and actors. cast_info does not have an index on movie_id!

$ psql -h localhost -d imdb_perf_lecture
imdb_perf_lecture=# \d actors
               Table "public.actors"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 name   | text    |           |          | 
Indexes:
    "actor_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "cast_info" CONSTRAINT "cast_info_person_id_fkey" FOREIGN KEY (person_id) REFERENCES actors(id)

imdb_perf_lecture=# \d cast_info
               Table "public.cast_info"
  Column   |  Type   | Collation | Nullable | Default 
-----------+---------+-----------+----------+---------
 person_id | integer |           |          | 
 movie_id  | integer |           |          | 
Foreign-key constraints:
    "cast_info_movie_id_fkey" FOREIGN KEY (movie_id) REFERENCES movies(id)
    "cast_info_person_id_fkey" FOREIGN KEY (person_id) REFERENCES actors(id)

Creating new Indexes¶

In the actors table, name is not a primary key. What kind of scan do you think the following query will produce?

We can manually create an index, even if it's not a primary key. Below, we create a multi-dimensional index just to show you the syntax:

This makes our original query much faster:

Why "Index Only" Scan? Well, SQL correctly identified that there are only two attributes in the Actor table, and both are located in the index. So we just need to search the index; we don't need to additionally fetch any records.

Exercise: Types of Scans¶

SQL automatically decides whether index scans are worth it. Sometimes, it decides to do a sequential scan instead, or even a bitmap heap scan.

The below exact match lookup produces an Index Scan:

This range lookup also produces an Index Scan:

However, the below range lookup produces a Sequential scan!

And this other range lookup produces a Bitmap Heap Scan??

• Index scan:
  • For each index key match, there is a page fetch.
  • If multiple index key matches all correspond to a single page, that single page may get fetched multiple times.matches on our query.
• Sequential scan:
  • Once each page is loaded in, all records on that page are scanned in sequence.
• Bitmap heap scan:
  • Pre-scans the index to identify the unique pages to visit, then sequentially scans the subset of pages
  • More here: stackoverflow

Takeaway:

• There is no guarantee that records in memory are sorted in the same way as records in the index.
• Therefore index lookups are effectively random lookups! Many random lookups are typically more expensive than many sequential lookups!

Other range lookups for your practice:

Cleanup¶

We drop the newly created index just to clean things up:

And we close the connection, then drop the database: