Right way to seek to an element in an indexed table

From: Risko Peter <rpetike(at)freemail(dot)hu>
To: pgsql-general(at)postgresql(dot)org
Subject: Right way to seek to an element in an indexed table
Date: 2002-03-09 23:15:06
Message-ID: Pine.LNX.4.21.0203100011580.562-100000@pepehost.ike
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

I am quite newbie to Postgres. I want to scroll and seek in a table
_very fast_ in my application. Thanks to Postgres I can use the
'declare c cursor...' and 'fetch...' commands for very cool scrolling.
But I still have a problem with seeking to the desired position:

Here is how I cope with it now:

create table t(f char(30));
COPY "t" FROM stdin;
apple
pear
peanut
melon
strawberry
banana
orange
grape
potato
\.
create index i on t (f);
begin work;
declare c cursor for select f from t order by f;

# Assume, that I want to seek to 'pear':

select count(f) from t where f < 'pear';

# it gives me what I need (6), but 'select count' seems to me a very big waste,
# and it loads heavily my server when I use it on tables having millions of
# rows :(
# now I can do the positioning:

move forward 6 in c;
...

So, I ask you for a nicer way to get the position of an element according
to an index.

Thank you for your help: RISKO Peter

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Reyes 2002-03-10 00:25:49 spanish characters in postgresql
Previous Message Thomas Lockhart 2002-03-09 22:06:18 Re: UNIQUE( col1, col2 ) creates what indexes?