Thread: Re: Browsing the tables and why pgsql does not perform well

The Hermit Hacker <scrappy@hub.org> wrote:

> >   Please please help me solve this or make workarounds or anything. I
> > would *really* like to see PosgreSQL to be playing against the Big
> > (commercial) Boys !
>
>         I'm curious, but can the "Big (commercial) Boys" do this?  If so,
> can you please provide an example of which and how?  Most of us here have
> access to an one or the other (me, Oracle) to use as a sample system...if
> we can prove that it does work on another system, then we have something
> to work with, but right now all I've seen is "I wish I could do this", and
> several examples on how to accomplish it using PostgreSQL, but that's
> it...
>
The main problem is that PostgreSQL does not use index for sorting and
thus really does a
"copy" of the whole table and then sorts it before it can use a few rows
from the beginning.

Using indexes for sorting as well as selecting is on the TODO list, but
seems to be not very high priority.

>
> >    0. having a value of a field on which there is an index, how can
> I do :
> >        a) current_pointer = some_function("value_I_have");
> >        b) next_pointer    = some_other_function(current_pointer);
> >        c) one_tupple      = yet_another_function(next_pointer);
> >    If I can accomplish a,b,c, then I win and I don't have to do
> questions
> >    1..5 below.
>
>         Why not put a sequence field on the table so that you can do:
>
>         select * from table where rowid = n;     -or-
>         select * from table where rowid = n - 1; -or-
>         select * from table where rowid = n + 1; -or-
>         select * from table where rowid >= n and rowid <= n+x;
>
>         And create the index on rowid?

It works no better than any other indexed field unless you disallow
deletes.

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 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.

Then one could just do:

--8<---------

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;

--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.

Even more unfortunately client side development tools like Access or
Delphi seem to rely on sorted queries using indexes for sorting and as a
result perform very poorly with PostgreSQL in their default modes.

OTOH, it usually shows poor database design if you can't specify your
seach criteria precisely enough to limit the number of rows to some
manageable level in interactive applications. It really is the task of
the database server to look up things. The poor user should not have to
wade through zillions of records in a looking for the one she wants
(even tho the you cand do it quite effectively using ISAM).

OTOOH, it would be very hard for general client side tools to do without
keyed access, so addind using indexes for sorting should be given at
least some priority.

----------------
Hannu Krosing
Trust-O-Matic OÜ

>

> Even more unfortunately client side development tools like Access or
> Delphi seem to rely on sorted queries using indexes for sorting and as a
> result perform very poorly with PostgreSQL in their default modes.

I find Delphi to be poor even on Informix because Delphi thinks it has
an ISAM file behind each table, and when using an SQL engine, does tons
of table rescans every time it wants to change data in a table because
it doesn't have a physical row it can manage internally.

--
Bruce Momjian
maillist@candle.pha.pa.us

On Sat, 24 January 1998, at 09:36:41, Hannu Krosing wrote:

> The main problem is that PostgreSQL does not use index for sorting and
> thus really does a
> "copy" of the whole table and then sorts it before it can use a few rows
> from the beginning.
>
> Using indexes for sorting as well as selecting is on the TODO list, but
> seems to be not very high priority.

It doesn't seem like it would be very difficult -- I'd be happy to
tackle it if given some pointers in the right direction (I am a newbie
pgsql-hacker and I'm looking for work!  give me some!!)

--brett

> OTOOH, it would be very hard for general client side tools to do without
> keyed access, so addind using indexes for sorting should be given at
> least some priority.
>
> ----------------
> Hannu Krosing
> Trust-O-Matic O�
>
> >
>

>
> On Sat, 24 January 1998, at 09:36:41, Hannu Krosing wrote:
>
> > The main problem is that PostgreSQL does not use index for sorting and
> > thus really does a
> > "copy" of the whole table and then sorts it before it can use a few rows
> > from the beginning.
> >
> > Using indexes for sorting as well as selecting is on the TODO list, but
> > seems to be not very high priority.
>
> It doesn't seem like it would be very difficult -- I'd be happy to
> tackle it if given some pointers in the right direction (I am a newbie
> pgsql-hacker and I'm looking for work!  give me some!!)
>

It is so complicated, I can not even suggest where to start.

--
Bruce Momjian
maillist@candle.pha.pa.us