From: Michael Lewis <mlewis@entrata.com>
To: Valentin Janeiko <val.janeiko@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow query fixed by replacing equality with a nested query
Date: Mon, 24 Jan 2022 10:52:40 -0700
Message-ID: <CAHOFxGq4WEDDWT4AEXHfMatk0O2DhP+aXbbnRnVfSYgqJG3T6w@mail.gmail.com> (raw)
In-Reply-To: <CAPhGPA7iCqCxbvycaVWr3qjOf5gGv68f4zYwA-hWeUJVQSnreQ@mail.gmail.com>
References: <CAPhGPA5OYUN8m6R=Z429BVBYTpYMvXqhSG3ZbZECpcEyjaDWvQ@mail.gmail.com>
<CAHOFxGoOz4RYH2Zj9C6EqcqLKKTwv=76onrOK0_DJGC8Fhu8vQ@mail.gmail.com>
<003801d80ebb$54ac97a0$fe05c6e0$@gmail.com>
<CAPhGPA7iCqCxbvycaVWr3qjOf5gGv68f4zYwA-hWeUJVQSnreQ@mail.gmail.com>
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)*
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mlewis@entrata.com, val.janeiko@gmail.com
Subject: Re: Slow query fixed by replacing equality with a nested query
In-Reply-To: <CAHOFxGq4WEDDWT4AEXHfMatk0O2DhP+aXbbnRnVfSYgqJG3T6w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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