Menu
In a postgresql database, with slick 3, what's the best way to have pagination?
855
To impove pagination we should have indexes for fields, which we are using in ORDER BY: The same index can be using in WHERE and ORDER BY. As you can see, fetching the next page is also faster. But in order to select, for example, the 10 page (10 per page), PostgreSQL should select 100 records and make offset 90 of selected rows.
The same index can be using in WHERE and ORDER BY. As you can see, fetching the next page is also faster. But in order to select, for example, the 10 page (10 per page), PostgreSQL should select 100 records and make offset 90 of selected rows.
Fast pagination on PostgreSQL UPDATE: 2014-11-19:Some people asked me how much creating an index on event(channel,id)helps. Answer: not much. During the implementation of IRCBrowseI discovered that Postgres’s built-in offset is not very fast. Here are the characteristics of my data:
But in order to select, for example, the 10 page (10 per page), PostgreSQL should select 100 records and make offset 90 of selected rows. To remove the rows from previous pages we can use WHERE filter instead of OFFSET. In this case neither the size of the base set (*) nor the fetched page number affects the response time.
You can use take and drop methods on TableQuery objects. They will be translated to limit and offset in the resulting SQL query:
val users: TableQuery[UsersTable] = UsersTable.query
val firstPartOfUsers = users.drop(0).take(25).result
val secondPartOfUsers = users.drop(25).take(25).result
Those two actions will be translated to the following SQL queries:
select "name", "email", "id" from "users" limit 25 offset 0
select "name", "email", "id" from "users" limit 25 offset 25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
