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
>
> Thanks for your reply.
> What I'm trying to do is the following: I want to browse through a view
> containing more than 10000 records. To avoid slowing things down too much,
> I would like my client program to receive (through the network) only the
> records that are to be displayed on the screen. I believe I could do this
> by declaring a cursor and then fetching the parts of the resultset I need.
> It would be useful to know the size of the resultset immediately after the
> cursor has been declared. How do I get this information? I could of course
> fetch all of the resultset, but that is what I am trying to avoid.
> Shouldn't it be quicker to perform a move through the set than fetching it?
> I found that moving zero records results in a move to the end of the
> resultset, with a command status returning the number of records moved.
> Although I expected this method to take less time than a fetch (does it?),
> I was wondering if there might be another way to get the size of the
> resultset that can be fetched through the declared cursor.
>
> KP
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
