Re: Yet Another COUNT(*)...WHERE...question

--- Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
> Sorry I was not clear. Imagine an Amazon.com search results page. It
> has about 15 results on Page 1, then it shows "Page 1 of 190".

I don't think that amazon or google really need to give an accurate count in determining an
estimated number of pages...

Could you determine the number of pages quickly from postgresql:

[ row count estimate ] / [ number of rows you want per page]

The estimated row count is updated every time you vacuum your tables. And getting the estimate
takes very little time.

> To show each page, the query probably has a "LIMIT 15 OFFSET 0" for
> Page 1.

The "LIMIT 15 OFFSET 1500" technique can be a performance killer since offset does not use an
index.

Is is better to use the last entry of each page in the query for the next page, so you can write
your query this way:

SELECT *
FROM your_table
WHERE item_nbr > [: last item on previous page :]
ORDER BY item_nbr
LIMIT 15;

This method was discuss on the list a couple of months ago.

Regards,
Richard Broersma Jr.