Re: How to interpret this explain analyse?

Tom Lane wrote:
> "Joost Kraaijeveld" <J(dot)Kraaijeveld(at)Askesis(dot)nl> writes:
> > I cannot change the query (it is geneated by a tool called Clarion) but it something like (from the psqlodbc_xxx.log):
> > "...
> > declare SQL_CUR01 cursor for
> > SELECT A.ordernummer, B.klantnummer FROM "orders" A LEFT OUTER JOIN "klt_alg" B ON A.Klantnummer=B.Klantnummer ORDER BY A.klantnummer;
> > fetch 100 in SQL_CUR01;
> > ..."
>
> Well, the planner does put some emphasis on startup time when dealing
> with a DECLARE CURSOR plan; the problem you face is just that that
> correction isn't large enough. (From memory, I think it optimizes on
> the assumption that 10% of the estimated rows will actually be fetched;
> you evidently want a setting of 1% or even less.)

Ouch. Is this really a reasonable assumption? I figured the primary
use of a cursor was to fetch small amounts of data at a time from a
large table, so 10% seems extremely high as an average fetch size. Or
is the optimization based on the number of rows that will be fetched
by the cursor during the cursor's lifetime (as opposed to in a single
fetch)?

Also, one has to ask what the consequences are of assuming a value too
low versus too high. Which ends up being worse?

> We once talked about setting up a GUC variable to control the percentage
> of a cursor that is estimated to be fetched:
> http://archives.postgresql.org/pgsql-hackers/2000-10/msg01108.php
> It never got done but that seems like the most reasonable solution to
> me.

Or keep some statistics on cursor usage, and adjust the value
dynamically based on actual cursor queries (this might be easier said
than done, of course).

--
Kevin Brown kevin(at)sysexperts(dot)com