Skip site navigation (1) Skip section navigation (2)

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

From: Jan Vicherek <honza(at)ied(dot)com>
To: Hannu Krosing <hannu(at)trust(dot)ee>
Cc: pgsql-hackers(at)postgreSQL(dot)org, scrappy(at)hub(dot)org, vadim(at)sable(dot)krasnoyarsk(dot)su
Subject: Re: Browsing the tables / why pgsql does not perform well (with temp fix)
Date: 1998-01-25 01:11:10
Message-ID: Pine.LNX.3.96.980124200003.497e-100000@ann.ied.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 1998-01-25 01:36:05
Subject: Re: [HACKERS] fork/exec for backend
Previous:From: TomDate: 1998-01-25 00:51:11
Subject: Re: [HACKERS] fork/exec for backend

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group