Skip site navigation (1) Skip section navigation (2)

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

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: best way to fetch next/prev record based on index
Date: 2004-07-27 15:29:53
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB34101AEFA@Herge.rcsinc.local (view raw or flat)
Thread:
Lists: pgsql-performance
> > select * from t where
> > 	a >= a1 and
> >      (a >  a1 or b >= b1) and
> >      (a >  a1 or b > b1 or c > c1)
> 
> > In about 95% of cases, the planner correctly selects the index
t(a,b,c)
> > and uses it.
> 
> I'm surprised it's that good.  Why not do

It is.  In fact, it's so good, I mistakenly assumed it would get it
right all the time.  That led me directly to my current situation.
 
> 	select * from t where a >= a1 and b >= b1 and c >= c1
> 	order by a,b,c
> 	limit 1 offset 1;
Note: I left off the limit/order part of the query in my original
example.

My previous experience with offset was that it's not practical for this
type of use.  Query time degrades when offset gets large...it's
basically n^2/2 for a scan of a table.  If offset was pumped up to O(1)
for any sized offset, the problem would be trivial.  

Plus, your where clause does not guarantee results.

Imagine:
a  b  c
2  3  4
4  2  1

c !> c1

The only other way to rewrite the query is thus (pg has much more
trouble with this form):
select * from t where
	a >   a1 or
     (a >=  a1 and b > b1) or
     (a >=  a1 and b >= b1 and c > c1)

Merlin

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2004-07-27 15:33:19
Subject: Re: best way to fetch next/prev record based on index
Previous:From: Tom LaneDate: 2004-07-27 15:14:01
Subject: Re: best way to fetch next/prev record based on index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group