Pagination: find out on which page the item is located (taking into account the primary key and sort order)

Question

Let's say I paginate like this:

SELECT article_id, article_content FROM articles ORDER BY article_rating OFFSET (page - 1) * items_per_page LIMIT items_per_page; 

I have an index over (article_rating, article_id) .

My question is: what is the most effective way to find out which page the article is on if I

a) know article_id

b) know that sorting is ORDER BY article_rating?

It must be effective because I am going to make this type of request very often.

It would be even better if he not only spat out the page number, but also all the articles on this page.

So, for example, if all articles are sorted by their rating, and every ten of them are placed on another page, I want to find out on which page the article with ID 839 is located.

I am using PostgreSQL 8.4 (I am ready to upgrade if necessary).

Thanks!

EDIT:

As indicated in the comments below, my query should look something like this:

 SELECT article_id, article_content FROM articles ORDER BY article_rating, article_id OFFSET (page - 1) * items_per_page LIMIT items_per_page; 

Answer 1

EDIT See the second query below, it is much better than this first.

Assuming Postgres is 9.0 or higher, you should use the window function to get row_number for each element. You can then split the row_number line of a specific article into items_per_page (and round) to get the page number. The only increase in efficiency is at least not to request articles that appear after that. So you get something like this:

 Select ceiling(rowNumber/items_per_page) from ( SELECT article_id , article_content , row_number() over (order by article_rating, article_id) as rowNumber FROM articles where article_rating <= (select article_rating from articles where article_id = 'xxxx' ) ORDER BY article_rating, article_id ) x where article_id = 'xxxx' 

EDIT In response to a question in the comments. Yes, I just realized that there is a much better way to do this. Running the counter (*), we go only to the index.

 Select ceiling(count(*)/items_per_page) FROM articles where article_rating < (select article_rating from articles where article_id = 'xxxx' ) or ( article_rating = (select article_rating from articles where article_id = 'xxxx' ) and article_id <= 'xxxx') 

We usually don’t like OR clauses in WHERE clauses because they can degrade performance, but it should be pretty safe because every item should be optimized if article_rating is indexed.