Re: Specific query taking time to process

--000000000000dc99730599741e7f
Content-Type: text/plain; charset="UTF-8"

On Tue, Dec 10, 2019 at 3:40 AM Fahiz Mohamed <[email protected]> wrote:

> Thank you very much for your prompt responses.
>
> I have analysed more regarding this and found the long running query.
>
> I ran "explain analyse" on this query and I got following result. (We have
> 2 identical DB instances and they consist of same data. Instane 1 took 20+
> second to process and instance 2 took less than a second)
>

They do not consist of the same data.  One returns 17 rows, the other 22.

One finds 5635 rows (scattered over 40765 blocks!) where qname_id = 251,
the other find 85 rows for the same condition.  It seems the first one is
not very well vacuumed.

I don't know if these differences are enough to be driving the different
plans (the estimation differences appear smaller than the actual
differences), but clearly the data is not the same.

Your first query is using the index idx_alf_node_mdq in a way which seems
to be counter-productive.  Perhaps you could inhibit it to see what plan it
chooses then.  For example, specify in your query "type_qname_id+0 = 240"
to prevent the use of that index.  Or you could drop the index, if it is
not vital.

But if the data has not be ANALYZEd recently, you should do that before
anything else.  Might as well make it a VACUUM ANALYZE.

Cheers,

Jeff

>

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

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Dec 10, 2019 at 3:40 AM Fahiz Moh=
amed &lt;<a href=3D"mailto:[email protected]">[email protected]</a>&gt; wro=
te:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex">



<div>
<div name=3D"messageBodySection">
<div dir=3D"auto">Thank you very much for your prompt responses.
<div dir=3D"auto"><br></div>
<div dir=3D"auto">I have analysed more regarding this and found the long ru=
nning query.</div>
<div dir=3D"auto"><br></div>
<div dir=3D"auto">I ran &quot;explain analyse&quot; on this query and I got=
 following result. (We have 2 identical DB instances and they consist of sa=
me data. Instane 1 took 20+ second to process and instance 2 took less than=
 a second)</div></div></div></div></blockquote><div><br></div><div>They do =
not consist of the same data.=C2=A0 One returns 17 rows, the other 22.</div=
><div><br></div><div>One finds 5635 rows (scattered over 40765 blocks!) whe=
re=C2=A0qname_id =3D 251, the other find 85 rows for the same condition.=C2=
=A0 It seems the first one is not very well vacuumed.</div><div><br></div><=
div>I don&#39;t know if these differences are enough to be driving the diff=
erent plans (the estimation differences appear smaller than the actual diff=
erences), but clearly the data is not the same.</div><div><br></div><div>Yo=
ur first query is using the index=C2=A0idx_alf_node_mdq in a way which seem=
s to be counter-productive.=C2=A0 Perhaps you could inhibit it to see what =
plan it chooses then.=C2=A0 For example, specify in your query &quot;type_q=
name_id+0 =3D 240&quot; to prevent the use of that index.=C2=A0 Or you coul=
d drop the index, if it is not vital.</div><div><br></div><div>But if the d=
ata has not be ANALYZEd recently, you should do that before anything else.=
=C2=A0 Might as well make it a VACUUM ANALYZE.</div><div><br></div><div>Che=
ers,</div><div><br></div><div>Jeff</div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div><div name=3D"messageReplySection"><blockquote type=3D"c=
ite" style=3D"margin:5px;padding-left:10px;border-left:thin solid rgb(26,18=
8,156)"><div dir=3D"ltr"><div class=3D"gmail_quote">
</div>
</div>
</blockquote>
</div>
</div>

</blockquote></div></div>

--000000000000dc99730599741e7f--