Performance tunning
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=62692434Planning Time: 1.039 msExecution Time: 599481.758 ms
Re: Performance tunning
Hi so 30. 5. 2020 v 9:37 odesÃlatel sugnathi hai 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?
Re: Performance tunning
On Sat, May 30, 2020 at 09:43:43AM +0200, Pavel Stehule wrote: > so 30. 5. 2020 v 9:37 odesÃlatel sugnathi hai 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
Re: Performance tunning
On Sat, May 30, 2020 at 3:37 AM sugnathi hai 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
