Re: number of rows returned from SELECT

On Fri, Jul 28, 2006 at 04:26:24PM -0700, Corey wrote:
> What's the most efficient way of determining the number of rows that
> _would_ be returned after/for a SELECT? Or is this impossible without...
> actually... _doing_ a SELECT?

Doing a SELECT is the only way to know for certain. You could use
EXPLAIN to get the planner's row count estimate but that number can
be inaccurate, especially if the statistics gathered by ANALYZE are
out of date.

> Basically this is for pagination purposes; given a SELECT statement, say,
> 'SELECT * from table LIMIT 10 OFFSET 10', I'd like to know how many total
> rows would be returned if LIMIT and OFFSET weren't used in the statement.

You could first do a "SELECT count(*) FROM ..." or you could use a
cursor and MOVE to the end to find out how many rows there are,
then use MOVE and FETCH to retrieve a certain number of rows starting
from a certain position. For large result sets these queries would
be inefficient because internally the server would visit all the
rows, but the count would be accurate.

--
Michael Fuhr