Re: SELECT performance drop

[Jan Nielsen <jan.sture.nielsen@xxxxxxxxx>]

On Tue, Jan 22, 2019 at 1:04 PM Jan Nielsen <jan.sture.nielsen@xxxxxxxxx> 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 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