best way to fetch next/prev record based on index

Поиск
Список
Период
Сортировка
От Merlin Moncure
Тема best way to fetch next/prev record based on index
Дата
Msg-id 6EE64EF3AB31D5448D0007DD34EEB34101AEF5@Herge.rcsinc.local
обсуждение исходный текст
Ответы Re: best way to fetch next/prev record based on index  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: best way to fetch next/prev record based on index  (Greg Stark <gsstark@mit.edu>)
Re: best way to fetch next/prev record based on index  (Markus Schaber <schabios@logi-track.com>)
Re: best way to fetch next/prev record based on index  (Rod Taylor <pg@rbt.ca>)
Список pgsql-performance
I am in a situation where I have to treat a table as logically ordered
based on an index.  Right now I'm doing this via queries, and a I need a
better way to do it.  Cursors do not meet my requirements, because they
are always insensitive.  Also, my performance requirements are
extreme...I need 100% index usage.

Currently, I use queries to do this.  Unfortunately, the queries can get
kind of complex because many if the indexes (keys, really) are over 3 or
more columns in a table.

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)

In about 95% of cases, the planner correctly selects the index t(a,b,c)
and uses it.  However, the 5% remaining cases usually come at the worst
time, when large tables and 3 or 4 part keys are involved.  In those
cases sometimes the planner applies the filter to a, but not b or c with
a large performance hit.  Manipulating statistics on the table does not
seem to help.

Interestingly, it is possible to rewrite the above query by switching
and with or and >= with >.  However when written that way, the planner
almost never gets it right.

My problem is deceptively simple: how you read the next record from a
table based on a given set of values?  In practice, this is difficult to
implement.  If anybody can suggest a alternative/better way to this, I'm
all ears.

Merlin

В списке pgsql-performance по дате отправления:

Предыдущее
От: Hervé Piedvache
Дата:
Сообщение: Little understanding for tuning ...
Следующее
От: Markus Schaber
Дата:
Сообщение: Automagic tuning