Re: find next in an index

Neil Dugan <postgres(at)butterflystitches(dot)com(dot)au> writes:

> Hi,
> I am trying to find out how to get the next record according to a
> particular index.
> I have a table with a name field and a serial field. The name field
> isn't unique so I made an index on name(varchar) & serialno(bigserial).
> I also have an index just on 'name'. I am having trouble working out
> the syntax for the query.
>
> select * from table where name>='jack' and serialno!='2'
> order by name,serialno;

From what you describe it sounds like you are really asking for

SELECT *
FROM table
WHERE (name > 'jack')
OR (name = 'jack' AND serialno>2)
ORDER BY name, serialno
LIMIT 1

However Postgres doesn't really handle this very well. If it uses the index at
all it fetches all the records starting from the beginning of the table
stopping when it finds the right one.

One option is to do

SELECT *
FROM table
WHERE name >= 'jack'
AND ((name > 'jack') OR (name = 'jack' AND serialno>2))
ORDER BY name, serialno
LIMIT 1

Which is fine as long as there are never too many records with the name
'jack'. If you have can possibly have hundreds of records with the name 'jack'
then it's going to spend time skimming through all of them even if you're
already far down the list.

To guarantee reasonable behaviour it looks like you have to do this:

(
SELECT *
FROM table
WHERE name > 'jack'
ORDER BY name, serialno
LIMIT 1
) UNION ALL (
SELECT *
FROM table
WHERE name = 'jack' AND serialno>2
ORDER BY name, serialno
LIMIT 1
)
ORDER BY name, serialno
LIMIT 1

I think there's a todo item about making indexes handle the row-wise
comparison operators like:

WHERE (name,serialno) > ('jack',2)

But that doesn't work properly in Postgres currently. (It may seem to, but
don't be confused, it's actually not doing what you want). It's too bad since
it would be a nice clean simple way to get exactly the right behaviour.

--
greg