1

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?

1
  • Can you show an EXPLAIN (ANALYZE, BUFFERS) for the query without the btree index? How many rows are there with the same session_id? While the gist index can't be used with maximal efficiency, it should still be "good enough". If you could show CREATE TABLE...AS SELECT with dummy (but realistic) data, and CREATE INDEX, that would help.
    – jjanes
    Commented Apr 5, 2020 at 17:24

1 Answer 1

2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.