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

2022-01-24 Thread Valentin Janeiko 
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

Thank you for your help. Let me know if you have any other suggestions.

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

2022-01-24 Thread Michael Lewis 
On Fri, Jan 21, 2022 at 4:37 AM  wrote:

> I have done a few simple experiments in the past comparing CTEs like this
> to JOINS, but the resultant query plans were the same. CTEs seemed easier
> to follow when troubleshooting issues, so I left them as such. Do JOINs
> become better than CTEs at a certain point?
>

Read up on from_collapse_limit. If the query can re-write subqueries to
collapse the join problem, then it will at first but then once it reaches
that threshold, then it won't try anymore to avoid excessive planning time.
That's when things can go awry.

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

2022-01-24 Thread Michael Lewis 
On Mon, Jan 24, 2022 at 6:22 AM Valentin Janeiko 
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)*