How to speed up fetching with pagination in PostgreSQL?

Question

PostgreSQL

I want to search for a song by title with pagination. There are two tables: one table with the name of the song, the second - with the name of the artist. The first table has 20 million rows, the second table has 1.5 million rows.
Approximately look like this:

create table recording (
  "id" serial primary key,
  "name" varchar,
  "artist_credit" serial REFERENCES artist_credit (id),
);
create table artist_credit (
  "id" serial primary key,
  "name" varchar
);

As a result, I want to get a list with the name of the song + the name of the artist. I thought to implement pagination as dynamic data loading, which will be triggered by scrolling or by clicking on a button, although if page-by-page pagination works faster, I don’t mind. In total, the most difficult moment is fast fetching + pagination. Now the request looks like this:

self.searchGlobal = function (obj) {

return db.query(
"SELECT 
recording.id  AS \"recordingId\", artist_credit.name AS \"artistName\", recording.name AS \"trackName\",  
FROM recording 
INNER JOIN artist_credit ON recording.artist_credit = artist_credit.id 
WHERE recording.name ~ ${regex} AND recording.id > ${index} 
ORDER BY recording.id 
LIMIT ${limit};", obj);
  };

The idea was to get the id of the last selected song and the next request would already look for songs with the id more so that there were no repetitions and that only those songs that did not exist were loaded. As a result, the request takes about 10 seconds. If you remove ORDER BY about 300ms , but then it is not clear how to make pagination faster.
While writing, I came up with another way: what if we send a list of all ids of already selected songs from the client and write something like this in the request to the database:
WHERE id NOT IN [array]
Probably it will be faster? The most important thing is how such things are implemented in a human way? Thanks

Answer 1

so you don’t have sorting or pagination slowing down, but searching by regexp. which is not indexed. replace it with full-text search (tsvector and so on) or indeed with some external indexer, such as solra or sphinx, translate.