Re: Slow query fixed by replacing equality with a nested query

On Mon, Jan 24, 2022 at 6:22 AM Valentin Janeiko <val(dot)janeiko(at)gmail(dot)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)*