Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Search with reverse chaining performing slowly #1705

Closed
d0roppe opened this issue Nov 12, 2020 · 3 comments
Closed

Search with reverse chaining performing slowly #1705

d0roppe opened this issue Nov 12, 2020 · 3 comments
Assignees
Labels
bug Something isn't working performance performance search

Comments

@d0roppe
Copy link
Collaborator

d0roppe commented Nov 12, 2020

Describe the bug
A clear and concise description of what the bug is.
The following search with _has takes over 2 minutes to run in the large postgres DB.
[base]/Patient?_has:Observation:patient:code-value-quantity=http://loinc.org|2339-0$68.6

To Reproduce
Steps to reproduce the behavior:

  1. Go to '...'
  2. Click on '....'
  3. Scroll down to '....'
  4. See error

Expected behavior
A clear and concise description of what you expected to happen.
The search should return faster than 2 minutes. in most deploys it would timeout.

Additional context
Add any other context about the problem here.

@d0roppe d0roppe added bug Something isn't working search labels Nov 12, 2020
@lmsurpre
Copy link
Member

I saw similar when we merged the initial PR for this feature: #1666 (comment)
Will be great if we can speed it up.

@lmsurpre lmsurpre added the performance performance label Nov 13, 2020
@lmsurpre lmsurpre self-assigned this Nov 13, 2020
lmsurpre added a commit that referenced this issue Nov 13, 2020
The `R.LOGICAL_RESOURCE_ID = LR.LOGICAL_RESOURCE_ID` part of this join
is unnecessary because R.RESOURCE_ID is the unique identifier on the
Resources (R) table...so if LR.CURRENT_RESOURCE_ID = R.RESOURCE_ID then
this RESOURCES row will always have a LOGICAL_RESOURCE_ID which matches
the LOGICAL_RESOURCES row.

Making this change changed the processing results of the following query
from over 5 minutes down to under 1 second on my postgresql db with
~30,000 patients and ~300,000 conditions:
`GET [base]/Patient?_has:Condition:patient:code=http://snomed.info/sct|44054006`

Signed-off-by: Lee Surprenant <lmsurpre@us.ibm.com>
lmsurpre added a commit that referenced this issue Nov 13, 2020
issue #1705 - simplified top-level join avoids bad postgresql plan for simple _has queries
@d0roppe
Copy link
Collaborator Author

d0roppe commented Nov 30, 2020

Running this search today with the latest code loaded on my local FHIR server linked to the large postgres DB. this is now taking about 3.6 seconds to complete.

@lmsurpre
Copy link
Member

lmsurpre commented Dec 1, 2020

Closing based on Dag's comment. Future enhancements can come as a result of #677 findings / fixes.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working performance performance search
Projects
None yet
Development

No branches or pull requests

2 participants