CODEWITHSUNDEEP
sqlpostgresqlpagination
Jon
Jon

Reputation: 40062

Can this postgres paging sql's performance be improved?

I have a HTTP client that wishes to simply send in an offset in a query string to handle paging eg http://foo.com/cars?offset=50.

In response they get a model that has the total item count and an array of data. {"totalitemcount":100, "data":[{"id":1,"name":"Porsche"},{"id":2, "name":"Ferrari"}]}

The SQL that I use to do this is below:

select count(items.id) over() as totalitemcount, items.* from ( 
      select * from cars 
      order by id
      ) as items 
limit 20 
offset 50

I'm not sure how performant this approach is and was wondering with the constraints above whether changes could be made to improve it?

Upvotes: 0

Views: 85

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 248030

The best way to do this is to first query like this:

SELECT * FROM cars 
ORDER BY id
LIMIT 20;

Then remember the last id.

The next page is retrieved with

SELECT * FROM cars 
WHERE id > [the id you remembered]
ORDER BY id
LIMIT 20;

And so on.

This way you don't get the total count of results, but you gain efficiency.

Do you really need the exact total number? If an approximation is enough, try the idea at the end of this blog post.

Upvotes: 1

Related Questions