Pagination: Find out which page an item is on (given primary key & sorting order)

Question

Let's say I do pagination 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 efficient way to find out on which page an article is if I

a) know the article_id

b) know the sorting is ORDER BY article_rating?

It needs to be efficient because I am going to do this type of query very often.

It would be even better if it not only spit out the page number, but also all articles on that page.

So, for example, if all articles are sorted by their rating and every ten of them are put on a different page, I want to figure out on which page the article with the ID 839 is.

I am using PostgreSQL 8.4 (I am willing to update, if necessary).

Thank you!

EDIT:

As pointed out in the comments below, my query should probably look 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 second query below, it is much better than this first one.

Assuming Postgres 9.0 or better, you have to make use of a window function to get a row_number onto each item. Then you divide the row_number of the particular article by items_per_page (and round) to get page number. The only efficiency improvement available is to at least not query the articles that come after the one in question. 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 question in comments. Yes, I just realized there is a much better way to do this. By running a count(*) instead we traverse only 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')

Normally we do not like OR clauses in WHERE clauses because they can degrade performance, but this one should be pretty safe because each clause ought to be optimizable if article_rating is indexed.