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: <pgsql-performance(at)postgresql(dot)org>
Cc: "Greg Stark" <gsstark(at)mit(dot)edu>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: best way to fetch next/prev record based on index
Date: 2004-07-29 16:48:51
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB34101AF0F@Herge.rcsinc.local (view raw or flat)
Thread:
Lists: pgsql-performance
Just an update on this:
queries in the 'next key' form on fields(a,b,c)
only ever use the index for the first field (a).  I just never noticed
that before...in most cases this is selective enough.  In most logical
cases in multi part keys the most important stuff comes first.

Curiously, queries in the Boolean reversed form (switching and with or,
etc.) find the index but do not apply any condition, kind of like an
indexed sequential scan...

Well, if and when the rowtype comparison can be made to work over multi
part keys (and the optimizer is made to do tricks there), postgres can
be made to give much better generalized ISAM access.  In the meantime,
I'll just troubleshoot specific cases via application specific behavior
as they come up.  In any case, many thanks to Greg and Tom for taking
the time to pick this apart.

Merlin

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2004-07-29 17:06:26
Subject: Re: Optimizer refuses to hash join
Previous:From: Stephan SzaboDate: 2004-07-29 16:08:55
Subject: Re: Optimizer refuses to hash join

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