- Home
- PostgreSQL
- PostgreSQL Indexes · A Practical Guide With Examples
PostgreSQL Indexes · A Practical Guide With Examples
By How To Speed Up Relational Database Tables
Our challenge today is speeding up our relational database tables using indices.
We downloaded some music data from an online API into Postgresql tables, and our lookup queries are slowing down due to the millions of records
When facing scaling pain you always need to evaluate your indexing strategy.
In comp sci there is a known trade-off between time and space complexities. That is, the fastest solutions often require the most space.
A prime example of this is database table indices. A table index is like the table of contents at the beginning of a book. It allows quick lookups by listing all of the sections and their page number. It takes a few more pages to print the table of contents but it saves the researchers time
A database table index is a quick reference for the database engine to find the records it needs. It takes a bit more space and extra time to do inserts but the lookup times are much faster. It is up to the discretion of the developer where to put the indexes for maximal effect while keeping it as limber as possible.
In this lesson we’ll look at two index strategies we can use in PostgreSQL, one for faster sorting, and the other for faster string lookups
\dt
\d+ "Artist";
Table "public.Artist"
Column | Type | Modifiers | Storage | Stats target | Description
---------------------+--------------------------+----------------------------------------+----------+--------------+-------------
name | character varying(1024) | not null default ''::character varying | extended | |
plays | integer | | plain | |
people | integer | | plain | |
summary | character varying(70000) | default NULL::character varying | extended | |
artPath | character varying(12) | default NULL::character varying | extended | |
LastInfoLookup | date | | plain | |
LastTopSongLookup | date | | plain | |
LastArtistSimLookup | date | | plain | |
LastTopAlbumLookup | date | | plain | |
Indexes:
"Artist_pkey" PRIMARY KEY, btree (name)
\d+ "Album";
Table "public.Album"
Column | Type | Modifiers | Storage | Stats target | Description
---------+-------------------------+----------------------------------------+----------+--------------+-------------
album | character varying(1024) | not null default ''::character varying | extended | |
artist | character varying(104) | not null default ''::character varying | extended | |
plays | integer | | plain | |
rank | integer | | plain | |
artPath | character varying(18) | default NULL::character varying | extended | |
Indexes:
"Album_pkey" PRIMARY KEY, btree (album, artist)
SELECT COUNT(*) FROM "Artist";
SELECT COUNT(*) FROM "Album";
SELECT * FROM "Artist" WHERE name like 'Bob%' order by plays desc limit 10;
SELECT * FROM "Artist" WHERE name like 'Bob%' order by people desc limit 10;
SELECT * FROM "Album" WHERE album like 'Bizarre%' order by plays desc limit 10;
SELECT * FROM "Album" WHERE album like 'Amazing%' order by rank asc limit 10;
CREATE INDEX album_album_index ON "Album" (album);
CREATE INDEX album_artist_index ON "Album" (artist);
CREATE INDEX artist_name_index ON "Artist" (name);
CREATE INDEX album_plays_index ON "Album" (plays DESC NULLS LAST);
CREATE INDEX album_rank_index ON "Album" (rank asc NULLS LAST);
CREATE INDEX artist_plays_index ON "Artist" (plays DESC NULLS LAST);
CREATE INDEX artist_followers_index ON "Artist" (people DESC NULLS LAST);
When adding indexes to your table, always ask yourself if it should be a unique index. (highlight)
A unique index means you can never have any duplicates for that field. It causes the system to check for duplicate values in the table each time data is added in which case it will generate an error.
Also, the typical database table of records with unique ids should be primary keys.
When designing your table, it’s usually a good idea to make the main id for each table the table’s primary key. Primary keys automatically create fast indexes and ensure uniqueness.
Note, in many cases such as association tables, you won’t want a primary key.
You can also have multicolumn indexes if you often lookup using the same two columns