pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance tunning
4+ messages / 4 participants
[nested] [flat]
* Performance tunning
@ 2020-05-30 07:36 sugnathi hai <suganhai@yahoo.com>
2020-05-30 07:43 ` Re: Performance tunning Pavel Stehule <pavel.stehule@gmail.com>
2020-05-30 14:55 ` Re: Performance tunning Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: sugnathi hai @ 2020-05-30 07:36 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance tunning
2020-05-30 07:36 Performance tunning sugnathi hai <suganhai@yahoo.com>
@ 2020-05-30 07:43 ` Pavel Stehule <pavel.stehule@gmail.com>
2020-05-30 14:19 ` Re: Performance tunning Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 1 reply; 4+ messages in thread
From: Pavel Stehule @ 2020-05-30 07:43 UTC (permalink / raw)
To: sugnathi hai <suganhai@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi
so 30. 5. 2020 v 9:37 odesÃlatel sugnathi hai <suganhai@yahoo.com> 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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance tunning
2020-05-30 07:36 Performance tunning sugnathi hai <suganhai@yahoo.com>
2020-05-30 07:43 ` Re: Performance tunning Pavel Stehule <pavel.stehule@gmail.com>
@ 2020-05-30 14:19 ` Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 4+ messages in thread
From: Justin Pryzby @ 2020-05-30 14:19 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: sugnathi hai <suganhai@yahoo.com>; pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance tunning
2020-05-30 07:36 Performance tunning sugnathi hai <suganhai@yahoo.com>
@ 2020-05-30 14:55 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jeff Janes @ 2020-05-30 14:55 UTC (permalink / raw)
To: sugnathi hai <suganhai@yahoo.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-05-30 14:55 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-30 07:36 Performance tunning sugnathi hai <suganhai@yahoo.com>
2020-05-30 07:43 ` Pavel Stehule <pavel.stehule@gmail.com>
2020-05-30 14:19 ` Justin Pryzby <pryzby@telsasoft.com>
2020-05-30 14:55 ` Jeff Janes <jeff.janes@gmail.com>
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