pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT performance drop
10+ messages / 4 participants
[nested] [flat]
* SELECT performance drop
@ 2019-01-22 20:04 Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:00 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Nielsen @ 2019-01-22 20:04 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT performance drop
2019-01-22 20:04 SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
@ 2019-01-22 21:00 ` Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:55 ` Re: SELECT performance drop legrand legrand <legrand_legrand@hotmail.com>
2019-01-23 13:50 ` Re: SELECT performance drop Jim Finnerty <jfinnert@amazon.com>
0 siblings, 2 replies; 10+ messages in thread
From: Jan Nielsen @ 2019-01-22 21:00 UTC (permalink / raw)
To: pgsql-performance
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
> <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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT performance drop
2019-01-22 20:04 SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:00 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
@ 2019-01-22 21:55 ` legrand legrand <legrand_legrand@hotmail.com>
2019-01-22 22:28 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: legrand legrand @ 2019-01-22 21:55 UTC (permalink / raw)
To: pgsql-performance
Hello,
could you check that statistics for fm_session are accurate ?
Regards
PAscal
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT performance drop
2019-01-22 20:04 SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:00 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:55 ` Re: SELECT performance drop legrand legrand <legrand_legrand@hotmail.com>
@ 2019-01-22 22:28 ` Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-23 19:37 ` RE:SELECT performance drop legrand legrand <legrand_legrand@hotmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Nielsen @ 2019-01-22 22:28 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-performance
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 <https://explain.depesz.com/s/p9KX;. Thank you so much!
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE:SELECT performance drop
2019-01-22 20:04 SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:00 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:55 ` Re: SELECT performance drop legrand legrand <legrand_legrand@hotmail.com>
2019-01-22 22:28 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
@ 2019-01-23 19:37 ` legrand legrand <legrand_legrand@hotmail.com>
2019-01-24 16:52 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: legrand legrand @ 2019-01-23 19:37 UTC (permalink / raw)
To: Jan Nielsen <jan.sture.nielsen@gmail.com>; +Cc: pgsql-performance
Hi,
is there an index on
fm_order(session_id,type)
?
regards
PAscal
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT performance drop
2019-01-22 20:04 SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:00 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:55 ` Re: SELECT performance drop legrand legrand <legrand_legrand@hotmail.com>
2019-01-22 22:28 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-23 19:37 ` RE:SELECT performance drop legrand legrand <legrand_legrand@hotmail.com>
@ 2019-01-24 16:52 ` Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-24 18:04 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Nielsen @ 2019-01-24 16:52 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-performance
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 | id
fm_account | uk_5p6qalvucbxmw9u64wf0aif9d | name
fm_allocation | fm_allocation_pkey | id
fm_approval | fm_approval_pkey | id
fm_capital | fm_capital_pkey | id
fm_grant | fm_grant_pkey | id
fm_market | fm_market_pkey | id
fm_marketplace | fm_marketplace_pkey | id
fm_order | fm_order_pkey | id
fm_person | fm_person_pkey | id
fm_session | fm_session_pkey | id
fm_trader | fm_trader_pkey | id
>
> regards
> PAscal
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT performance drop
2019-01-22 20:04 SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:00 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:55 ` Re: SELECT performance drop legrand legrand <legrand_legrand@hotmail.com>
2019-01-22 22:28 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-23 19:37 ` RE:SELECT performance drop legrand legrand <legrand_legrand@hotmail.com>
2019-01-24 16:52 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
@ 2019-01-24 18:04 ` Jan Nielsen <jan.sture.nielsen@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jan Nielsen @ 2019-01-24 18:04 UTC (permalink / raw)
To: legrand legrand <legrand_legrand@hotmail.com>; +Cc: pgsql-performance
With Pascal's suggestion, I added a new index:
CREATE INDEX fm_order_sid_type_idx ON fm_order (session_id, type);
which improved the query to 2mS!
https://explain.depesz.com/s/oxvs
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 | id
> fm_account | uk_5p6qalvucbxmw9u64wf0aif9d | name
> fm_allocation | fm_allocation_pkey | id
> fm_approval | fm_approval_pkey | id
> fm_capital | fm_capital_pkey | id
> fm_grant | fm_grant_pkey | id
> fm_market | fm_market_pkey | id
> fm_marketplace | fm_marketplace_pkey | id
> fm_order | fm_order_pkey | id
> fm_person | fm_person_pkey | id
> fm_session | fm_session_pkey | id
> fm_trader | fm_trader_pkey | id
>
>
>
>
>>
>> regards
>> PAscal
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT performance drop
2019-01-22 20:04 SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:00 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
@ 2019-01-23 13:50 ` Jim Finnerty <jfinnert@amazon.com>
2019-01-23 17:28 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Jim Finnerty @ 2019-01-23 13:50 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT performance drop
2019-01-22 20:04 SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:00 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-23 13:50 ` Re: SELECT performance drop Jim Finnerty <jfinnert@amazon.com>
@ 2019-01-23 17:28 ` Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-23 22:41 ` Re: SELECT performance drop Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 10+ messages in thread
From: Jan Nielsen @ 2019-01-23 17:28 UTC (permalink / raw)
To: Jim Finnerty <jfinnert@amazon.com>; +Cc: pgsql-performance
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.
>
which results in:
https://explain.depesz.com/s/vGVo
>
>
>
> -----
> Jim Finnerty, AWS, Amazon Aurora PostgreSQL
> --
> Sent from:
> http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: SELECT performance drop
2019-01-22 20:04 SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:00 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-23 13:50 ` Re: SELECT performance drop Jim Finnerty <jfinnert@amazon.com>
2019-01-23 17:28 ` Re: SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
@ 2019-01-23 22:41 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 10+ messages in thread
From: Laurenz Albe @ 2019-01-23 22:41 UTC (permalink / raw)
To: Jan Nielsen <jan.sture.nielsen@gmail.com>; Jim Finnerty <jfinnert@amazon.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2019-01-24 18:04 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-01-22 20:04 SELECT performance drop Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:00 ` Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-22 21:55 ` legrand legrand <legrand_legrand@hotmail.com>
2019-01-22 22:28 ` Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-23 19:37 ` legrand legrand <legrand_legrand@hotmail.com>
2019-01-24 16:52 ` Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-24 18:04 ` Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-23 13:50 ` Jim Finnerty <jfinnert@amazon.com>
2019-01-23 17:28 ` Jan Nielsen <jan.sture.nielsen@gmail.com>
2019-01-23 22:41 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox