Subject: Re: length of recordset read through a cursor
From: Rod Taylor <rbt@rbt.ca>
To: "Knut P. Lehre" <k.p.d.lehre@basalmed.uio.no>
Cc: pgsql-sql@postgresql.org, Christoph Haller <ch@rodos.fzk.de>
In-Reply-To: <5.2.0.9.2.20030805123048.01415ed8@uio-pop.uio.no>
References: <5.2.0.9.2.20030805123048.01415ed8@uio-pop.uio.no>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-FIPX5Ab3rW25AjaEPWf3"
Message-Id: <1060140310.19206.46.camel@jester>
Mime-Version: 1.0
Date: Tue, 05 Aug 2003 23:25:10 -0400

--=-FIPX5Ab3rW25AjaEPWf3
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

Yes, a move takes less time, but can still a significant amount of time.

Do you need to know exactly what to expect?  Run ANALYZE recently?

A cheat I've used before is to parse the EXPLAIN (not EXPLAIN ANALYZE)
output for the expected number of records involved.  If that number was
less than 2000, I MOVE through them for an exact count -- otherwise
display as approx <explain number>.

In most cases it's within 50% of actuality, sometimes better, but very
few people care. They just want to know whether the information from
their search is within the next screen or two.

On Tue, 2003-08-05 at 07:13, Knut P. Lehre wrote:
>  >> After declaring a cursor, one way of obtaining the length of the
>  >resultset
>  >> is to perform a "MOVE 0" and read the PQcmdStatus which returns a
>  >"MOVE nn"
>  >> where nn is the length of the resultset. (A negative MOVE can then be
>  >used
>  >> to allow starting to fetch records from the beginning of the
>  >resultset.)
>  >>
>  >> Is there another, possibly faster way?
>  >>
>  >Looks like you're using libpq (because you mention PQcmdStatus),
>  >then after declaring a cursor and FETCH ALL, try
>  >
>  >1.3.4. Retrieving SELECT Result Information
>  >
>  >    PQntuples Returns the number of tuples (rows) in the query result.
>  >
>  >    int PQntuples(const PGresult *res);
>  >
>  >I'm not exactly sure what you're trying to achieve or going to do,
>  >so if I misunderstood you, ask again.
>  >
>  >Regards, Christoph
>=20
> Thanks for your reply.
> What I'm trying to do is the following: I want to browse through a view=
=20
> containing more than 10000 records. To avoid slowing things down too much=
,=20
> I would like my client program to receive (through the network) only the=
=20
> records that are to be displayed on the screen. I believe I could do this=
=20
> by declaring a cursor and then fetching the parts of the resultset I need=
.=20
> It would be useful to know the size of the resultset immediately after th=
e=20
> cursor has been declared. How do I get this information? I could of cours=
e=20
> fetch all of the resultset, but that is what I am trying to avoid.=20
> Shouldn't it be quicker to perform a move through the set than fetching i=
t?=20
> I found that moving zero records results in a move to the end of the=20
> resultset, with a command status returning the number of records moved.=
=20
> Although I expected this method to take less time than a fetch (does it?)=
,=20
> I was wondering if there might be another way to get the size of the=20
> resultset that can be fetched through the declared cursor.
>=20
> KP
>=20
>=20
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>=20

--=-FIPX5Ab3rW25AjaEPWf3
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/MHUV6DETLow6vwwRAudqAJ0dueyO8Z/gUC39bd9g73qwITMMbgCeOPn3
AoK9WZ1dJTiaZusd5cgGWs0=
=1iFw
-----END PGP SIGNATURE-----

--=-FIPX5Ab3rW25AjaEPWf3--