Обсуждение: SELECT performance drop
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 | 2233237fm_grant | Insufficient data | 204282fm_trader | 5 | 89037fm_capital | 99 | 84267fm_session | 99 | 7182fm_person | 99 | 4365fm_allocation | 96 | 4286fm_approval | Insufficient data | 920fm_market | 97 | 583fm_account | 93 | 451fm_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
On Tue, Jan 22, 2019 at 1:04 PM Jan Nielsen <jan.sture.nielsen@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 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 | 2233237fm_grant | Insufficient data | 204282fm_trader | 5 | 89037fm_capital | 99 | 84267fm_session | 99 | 7182fm_person | 99 | 4365fm_allocation | 96 | 4286fm_approval | Insufficient data | 920fm_market | 97 | 583fm_account | 93 | 451fm_marketplace | 22 | 275and 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_.idAND order0_.type = 'LIMIT'AND session1_.original = 7569AND ( order0_.consumer IS NULLOR ( order0_.consumer IS NOT NULL )AND order0_.consumer > 0AND ( EXISTS (SELECT 1FROM fm_order order2_CROSS JOIN fm_session session3_WHERE order2_.session_id = session3_.idAND order2_.id = order0_.consumerAND session3_.original = 7569AND order2_.type = 'LIMIT'ANDorder2_.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
Hello, could you check that statistics for fm_session are accurate ? Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
On Tue, Jan 22, 2019 at 2:55 PM legrand legrand <legrand_legrand@hotmail.com> wrote:
Hello,
could you check that statistics for fm_session are accurate ?
Regards
PAscal
heroku pg:psql -c "SELECT schemaname, relname, last_analyze FROM pg_stat_all_tables WHERE relname LIKE 'fm_%'"schemaname | relname | last_analyze------------+----------------+--------------public | fm_account |public | fm_allocation |public | fm_approval |public | fm_capital |public | fm_grant |public | fm_market |public | fm_marketplace |public | fm_order |public | fm_person |public | fm_session |public | fm_trader |
I suspect you'd say "not accurate"? :-o After ANALYZE, the performance is much better. Thank you so much!
One thing that isn't helping is that you have a redundant predicate. The selectivity of this predicate is also estimated too low, so removing the redundant predicate might improve the estimate and change the plan: ( " + " o.consumer IS NULL " + " ) OR ( " + " o.consumer IS NOT NULL " + " AND o.consumer > 0 remove "o.consumer IS NOT NULL AND", which is implied by o.consumer > 0. This predicate should have been automatically removed, but the filter shown in depesz shows that it was not. If you can find out what the faster plan was, that would be helpful to know. ----- Jim Finnerty, AWS, Amazon Aurora PostgreSQL -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
On Wed, Jan 23, 2019 at 6:51 AM Jim Finnerty <jfinnert@amazon.com> wrote:
One thing that isn't helping is that you have a redundant predicate. The
selectivity of this predicate is also estimated too low, so removing the
redundant predicate might improve the estimate and change the plan:
( "
+ " o.consumer IS NULL "
+ " ) OR ( "
+ " o.consumer IS NOT NULL "
+ " AND o.consumer > 0
remove "o.consumer IS NOT NULL AND", which is implied by o.consumer > 0.
This predicate should have been automatically removed, but the filter shown
in depesz shows that it was not.
Good point -- the new generated SQL is
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>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;
If you can find out what the faster plan was, that would be helpful to know.
-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
Hi, is there an index on fm_order(session_id,type) ? regards PAscal
On Wed, 2019-01-23 at 10:28 -0700, Jan Nielsen wrote: > 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>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; It might be more efficient to rewrite that along these lines: SELECT DISTINCT order0_.* FROM fm_order order0_ JOIN fm_session session1_ ON order0_.session_id = session1_.id LEFT JOIN fm_order order2_ ON order2_.id = order0_.consumer LEFT JOIN fm_session session3_ ON order2_.session_id = session3_.id WHERE coalesce(order2_.id, 1) > 0 AND /* all the other conditions */; Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On Wed, Jan 23, 2019 at 12:37 PM legrand legrand <legrand_legrand@hotmail.com> wrote:
Hi,
is there an index on
fm_order(session_id,type)?
There isn't at the moment:
table_name | index_name | column_name----------------+------------------------------+-------------fm_account | fm_account_pkey | idfm_account | uk_5p6qalvucbxmw9u64wf0aif9d | namefm_allocation | fm_allocation_pkey | idfm_approval | fm_approval_pkey | idfm_capital | fm_capital_pkey | idfm_grant | fm_grant_pkey | idfm_market | fm_market_pkey | idfm_marketplace | fm_marketplace_pkey | idfm_order | fm_order_pkey | idfm_person | fm_person_pkey | idfm_session | fm_session_pkey | idfm_trader | fm_trader_pkey | id
regards
PAscal
With Pascal's suggestion, I added a new index:
CREATE INDEX fm_order_sid_type_idx ON fm_order (session_id, type);
Thank you, Pascal!
On Thu, Jan 24, 2019 at 9:52 AM Jan Nielsen <jan.sture.nielsen@gmail.com> wrote:
On Wed, Jan 23, 2019 at 12:37 PM legrand legrand <legrand_legrand@hotmail.com> wrote:Hi,
is there an index on
fm_order(session_id,type)?There isn't at the moment:table_name | index_name | column_name----------------+------------------------------+-------------fm_account | fm_account_pkey | idfm_account | uk_5p6qalvucbxmw9u64wf0aif9d | namefm_allocation | fm_allocation_pkey | idfm_approval | fm_approval_pkey | idfm_capital | fm_capital_pkey | idfm_grant | fm_grant_pkey | idfm_market | fm_market_pkey | idfm_marketplace | fm_marketplace_pkey | idfm_order | fm_order_pkey | idfm_person | fm_person_pkey | idfm_session | fm_session_pkey | idfm_trader | fm_trader_pkey | id
regards
PAscal