Re: best way to fetch next/prev record based on index

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: <gsstark(at)mit(dot)edu>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: best way to fetch next/prev record based on index
Date: 2004-07-29 18:23:15
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB34101AF11@Herge.rcsinc.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Greg Stark wrote:
> Well I'm not sure whether you caught it, but Tom did come up with a
> work-around that works with the current infrastructure if all the
columns
> involved are the same datatype.
>
> You can create a regular btree index on the expression array[a,b,c]
and
> then
> do your lookup using array[a,b,c] > array[a1,b1,c1].

Unfortunately, ISAM files allow keys based on combinations of fields on
any type. So this is not an option. (I have spent over 6 months
researching this problem).

However, this would work:
Create index on t(stackparam(array[a::text,b::text,c::text),
array['char(2)', 'int', 'date')];

With the 'type strings' queried out in advance. stackparam(text[],
text[]) is a C function with uses the types and cats the strings
together in such a way that preserves sorting. In any case, this is an
ugly and inefficient mess, and I have no desire to do this unless there
is no other way. I would much rather see postgres 'get' (a,b,c) > (a1,
b1, c1)...if there is even a chance this is possible, I'll direct my
efforts there. IMNSHO, this form was invented by the SQL folks for
dealing with data in an ISAM manner, postgres should be able do it and
do it well.

Merlin

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2004-07-29 18:41:37 Re: best way to fetch next/prev record based on index
Previous Message Greg Stark 2004-07-29 17:57:55 Re: best way to fetch next/prev record based on index