Обсуждение: 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=62692434 Planning Time: 1.039 ms Execution Time: 599481.758 ms
Hi
so 30. 5. 2020 v 9:37 odesílatel sugnathi hai <suganhai@yahoo.com> napsal:
Hi ,Can you help to tune the below planLimit (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?
On Sat, May 30, 2020 at 09:43:43AM +0200, Pavel Stehule wrote: > so 30. 5. 2020 v 9:37 odesílatel sugnathi hai <suganhai@yahoo.com> 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
On Sat, May 30, 2020 at 3:37 AM sugnathi hai <suganhai@yahoo.com> 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