SELECT performance drop

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Ron 2019-01-22 20:05:25 Re: Memory and hard ware calculation :
Previous Message Pavel Stehule 2019-01-22 19:32:46 Re: [HACKERS] proposal: schema variables