Thread: find next in an index

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;

I don't think this will work under all circumstances.

Any help appreciated. Thanks.

On Sun, Feb 13, 2005 at 14:03:02 +1100,
  Neil Dugan <postgres@butterflystitches.com.au> wrote:
> 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;
>
> I don't think this will work under all circumstances.
>
> Any help appreciated. Thanks.

I think using OFFSET 1 and LIMIT 2 with an appropiate WHERE clause
will get you want you want. If you are going to keep stepping through
the list, you might want to use a cursor.

On Sat, 2005-02-12 at 21:47 -0600, Bruno Wolff III wrote:
> On Sun, Feb 13, 2005 at 14:03:02 +1100,
>   Neil Dugan <postgres@butterflystitches.com.au> wrote:
> > 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;
> >
> > I don't think this will work under all circumstances.
> >
> > Any help appreciated. Thanks.
>
> I think using OFFSET 1 and LIMIT 2 with an appropiate WHERE clause
> will get you want you want. If you are going to keep stepping through
> the list, you might want to use a cursor.
>
With cursors is it possible to set the cursor to the location of a
particular row (found by another select query).  The documentation say
you can set at a particular row number, but I would like to move the
cursor to the same record as found by another query (same index, same
fields).

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.

--
greg

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.