Re: find next in an index

On Sun, 2005-02-13 at 01:24 -0500, Greg Stark wrote:
> 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.
>

Thanks Greg,
I have put your suggestion (number 2) in my code. It is working quite
well.