SELECT performance drop

[Jan Nielsen <jan.sture.nielsen@xxxxxxxxx>]

I just notice that one of my Hibernate JPA SELECTs against my Heroku PG 10.4 instance is taking a  l o o o g  to complete as this EXPLAIN (ANALYZE, BUFFERS) shows. The database is 591MB running in PG 10.4 on Heroku with the following row counts and index use:

    relname     | percent_of_times_index_used | rows_in_table  

----------------+-----------------------------+---------------

fm_order       | 99                          |       2233237

fm_grant       | Insufficient data           |        204282

fm_trader      | 5                           |         89037

fm_capital     | 99                          |         84267

fm_session     | 99                          |          7182

fm_person      | 99                          |          4365

fm_allocation  | 96                          |          4286

fm_approval    | Insufficient data           |           920

fm_market      | 97                          |           583

fm_account     | 93                          |           451

fm_marketplace | 22                          |           275

and the offending JPA JPQL is:

    @Query("SELECT o FROM Order o WHERE                                "

         + "       o.type                  = 'LIMIT'                   "

         + "   AND o.session.original      = :originalSessionId        "

         + "   AND ( (                                                 "

         + "         o.consumer IS NULL                                "

         + "    ) OR (                                                 "

         + "         o.consumer IS NOT NULL                            "

         + "     AND o.consumer > 0                                    "

         + "     AND EXISTS (                                          "

         + "       SELECT 1 FROM Order oo WHERE                        "

         + "             oo.id                    = o.consumer         "

         + "         AND oo.session.original      = :originalSessionId "

         + "         AND oo.type                  = 'LIMIT'            "

         + "         AND oo.owner                != o.owner            "

         + "     )                                                     "

         + "   )                                                       "

         + " )                                                         "

         + " ORDER BY o.lastModifiedDate DESC                          ")

I'd like get this SELECT to complete in a few milliseconds again instead of the several minutes (!) it is now taking. Any ideas what I might try?

Thanks for your time,

Jan