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

The Hermit Hacker <scrappy(at)hub(dot)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Ü

>