Lists: | pgsql-hackers |
---|
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Cursor estimated row count |
Date: | 2005-11-12 18:31:44 |
Message-ID: | 20051112183144.GA91710@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Cursor estimated row count |
Date: | 2005-11-12 18:50:20 |
Message-ID: | 28801.1131821420@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Michael Fuhr <mike(at)fuhr(dot)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
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Cursor estimated row count |
Date: | 2005-11-12 18:56:00 |
Message-ID: | 20051112185600.GA92075@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
On Sat, Nov 12, 2005 at 01:50:20PM -0500, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)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