MIME-Version: 1.0
References: 
 <CAPhGPA5OYUN8m6R=Z429BVBYTpYMvXqhSG3ZbZECpcEyjaDWvQ@mail.gmail.com>
 <CAHOFxGoOz4RYH2Zj9C6EqcqLKKTwv=76onrOK0_DJGC8Fhu8vQ@mail.gmail.com>
 <003801d80ebb$54ac97a0$fe05c6e0$@gmail.com>
 <CAPhGPA7iCqCxbvycaVWr3qjOf5gGv68f4zYwA-hWeUJVQSnreQ@mail.gmail.com>
In-Reply-To: 
 <CAPhGPA7iCqCxbvycaVWr3qjOf5gGv68f4zYwA-hWeUJVQSnreQ@mail.gmail.com>
From: Michael Lewis <mlewis@entrata.com>
Date: Mon, 24 Jan 2022 10:52:40 -0700
Message-ID: 
 <CAHOFxGq4WEDDWT4AEXHfMatk0O2DhP+aXbbnRnVfSYgqJG3T6w@mail.gmail.com>
Subject: Re: Slow query fixed by replacing equality with a nested query
To: Valentin Janeiko <val.janeiko@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000010735905d657a4fc"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHOFxGq4WEDDWT4AEXHfMatk0O2DhP%2BaXbbnRnVfSYgqJG3T6w%40mail.gmail.com>
Precedence: bulk

--00000000000010735905d657a4fc
Content-Type: text/plain; charset="UTF-8"

On Mon, Jan 24, 2022 at 6:22 AM Valentin Janeiko <val.janeiko@gmail.com>
wrote:

> I have rewritten the query using JOINs. I had to make one of them a
> FULL JOIN, but otherwise JOINs seem like a good idea.
> I have added the new query to the (same) gist:
>
> https://gist.github.com/valeneiko/89f8cbe26db7ca2651b47524462b5d18#file-queryoptimized-sql
> The query plan is much better with just a few small index scans which
> completes in under a millisecond: https://explain.depesz.com/s/vBdG


Glad to hear it, but as best as I can figure, that right join is actually
an inner join because of the where clause meaning that cte2Source must not
be null and therefore cte2.resource_surrogate_id must not be null.

*RIGHT* JOIN fhir.reference_search_param AS cte2 ON
cte2.is_history = false
AND cte2.search_param_id = 561
AND cte2.resource_type_id IN (42)
AND cte2.reference_resource_type_id = r.resource_type_id
AND cte2.reference_resource_id_hash = r.resource_id_hash

INNER JOIN fhir.resource AS cte2Source ON
   cte2Source.is_history = false
   AND cte2Source.resource_type_id IN (42)
*   AND cte2Source.resource_surrogate_id = cte2.resource_surrogate_id*

WHERE cte1.start_date_time <= '2022-01-12 12:13:21.969000Z'
AND r.resource_type_id IN (10, 52, 95, 119, 60)
* AND cte2Source.resource_id_hash IN
('df26ca5a-d2e2-1576-2507-815d8e73f15e'::uuid)*

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

<div dir=3D"ltr"><div dir=3D"ltr">On Mon, Jan 24, 2022 at 6:22 AM Valentin =
Janeiko &lt;<a href=3D"mailto:val.janeiko@gmail.com">val.janeiko@gmail.com<=
/a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex">I have rewritten the query using JOINs. I had to =
make one of them a<br>
FULL JOIN, but otherwise JOINs seem like a good idea.<br>
I have added the new query to the (same) gist:<br>
<a href=3D"https://gist.github.com/valeneiko/89f8cbe26db7ca2651b47524462b5d=
18#file-queryoptimized-sql" rel=3D"noreferrer" target=3D"_blank">https://gi=
st.github.com/valeneiko/89f8cbe26db7ca2651b47524462b5d18#file-queryoptimize=
d-sql</a><br>
The query plan is much better with just a few small index scans which<br>
completes in under a millisecond: <a href=3D"https://explain.depesz.com/s/v=
BdG" rel=3D"noreferrer" target=3D"_blank">https://explain.depesz.com/s/vBdG=
</a></blockquote><div><br></div><div>Glad to hear it, but as best as I can =
figure, that right join is actually an inner join because of the where clau=
se meaning that=C2=A0cte2Source must not be null and therefore cte2.resourc=
e_surrogate_id must not be null.<br><br><b>RIGHT</b> JOIN fhir.reference_se=
arch_param AS cte2 ON<br>	cte2.is_history =3D false<br>	AND cte2.search_par=
am_id =3D 561<br>	AND cte2.resource_type_id IN (42)<br>	AND cte2.reference_=
resource_type_id =3D r.resource_type_id<br>	AND cte2.reference_resource_id_=
hash =3D r.resource_id_hash</div><div><br>INNER JOIN fhir.resource AS cte2S=
ource ON<br>=C2=A0 =C2=A0cte2Source.is_history =3D false<br>=C2=A0 =C2=A0AN=
D cte2Source.resource_type_id IN (42)<br><b>=C2=A0 =C2=A0AND cte2Source.res=
ource_surrogate_id =3D cte2.resource_surrogate_id</b><br><br></div><div>WHE=
RE cte1.start_date_time &lt;=3D &#39;2022-01-12 12:13:21.969000Z&#39;<br>	A=
ND r.resource_type_id IN (10, 52, 95, 119, 60)<br><b>	AND cte2Source.resour=
ce_id_hash IN (&#39;df26ca5a-d2e2-1576-2507-815d8e73f15e&#39;::uuid)</b><br=
></div></div></div>

--00000000000010735905d657a4fc--