Should database be denormalized if I need an index across multiple tables I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like:

SELECT * FROM song s
JOIN album a ON a.id = s.album_id
WHERE LOWER(CONCAT_WS(' ', album.name, song.name) = LOWER('Meteora Breaking The Habit')


I wish to create an index on the above type of search terms but it looks like I can't have an index across multiple tables. In this case, would it make sense to de-normalize the table and put the song &amp; album name in a single table or is there a better approach?

Reference Table:

CREATE TABLE album (
   id SERIAL PRIMARY KEY,
   name text NOT NULL
);

CREATE TABLE song (
   id SERIAL PRIMARY KEY,
   name text NOT NULL,
   album_id integer NOT NULL,
   CONSTRAINT album_id_fk FOREIGN KEY (album_id) REFERENCES album (id)
);


P.S. For the sake of understanding, I'm ignoring the fact that query can be song name first &amp; album name later. Also ignoring that query can contain band name too. The query sent by the client is of the form album.name + ' ' + song.name.
If you are getting the LOWER('Meteora Breaking The Habit') portion of the query from a source that it outside your control, then I would recommend keeping your song/album table structure (although remove album_id from song table), but include a 3rd table:

CREATE TABLE ALBUM_SONG (
  album_id,
  song_id,
  album_song_name -- this would be album.name || song.name
)


This is essentially a join table between the two for M-M relationships ('cause hey - the same song could be on multiple albums)..

Then you can index and query the join table on LOWER(album_song_name)