On Mon, Jan 24, 2022 at 6:22 AM Valentin Janeiko <val.jane...@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)*
