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
<https://explain.depesz.com/s/r2GU> 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
