I noticed that one of my Hibernate JPA SELECT
s against my Heroku PG 10.4 instance is taking a loooog time 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 ")
which Hibernate turns into:
SELECT order0_.id AS id1_7_, order0_.created_by AS created_2_7_, order0_.created_date AS created_3_7_, order0_.last_modified_by AS last_mod4_7_, order0_.last_modified_date AS last_mod5_7_, order0_.consumer AS consumer6_7_, order0_.market_id AS market_14_7_, order0_.original AS original7_7_, order0_.owner_id AS owner_i15_7_, order0_.owner_target AS owner_ta8_7_, order0_.price AS price9_7_, order0_.session_id AS session16_7_, order0_.side AS side10_7_, order0_.supplier AS supplie11_7_, order0_.type AS type12_7_, order0_.units AS units13_7_ FROM fm_order order0_ CROSS JOIN fm_session session1_ WHERE order0_.session_id = session1_.id AND order0_.type = 'LIMIT' AND session1_.original = 7569 AND ( order0_.consumer IS NULL OR ( order0_.consumer IS NOT NULL ) AND order0_.consumer > 0 AND ( EXISTS (SELECT 1 FROM fm_order order2_ CROSS JOIN fm_session session3_ WHERE order2_.session_id = session3_.id AND order2_.id = order0_.consumer AND session3_.original = 7569 AND order2_.type = 'LIMIT' AND order2_.owner_id <> order0_.owner_id) ) ) ORDER BY order0_.last_modified_date DESC;
Any ideas how I turn this 320S disaster around?