From: | Jan Nielsen <jan(dot)sture(dot)nielsen(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | SELECT performance drop |
Date: | 2019-01-22 20:04:38 |
Message-ID: | CANxH4hECtKo1+O38ZZ6cxVX3kua+n1rQz2FjSw-=f9U9g1c+Ow@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2019-01-22 20:05:25 | Re: Memory and hard ware calculation : |
Previous Message | Mickael van der Beek | 2019-01-22 15:24:20 | Re: Very long query planning times for database with lots of partitions |