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
Re: best way to fetch next/prev record based on index Re: best way to fetch next/prev record based on index Re: best way to fetch next/prev record based on index |
Список | 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 по дате отправления: