To get the most recent session I need to
SELECT MAX(UPPER(range)) AS end FROM sessions WHERE session_id = ?. I have a GiST index on
(session_id, range). This query is extremely slow, taking nearly 30 seconds. I added a normal btree index on
(session_id, UPPER(range)) and that fixed it down to under a millisecond, but it seems like the index on range should allow for queries on its upper bound. Is there a way to do this with just one index? Am I doing something wrong, either in the query or in the index? Should I use a different type of index other than GiST?
Any index can only be used for
WHERE conditions of the form
<indexed expression> <operator> <constant>
<operator> must be of an operator class supported by the index, and
<constant> must be constant for the duration of the index scan.
Other than that, indexes can speed up the following (where the first two are only possible with B-tree indexes):
ORDER BY <indexed expression>
WHEREcondition, if they have a support function.