MIME-Version: 1.0
References: <CAC5iy610q5vrvmcU88CTEP7367CJVYagDmpYnKZ9G08uQf9ZUg@mail.gmail.com>
 <1155364.1723523231@sss.pgh.pa.us>
In-Reply-To: <1155364.1723523231@sss.pgh.pa.us>
From: Siraj G <tosiraj.g@gmail.com>
Date: Mon, 26 Aug 2024 15:59:57 +0530
Message-ID: <CAC5iy62+3TSUo_wys0izZsN=LL2SqQMwj_NiYY4SOq9_hrd=KA@mail.gmail.com>
Subject: Re: Problem with a Query
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f98d500620939b19"
Archived-At: <https://www.postgresql.org/message-id/CAC5iy62%2B3TSUo_wys0izZsN%3DLL2SqQMwj_NiYY4SOq9_hrd%3DKA%40mail.gmail.com>
Precedence: bulk

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

Thanks Tom. Collecting full stats on the tables involved corrected the
execution.

On Tue, Aug 13, 2024 at 9:57=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Siraj G <tosiraj.g@gmail.com> writes:
> > We migrated a PgSQL database from Cloud SQL to compute engine and since
> > then there is a SQL we observed taking a long time. After some study, I
> > found that the SQL is using NESTED LOOP where the cost is too high.
>
> The core of your problem seems to be here:
>
> >                      ->  Index Scan using marketing_a_cancel__55ffff_id=
x
> on
> > marketing_app_leadhistory w0  (cost=3D0.57..4274.30 rows=3D1 width=3D8)=
 (actual
> > time=3D46.678..51.232 rows=3D44 loops=3D1)
> >                            Index Cond: ((cancel_event_id IS NOT NULL) A=
ND
> > (cancel_event_type =3D 1))
> >                            Filter: ((status_id =3D 93) AND
> > ((followup_date)::date >=3D '2024-08-01'::date) AND ((followup_date)::d=
ate
> <=3D
> > '2024-08-07'::date))
> >                            Rows Removed by Filter: 22268
> >                            Buffers: shared hit=3D9170 read=3D19
>
> If the planner had estimated 40-some rows out of this step, rather
> than one, it would certainly not have chosen to use nestloop joins
> atop this.  So the big problem to focus on is making that estimate
> better.
>
> A secondary problem is that the choice of index seems poor: the
> index itself is selecting 44+22268 =3D 22312 rows and then the filter
> condition is throwing away 99.8% of those rows.  Probably, using
> an index on (status_id, followup_date) would have worked better.
>
> I suspect that both of these things are tied to the non-normalization
> of your "cancel" condition.  The planner probably believes that
> "cancel_event_id IS NOT NULL" is statistically independent of
> "cancel_event_type =3D 1"; but I'll bet it isn't, and thus the index
> condition selects many more rows than the planner guessed.  You might
> be able to improve that estimate by creating extended stats on both of
> those columns, but really a better idea would be to take a step back
> and figure out if those two columns can't be merged into one.
>
>                         regards, tom lane
>

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

<div dir=3D"ltr">Thanks Tom. Collecting full stats on the tables involved c=
orrected=C2=A0the execution.</div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Tue, Aug 13, 2024 at 9:57=E2=80=AFAM Tom L=
ane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrot=
e:<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">Siraj G &lt;<=
a href=3D"mailto:tosiraj.g@gmail.com" target=3D"_blank">tosiraj.g@gmail.com=
</a>&gt; writes:<br>
&gt; We migrated a PgSQL database from Cloud SQL to compute engine and sinc=
e<br>
&gt; then there is a SQL we observed taking a long time. After some study, =
I<br>
&gt; found that the SQL is using NESTED LOOP where the cost is too high.<br=
>
<br>
The core of your problem seems to be here:<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 -&gt;=C2=A0 Index Scan using marketing_a_cancel__55ffff_idx on<br>
&gt; marketing_app_leadhistory w0=C2=A0 (cost=3D0.57..4274.30 rows=3D1 widt=
h=3D8) (actual<br>
&gt; time=3D46.678..51.232 rows=3D44 loops=3D1)<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 Index Cond: ((cancel_event_id IS NOT NULL) AND<=
br>
&gt; (cancel_event_type =3D 1))<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 Filter: ((status_id =3D 93) AND<br>
&gt; ((followup_date)::date &gt;=3D &#39;2024-08-01&#39;::date) AND ((follo=
wup_date)::date &lt;=3D<br>
&gt; &#39;2024-08-07&#39;::date))<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 Rows Removed by Filter: 22268<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 Buffers: shared hit=3D9170 read=3D19<br>
<br>
If the planner had estimated 40-some rows out of this step, rather<br>
than one, it would certainly not have chosen to use nestloop joins<br>
atop this.=C2=A0 So the big problem to focus on is making that estimate<br>
better.<br>
<br>
A secondary problem is that the choice of index seems poor: the<br>
index itself is selecting 44+22268 =3D 22312 rows and then the filter<br>
condition is throwing away 99.8% of those rows.=C2=A0 Probably, using<br>
an index on (status_id, followup_date) would have worked better.<br>
<br>
I suspect that both of these things are tied to the non-normalization<br>
of your &quot;cancel&quot; condition.=C2=A0 The planner probably believes t=
hat<br>
&quot;cancel_event_id IS NOT NULL&quot; is statistically independent of<br>
&quot;cancel_event_type =3D 1&quot;; but I&#39;ll bet it isn&#39;t, and thu=
s the index<br>
condition selects many more rows than the planner guessed.=C2=A0 You might<=
br>
be able to improve that estimate by creating extended stats on both of<br>
those columns, but really a better idea would be to take a step back<br>
and figure out if those two columns can&#39;t be merged into one.<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>

--000000000000f98d500620939b19--