Performance tunning

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Performance tunning

sugnathi hai
Hi ,

Can you help to tune the below plan

Limit (cost=0.87..336777.92 rows=100 width=57) (actual time=599302.173..599481.552 rows=100 loops=1) Buffers: shared hit=78496066 -> Nested Loop (cost=0.87..11005874.67 rows=3268 width=57) (actual time=599302.170..599481.506 rows=100 loops=1) Buffers: shared hit=78496066 -> Index Scan using inx_callprocessingstatus_modifieddate on callprocessingstatus contactsta1_ (cost=0.44..2008486.89 rows=15673696 width=16) (actual time=0.356..66774.105 rows=15651059 loops=1) Index Cond: (modifieddate < now()) Filter: ((overallstatus)::text = 'COMPLETED'::text) Rows Removed by Filter: 275880 Buffers: shared hit=15803632 -> Index Scan using "INX_callinfo_Callid" on callinfo contact0_ (cost=0.43..0.57 rows=1 width=49) (actual time=0.033..0.033 rows=0 loops=15651059) Index Cond: (callid = contactsta1_.callid) Filter: ((combinationkey IS NULL) AND (mod(callid, '2'::bigint) = 0)) Rows Removed by Filter: 1 Buffers: shared hit=62692434 Planning Time: 1.039 ms Execution Time: 599481.758 ms

Reply | Threaded
Open this post in threaded view
|

Re: Performance tunning

Pavel Stehule
Hi

so 30. 5. 2020 v 9:37 odesílatel sugnathi hai <[hidden email]> napsal:
Hi ,

Can you help to tune the below plan

Limit (cost=0.87..336777.92 rows=100 width=57) (actual time=599302.173..599481.552 rows=100 loops=1) Buffers: shared hit=78496066 -> Nested Loop (cost=0.87..11005874.67 rows=3268 width=57) (actual time=599302.170..599481.506 rows=100 loops=1) Buffers: shared hit=78496066 -> Index Scan using inx_callprocessingstatus_modifieddate on callprocessingstatus contactsta1_ (cost=0.44..2008486.89 rows=15673696 width=16) (actual time=0.356..66774.105 rows=15651059 loops=1) Index Cond: (modifieddate < now()) Filter: ((overallstatus)::text = 'COMPLETED'::text) Rows Removed by Filter: 275880 Buffers: shared hit=15803632 -> Index Scan using "INX_callinfo_Callid" on callinfo contact0_ (cost=0.43..0.57 rows=1 width=49) (actual time=0.033..0.033 rows=0 loops=15651059) Index Cond: (callid = contactsta1_.callid) Filter: ((combinationkey IS NULL) AND (mod(callid, '2'::bigint) = 0)) Rows Removed by Filter: 1 Buffers: shared hit=62692434 Planning Time: 1.039 ms Execution Time: 599481.758 ms

Can you show a query related to this plan?




Reply | Threaded
Open this post in threaded view
|

Re: Performance tunning

Justin Pryzby
On Sat, May 30, 2020 at 09:43:43AM +0200, Pavel Stehule wrote:
> so 30. 5. 2020 v 9:37 odesílatel sugnathi hai <[hidden email]> napsal:
> > Can you help to tune the below plan

Could you also send it so line breaks aren't lost, as seen here:
https://www.postgresql.org/message-id/975278223.51863.1590824209351%40mail.yahoo.com

Probably best to send a link to the plan at https://explain.depesz.com/

https://wiki.postgresql.org/wiki/Slow_Query_Questions
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems

--
Justin


Reply | Threaded
Open this post in threaded view
|

Re: Performance tunning

Jeff Janes
In reply to this post by sugnathi hai
On Sat, May 30, 2020 at 3:37 AM sugnathi hai <[hidden email]> wrote:
Hi ,

Can you help to tune the below plan


It looks like your query (which you should show us) has something like

  ORDER BY modifieddate LIMIT 100

It thinks it can walk the index in order, then stop once it collects 100 qualifying rows.  But since almost all rows are removed by the join conditions, it ends up walking a large chunk of the index before finding 100 of them which qualify.

You could try forcing it out of this plan by doing:

  ORDER BY modifieddate + interval '0 second' LIMIT 100

 Cheers,

Jeff