SQL Query For Index/primary Key Ordinal

In our on-line contest system, there is a frequently changing table standings with integer columns (user_id, score). Both are indexed with a unique constraint. Two kinds of queries

Solution 1:

With a regular table, there is not much you can do in PostgreSQL 9.1. count() results in a table scan, because indexes do not have visibility information. To verify the rows are not deleted in the meantime, PostgreSQL has to visit the table.

If the table is read-only (or rarely updated), you could add a row number to the table. Then a query like:

SELECT rownumber+1
FROM   standings
WHERE  score < ?
ORDER  BY score DESC
LIMIT  1;

With an index:

CREATE INDEX standings_score_idx ON standings (score DESC);

Would get the result almost instantly. However, that's not an option for a table with write load for obvious reasons. So not for you.

---

The good news: one of the major new features of the upcoming PostgreSQL 9.2 is just right for you: "Covering index" or "index-only scan". I quote the 9.2 release notes here:

Allow queries to retrieve data only from indexes, avoiding heap access (Robert Haas, Ibrar Ahmed, Heikki Linnakangas, Tom Lane)

This is often called "index-only scans" or "covering indexes". This is possible for heap pages with exclusively all-visible tuples, as reported by the visibility map. The visibility map was made crash-safe as a necessary part of implementing this feature.

This blog post by Robert Haas has more details how this affects count performance. It helps performance even with a WHERE clause, like in your case.