Whole system search with _type parameter and global params should be much faster #3232
Assignees
Labels
Comments
lmsurpre
added a commit
that referenced
this issue
Jan 21, 2022
… subquery Signed-off-by: Lee Surprenant <[email protected]>
lmsurpre
added a commit
that referenced
this issue
Jan 21, 2022
… subquery Signed-off-by: Lee Surprenant <[email protected]>
|
for QA: ensure coverage for this type of whole-system search is in our test performance regression suite (fhir-meter). ensure the performance is at least as good (hopefully much better) |
|
ran this on 4.10.2 and it generated the following SQL Then Ran on latest and this was the SQL Reopening issue |
|
the interesting thing about the findings from dag is that its actually the count query that is being slow here. when robin and I were looking at the initial issue, it was the "fetch ids" step (the 2nd query out of the 3) that was being slow |
lmsurpre
added a commit
that referenced
this issue
Feb 10, 2022
Previously, we were adding this extra IN clause for the _type parameter in all cases. In the case of a whole-system search *count* query, this was slowing things down a bit. We still add the IN clause to the query where it seemed to help, but now we avoid that for the count query. Signed-off-by: Lee Surprenant <[email protected]>
lmsurpre
added a commit
that referenced
this issue
Feb 10, 2022
Previously, we were adding this extra IN clause for the _type parameter in all cases. In the case of a whole-system search *count* query, this was slowing things down a bit. We still add the IN clause to the query where it seemed to help, but now we avoid that for the count query. Signed-off-by: Lee Surprenant <[email protected]>
lmsurpre
added a commit
that referenced
this issue
Feb 10, 2022
Previously, we were adding this extra IN clause for the _type parameter in all cases. In the case of a whole-system search *count* query, this was slowing things down a bit. We still add the IN clause to the query where it seemed to help, but now we avoid that for the count query. Signed-off-by: Lee Surprenant <[email protected]>
lmsurpre
added a commit
that referenced
this issue
Feb 10, 2022
Previously, we were adding this extra IN clause for the _type parameter in all cases. In the case of a whole-system search *count* query, this was slowing things down a bit. We still add the IN clause to the query where it seemed to help, but now we avoid that for the count query. Signed-off-by: Lee Surprenant <[email protected]>
lmsurpre
added a commit
that referenced
this issue
Feb 15, 2022
issue #3232- add the extra IN clause more selectively
|
The query now runs 20% faster than it did before on a DB with over 15 million resources. Closing issue. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Describe the bug
a whole-system search like this:
results in queries like these.
count:
fetch ids:
fetch resources:
The
fetch idsquery in particular is very slow when you have a lot of resources.This is because the inner select grabs every row in the table and then the outer select filters it down to the (possibly very small) subset of resources.
Environment
Which version of IBM FHIR Server?
To Reproduce
Steps to reproduce the behavior:
Expected behavior
The search should return in less than 2 seconds.
Additional context
In our testing, adding
AND LR0.RESOURCE_TYPE_ID IN (8,52,126)to the inner select takes this query from >20 seconds down to ~100 ms.The text was updated successfully, but these errors were encountered: