Skip site navigation (1) Skip section navigation (2)

Re: number of rows returned from SELECT

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Corey <corey(at)bitworthy(dot)net>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: number of rows returned from SELECT
Date: 2006-07-29 00:33:00
Message-ID: 20060729003300.GA21758@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Jim NasbyDate: 2006-07-29 00:37:20
Subject: Re: [NOVICE] The name of the game
Previous:From: Andrew HammondDate: 2006-07-29 00:16:14
Subject: Re: number of rows returned from SELECT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group