Re: Slow running query

--000000000000582d9705997c52d6
Content-Type: text/plain; charset="UTF-8"

Hello Tom,

Thanks for the suggestion, I will try to tune the mentioned functions.


Thanks and regards

On Wed, Dec 11, 2019 at 9:02 PM Tom Lane <[email protected]> wrote:

> Shrikant Bhende <[email protected]> writes:
> > Below is the query which is running very slow, can anyone suggest any
> > improvement for the same to make it faster.
>
> Not when you haven't given us any supporting data :-(.  There's some
> advice about how to ask useful performance questions here:
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> However, just scanning your EXPLAIN output, it seems that the bulk
> of the time is being spent inside two user-defined functions:
>
> >                                              ->  Function Scan on
> get_num_connections f  (cost=0.25..10.25 rows=1000 width=24) (actual
> time=22331.461..22331.479 rows=263 loops=1)
> ...
> >                                              ->  Function Scan on
> get_num_proprietary f_1  (cost=0.25..10.25 rows=1000 width=24) (actual
> time=4052.081..4052.085 rows=26 loops=1)
> ...
> >  Planning time: 18.362 ms
> >  Execution time: 33944.679 ms
>
> ie, 26 of the 34 seconds are being spent there.  You're not going to be
> able to move the needle very far unless you can make those a lot cheaper.
>
> I notice that the first thing the plan does with these is FULL JOIN them
> to each other, which seems suspiciously like a performance anti-pattern.
>
>                         regards, tom lane
>

--000000000000582d9705997c52d6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello Tom,<div><br></div><div>Thanks for the suggestion, I=
 will try to tune the mentioned functions.</div><div><br></div><div><br></d=
iv><div>Thanks and=C2=A0regards</div></div><br><div class=3D"gmail_quote"><=
div dir=3D"ltr" class=3D"gmail_attr">On Wed, Dec 11, 2019 at 9:02 PM Tom La=
ne &lt;<a href=3D"mailto:[email protected]">[email protected]</a>&gt; wrote=
:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.=
8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Shrikant Bhend=
e &lt;<a href=3D"mailto:[email protected]" target=3D"_blank">shr=
[email protected]</a>&gt; writes:<br>
&gt; Below is the query which is running very slow, can anyone suggest any<=
br>
&gt; improvement for the same to make it faster.<br>
<br>
Not when you haven&#39;t given us any supporting data :-(.=C2=A0 There&#39;=
s some<br>
advice about how to ask useful performance questions here:<br>
<br>
<a href=3D"https://wiki.postgresql.org/wiki/Slow_Query_Questions" rel=3D"no=
referrer" target=3D"_blank">https://wiki.postgresql.org/wiki/Slow_Query_Que=
stions</a><br>
<br>
However, just scanning your EXPLAIN output, it seems that the bulk<br>
of the time is being spent inside two user-defined functions:<br>
<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 -&gt;=C2=A0 Function Scan on get_num_connections f=C2=A0 =
(cost=3D0.25..10.25 rows=3D1000 width=3D24) (actual time=3D22331.461..22331=
.479 rows=3D263 loops=3D1)<br>
...<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 -&gt;=C2=A0 Function Scan on get_num_proprietary f_1=C2=
=A0 (cost=3D0.25..10.25 rows=3D1000 width=3D24) (actual time=3D4052.081..40=
52.085 rows=3D26 loops=3D1)<br>
...<br>
&gt;=C2=A0 Planning time: 18.362 ms<br>
&gt;=C2=A0 Execution time: 33944.679 ms<br>
<br>
ie, 26 of the 34 seconds are being spent there.=C2=A0 You&#39;re not going =
to be<br>
able to move the needle very far unless you can make those a lot cheaper.<b=
r>
<br>
I notice that the first thing the plan does with these is FULL JOIN them<br=
>
to each other, which seems suspiciously like a performance anti-pattern.<br=
>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>

--000000000000582d9705997c52d6--