Re: Browsing the tables / why pgsql does not perform well (with temp fix)

On Sat, 24 Jan 1998, Hannu Krosing wrote:

> > if aggregates were able to use indexes you could do:
> >
> > select min(n) from table where rowid >n;
> >
> > and then
> >
> > select * from table where n = n_found by_last_previous_select;
> >
> > but as they don't you would get very poor performance from the first
> > select;

  This looks good. No transactions necessary, no locking, no mutliple rows
copying.

  how hard would it be to make aggregates able to use indexes ?

  Could I manage in a day ? (10 hours)


> > This could be simulated by fetching only the first row from a cursor
> > sorted on the field.
> >
> > So the real solution would be to use indexes for sorting, maybe at first
> > for single field sorts.

    How many hours would that take to write ?

> > Then one could just do:
> >
> > --8<---------
> > begin;
> > declare cursor part_cursor for
> >   select * from part_table
> >     where indexed_field > 'last_value'
> >     order by indexed_field ;
> >
> > fetch 10 from  part_cursor;
> >
> > close part_cursor;
> > end;
> > --8<---------
> >
> > for moving backwards you would of course use '<' and 'desc' in the
> > select clause.
> >
> > Unfortunately it does not work nearly fast enough for big tables as
> > often almost the whole table is copied and then sorted before you get
> > your few rows.

    After code that makes sorting use indices would fix this problem,
right ?

        Jan

 -- Gospel of Jesus is the saving power of God for all who believe --
Jan Vicherek ## To some, nothing is impossible. ##  www.ied.com/~honza
    >>>    Free Software Union President  ...  www.fslu.org    <<<
Interactive Electronic Design Inc.    -#-    PGP: finger honza@ied.com