From: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | best way to fetch next/prev record based on index |
Date: | 2004-07-27 13:07:02 |
Message-ID: | 6EE64EF3AB31D5448D0007DD34EEB34101AEF5@Herge.rcsinc.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Schaber | 2004-07-27 13:15:31 | Automagic tuning |
Previous Message | Hervé Piedvache | 2004-07-27 07:35:33 | Little understanding for tuning ... |