Re: find next in an index - Mailing list pgsql-general

From Neil Dugan
Subject Re: find next in an index
Date
Msg-id 1108339730.5086.4.camel@postgresql.localdomain
Whole thread Raw
In response to Re: find next in an index  (Greg Stark <gsstark@mit.edu>)
List pgsql-general
On Sun, 2005-02-13 at 01:24 -0500, Greg Stark wrote:
> Neil Dugan <postgres@butterflystitches.com.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.



pgsql-general by date:

Previous
From: Geoffrey
Date:
Subject: Re: [pgsql-www] Website Documentation
Next
From: Guy Thornley
Date:
Subject: Index search order hints for R-Tree indexes