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

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "Markus Schaber" <schabios(at)logi-track(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: best way to fetch next/prev record based on index
Date: 2004-07-27 14:21:32
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB34101AEF8@Herge.rcsinc.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

> > So, for a table t with a three part key over columns a,b,c, the
query
> > to read the next value from t for given values a1, b1, c1 is
> >
> > select * from t where
> > a >= a1 and
> > (a > a1 or b >= b1) and
> > (a > a1 or b > b1 or c > c1)
>
> You mut not rely on such trickery to get any ordering, as the SQL data
> model contains no ordering, and a query optimizer is free to deliver
you
> the tuples in any order it feels like.
>
> Why don't you add a 'ORDER BY a,b,c ASC' to your query?

Left that part out (oops) :). My queries always have that at the end
(or they will give incorrect results!). All are suffixed with order by
a,b,c limit n. n is manipulated in some cases for progressive read
ahead (kind of like fetch 'n' in cursors)).

The basic problem is the planner can't always match the query to the
index. So, either the planner has to be helped/fixed or I have to
explore another solution. This seems to happen most when the 'a' column
has very poor selectivity. In this case, the planner will only examine
the 'a' part of the key.

Merlin

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Rod Taylor 2004-07-27 14:36:34 Re: best way to fetch next/prev record based on index
Previous Message Markus Schaber 2004-07-27 14:21:17 Correction of best way to fetch next/prev record based on index