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(at)ied(dot)com