[Solved] Does this index in postgres make sense?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Does this index in postgres make sense?

Database type:

I made a very simple DB in Postgres containing these two tables:

Singer (ID, name, surname, birthday)

Song (ID, name, album, year, singer)

ID in table Singer is of type serial and it's its primary key.

ID in table Song is of type serial and it's its primary key.

The column "singer" in the Song table references ID in the Singer table

I want to retrieve all the songs of a given singer (let's say the singer having id = 3) and the singer's name and surname.

Therefore I need a query like this:

SELECT song.name, song.album, song.year, singer.name, singer.surname
FROM song, singer
WHERE song.singer = singer.id AND singer.id = 3

I created then an hash index on the "singer" column in the Song table for optimizing the join operation:

CREATE INDEX ON song USING hash (singer);

However, if I execute the query and use explain/analyze, my index seems not to be used.

So my question is: does an index like this make sense or not?

If not, is this because the column "singer" in the song table is a foreign key which references a primary key (id) in the singer table and Postgres automatically creates indexes on primary keys (so my custom index is not needed because basically it's quite the same thing)?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
CREATE INDEX singer_idx_id ON "singer" ("id");
CREATE INDEX song_idx_singer ON "song" ("singer");
The optimized query:
SELECT
        song.name,
        song.album,
        song.year,
        singer.name,
        singer.surname 
    FROM
        song,
        singer 
    WHERE
        song.singer = singer.id 
        AND singer.id = 3

Related Articles



* original question posted on StackOverflow here.