On 6/28/05, Nando Dessena <nando@...> wrote:

> Dmitry,
>
> D> As for the FIRST/SKIP problem, I'd say this is easily solvable on
> D> the client or in PSQL via an open cursor and explicit fetches. Do I
> D> miss something important in this wish?
>
> they want to do it stateless, in different transactions if not
> different attachments, with not much regard for accuracy, mostly from
> web applications. That's what people appear to need.

To "paginate" the long lists on web-pages I often use the following trick:
1) Ensure that selection is ordered by unique key (easily accomplished
by adding PK to the end of ORDER BY list).
2) After the query is issued, retrieve only the first 10 records via
"select first 11..." [sic] syntax.
3) Show/enable "next" button in user interface if the query issued on
step 2 actually returned no less than 11 records.
4) Put onto the html form some <input type=hidden> fields holding the
values for the last key on the page.
5) When the user presses "next" button, issue a query of the form
"SELECT FIRST 11 ... FROM ... WHERE ... AND field1 > :f1 OR field1=:f1
AND field2>:f2 ORDER BY f1, f2", where f1 and f2 are the values
retrieved from hidden fields.
6) Repeat steps 2-5 to allow efficient browsing of the dataset without
ever using SKIP clause. If there there are suitable indices, this is
quite effective too. It is also very simple to extend this to
"backwards" browsing -- just store first record's key also and revert
the conditions.

The main advantage of this technique is that it avoids the need to
fetch "skipped" records. The main drawbacks are:
1) The backwards browsing performance drops when there is no "DESC" index.
2) The user do not have information about the total number of records
in query when this number is above 10.

The first point might be considered and argument for the
bi-directional indexes, but I actually do not think so. Once I
understood the issue, (although admittedly, it really surprised me),
then a question whether to create one, two or none indexes for a
particular field became just another design trade-off that should be
made, nothing too worry much about.

But is second point is IMO important, so please let me start another
thread about it.