Question :
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?
Answer :
Any index can only be used for WHERE
conditions of the form
<indexed expression> <operator> <constant>
Here <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):
-
max(<indexed expression>)
andmin(<indexed expression>)
-
ORDER BY <indexed expression>
-
functions returning
boolean
in theWHERE
condition, if they have a support function.