MAX(UPPER(range)) can’t be accelerated with a GiST index on the range?

Posted on

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>) and min(<indexed expression>)

  • ORDER BY <indexed expression>

  • functions returning boolean in the WHERE condition, if they have a support function.

Leave a Reply

Your email address will not be published. Required fields are marked *