On Tue, Jan 22, 2019 at 1:04 PM Jan Nielsen <jan.sture.niel...@gmail.com> wrote:
> 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 ") > > ...which Hibernate converts to: 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; > 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 >
