Right way to seek to an element in an indexed table

Started by Risko Peterabout 24 years ago1 messagesgeneral
Jump to latest
#1Risko Peter
rpetike@freemail.hu

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