Re: SELECT performance drop

On Tue, Jan 22, 2019 at 1:04 PM Jan Nielsen <jan(dot)sture(dot)nielsen(at)gmail(dot)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
>