Re: How to get the total number of rows returned by query when using a cursor?

> > I'm implementing paging through search results using cursors. Is there a
> > better way to know total number of rows under a cursor than running a
> > separate COUNT(*) query? I think PostgreSQL is bound to know this number
> > after the first FETCH, isn't it?
>
> Why would you think that?  In general Postgres doesn't know the number
> of rows until it reaches the end of the query.

Sorry, I thought that because my query was sorted. For unsorted queries,
yes, there's usually no way to know until you reach the end.

> > On a side note, why queries using LIMIT are SO terribly slow,
> compared to
> > cursors and sometimes even ones without LIMIT?
>
> Generally they're not particularly slow.  Perhaps you should show us the
> EXPLAIN ANALYZE results for your problem case.

I did just that, but the plan was too complicated for me to comprehend  ;)
The interesting thing is that some queries are tens (!) of times slower with
LIMIT that without. These contain a number of joins on subselects. If you're
interested, I'll post examples.

Best regards,
Egor Shipovalov.