Обсуждение: CURSOR/FETCH vs LIMIT/OFFSET

Hi,

re: displaying results of query.

Trying to work out details of how to let users page back and forth 
among a multi-page list of results.

Problem: I need to know the total number of tuples the query would 
return in order to decide if there are more to display.

1) Use CURSOR and FETCH

$res= $con->exec("BEGIN WORK;DECLARE gene_result CURSOR FORSELECT blah blahORDER BY blah;FETCH $offset IN
gene_result");

$result->ntuple: returns only the number set by $offset, not TOTAL for query.

2) use LIMIT OFFSET

same problem,
$result->ntuple: returns only the number set by LIMIT, OFFSET, not 
TOTAL for query.

So there has to be a way to glean both the TOTAL and the SUBSET returned?

Searching the postgresql archives obviously can deal with this
http://archives.postgresql.org/pgsql-sql/
'Displaying documents 11-20 of total 243 found.'

-- 
Regards,
Chuck

Why not at the very beginning do something like "SELECT COUNT(*)..." and
store that as $totalTuples... then use limit/offset...

-philip

On Wed, 8 May 2002, Charles Hauser wrote:

> Hi,
>
> re: displaying results of query.
>
> Trying to work out details of how to let users page back and forth
> among a multi-page list of results.
>
> Problem: I need to know the total number of tuples the query would
> return in order to decide if there are more to display.
>
> 1) Use CURSOR and FETCH
>
> $res= $con->exec(
>     "BEGIN WORK;
>     DECLARE gene_result CURSOR FOR
>     SELECT blah blah
>     ORDER BY blah;
>     FETCH $offset IN gene_result
>     ");
>
> $result->ntuple: returns only the number set by $offset, not TOTAL for query.
>
> 2) use LIMIT OFFSET
>
> same problem,
> $result->ntuple: returns only the number set by LIMIT, OFFSET, not
> TOTAL for query.
>
> So there has to be a way to glean both the TOTAL and the SUBSET returned?
>
> Searching the postgresql archives obviously can deal with this
> http://archives.postgresql.org/pgsql-sql/
> 'Displaying documents 11-20 of total 243 found.'
>
> --
> Regards,
>
>     Chuck
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Hi all,
Hi Tom,

I was exploring ways to improve the time required to update a large
table from the join of two others as in:
UPDATE a FROM b, c;

I found that whatever index I create, compound or not, PG insist on
performing the cartesian product first.
Now, if "b" and "c" are relatively small, that make sense, but when the
cartesian product of "b" and "c" is and order of magnitude larger than
"a" it makes no sense at all.

Shouldn't the number of rows in "b" and "c" be reduced to the matching
the criterias on "a" first?
If "b" is meant to be a many to one relation to "c", shouldn't "a" be
joined to "b" first?

Is there a way I can force the "a" join "b" first?


I also tried to do it in a PLPGSQL FOR LOOP, but because the script is
processed as a transaction, I do not get much better performance.

It would sure be nice to be able to commit the change on a row per row
basis.

THX

jll