On Mon, Jan 24, 2022 at 6:22 AM Valentin Janeiko <val.janeiko@xxxxxxxxx> 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
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)
AND r.resource_type_id IN (10, 52, 95, 119, 60)
AND cte2Source.resource_id_hash IN ('df26ca5a-d2e2-1576-2507-815d8e73f15e'::uuid)
