How to speed up a sample with pagination?

Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
I must say, I'm not strong nuances, know sql in General.
Now the pagination is done via standard remembering the last ID. Something like this:
SELECT * FROM recording WHERE > 0 AND recording.artist_id = '269608' ORDER BY LIMIT 10

The request is 5-10 seconds. The query plan here
As you can see most of the time pulls the index scan, I do not understand why.
If you remove the ORDER BY it all the more fun
Have noticed that if aydishnik artist not 269608 and less, for example 500, the query is very fast. And the more aydishnik, the longer the query is executed. Is this normal? The impression is that all the ID are converted in order until you reach the desired.
In General, as to be without ORDER BY, if needed and speed (primarily) and data filtering?
The table contains 18mn records, but I don't think it's just still daunting schema for DBMS (?)

Describe the task completely.
Need to get the tracks on aydishnik artist. The tracks are in the table recording artists in the artist table. But the tables are not related directly, but only through the other two artist_credit_name and artist_credit.
The connection next: <--> artist_credit_name .artist, artist_credit_name .artist_credit <--> <--> recording.artist_credit
The whole scheme here
The full query looks like this:
SELECT AS "recordingId", AS, "trackName", "artist".name AS "artistName" FROM artist INNER JOIN artist_credit_name ON = artist_credit_name.artist INNER JOIN artist_credit_name artist_credit ON.artist_credit = INNER JOIN recording ON = recording.artist_credit WHERE = $(artistId) AND > $(index) ORDER BY LIMIT $(limit)

It turns out that one `` can have multiple ``. So I tried to rewrite the query so that first select all `` for a given artist and then them using the `WHERE IN` select tracks, acceleration is about 30% (although it may be error), but the result is still not the need.
Indexes on tables :
recording: id (PK),
artist_credit: id (PK),
artist_credit_name: id (PK), artist(FK),
artist: id (PK)
Can add an index on the field `recording.artist_credit` ? Don't know if you can add indexes on foreign keys?

UPD#2 Added index on `recording.artist_credit`, now the query goes fast
by | 26 views

2 Answers

0 like 0 dislike
The table contains 18mn records, but I don't think it's just still daunting schema for DBMS (?)

this is server dependent. in addition to the pk index is? or tried to add, for example, unique id + artist_id?
why check " > 0", auto increment you don't use?
why sort by id if values are still recorded in this manner due to auto increment.
0 like 0 dislike
You have a problem in that Postgres first sorts ALL, and then takes the limit.
Try to add something like: AND < $(index+dofiga) where a lot is limit + the maximum conceivable size of the "holes" in the list of primary keys.

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
110,608 questions
257,187 answers
40,796 users