Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cursor estimated row count

Cursor estimated row count

From
Michael Fuhr
Date:
An occasionally asked question is "How can I find out how many rows
a cursor will return?" to which the answer is "Fetch them all."  But
what about a way to get the planner's estimate?  Would anybody find
that useful?  Does the code below look close to being correct?

test=> EXPLAIN SELECT * FROM pg_class;                        QUERY PLAN                         
------------------------------------------------------------Seq Scan on pg_class  (cost=0.00..6.88 rows=188 width=163)
(1 row)

test=> BEGIN;
BEGIN
test=> DECLARE curs CURSOR FOR SELECT * FROM pg_class;
DECLARE CURSOR
test=> SELECT cursor_plan_rows('curs');cursor_plan_rows 
------------------             188
(1 row)

#include "postgres.h"
#include "fmgr.h"

#include "nodes/pg_list.h"
#include "nodes/plannodes.h"
#include "utils/portal.h"

Datum   cursor_plan_rows(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(cursor_plan_rows);

Datum
cursor_plan_rows(PG_FUNCTION_ARGS)
{   char    *portalname = PG_GETARG_CSTRING(0);   Portal   portal;   Plan    *plan;
   portal = GetPortalByName(portalname);
   if (!PortalIsValid(portal)) {       ereport(ERROR,               (errcode(ERRCODE_UNDEFINED_CURSOR),
errmsg("cursor\"%s\" does not exist", portalname)));   }
 
   if (!portal->planTrees) {       ereport(ERROR,               (errcode(ERRCODE_INVALID_CURSOR_STATE),
errmsg("cursor\"%s\" has no plan trees", portalname)));   }
 
   plan = linitial(portal->planTrees);
   if (!plan) {       ereport(ERROR,               (errcode(ERRCODE_INVALID_CURSOR_STATE),
errmsg("cursor\"%s\" plan is NULL", portalname)));   }
 
   PG_RETURN_FLOAT8(plan->plan_rows);
}

-- 
Michael Fuhr

Re: Cursor estimated row count

From
Tom Lane
Date:
Michael Fuhr <mike@fuhr.org> writes:
> An occasionally asked question is "How can I find out how many rows
> a cursor will return?" to which the answer is "Fetch them all."  But
> what about a way to get the planner's estimate?  Would anybody find
> that useful?

Given how far off it frequently is, I can't believe that any of the
people who ask for the feature would find this a satisfactory answer :-(
        regards, tom lane

Re: Cursor estimated row count

From
Michael Fuhr
Date:
On Sat, Nov 12, 2005 at 01:50:20PM -0500, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.org> writes:
> > An occasionally asked question is "How can I find out how many rows
> > a cursor will return?" to which the answer is "Fetch them all."  But
> > what about a way to get the planner's estimate?  Would anybody find
> > that useful?
> 
> Given how far off it frequently is, I can't believe that any of the
> people who ask for the feature would find this a satisfactory answer :-(

That aside, do you see any problems with the code?  Is it at least
returning the right wrong answer? ;-)

-- 
Michael Fuhr