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)?