Re: Performance of count(*)

Brian Hurt wrote:
>> One of our biggest single problems is this very thing. It's not a
>> Postgres problem specifically, but more embedded in the idea of a
>> relational database: There are no "job status" or "rough estimate of
>> results" or "give me part of the answer" features that are critical to
>> many real applications.
>>
> For the "give me part of the answer", I'm wondering if cursors wouldn't
> work (and if not, why not)?

There is no mechanism in Postgres (or any RDB that I know of) to say, "Give me rows 1000 through 1010", that doesn't also execute the query on rows 1-1000. In other words, the RDBMS does the work for 1010 rows, when only 10 are needed -- 100 times more work than is necessary.

Limit/Offset will return the correct 10 rows, but at the cost of doing the previous 1000 rows and discarding them.

Web applications are stateless. To use a cursor, you'd have to keep it around for hours or days, and create complex "server affinity" code to direct a user back to the same server of your server farm (where that cursor is being held), on the chance that the user will come back and ask for rows 1000 through 1010, then a cursor isn't up to the task.

Craig