Skip to content

Latest commit

 

History

History
45 lines (37 loc) · 2.34 KB

_explain.md

File metadata and controls

45 lines (37 loc) · 2.34 KB
description
Debug & Optimise your SQL queries for Search

_explain

Use the nonstandard _explain parameter to inspect the search query execution plan.

GET /Encounter?subject:Patient._ilike=john&_explain=analyze

-- resp
query: ['SELECT * FROM (SELECT DISTINCT ON ("encounter".id) "encounter".* FROM "encounter" INNER JOIN patient subject_patient ON ("encounter".resource @> ($JSON${"subject":{"id":"$JSON$ || subject_patient.id || $JSON$","resourceType":"Patient"}}$JSON$)::jsonb) WHERE ((("subject_patient".id || '' '' ||  "subject_patient".resource::text) ilike ?)) ) "encounter" LIMIT ? OFFSET ?', '%john%', 100, 0]
plan: |-
  Limit  (cost=94.22..94.25 rows=2 width=116) (actual time=2.188..2.196 rows=0 loops=1)
    ->  Unique  (cost=94.22..94.23 rows=2 width=116) (actual time=2.169..2.178 rows=0 loops=1)
          ->  Sort  (cost=94.22..94.23 rows=2 width=116) (actual time=2.152..2.161 rows=0 loops=1)
                Sort Key: encounter.id
                Sort Method: quicksort  Memory: 25kB
                ->  Nested Loop  (cost=0.00..94.21 rows=2 width=116) (actual time=2.107..2.116 rows=0 loops=1)
                      Join Filter: (encounter.resource @> ((('{"subject":{"id":"'::text || subject_patient.id) || '","resourceType":"Patient"}}'::text))::jsonb)
                      ->  Seq Scan on encounter  (cost=0.00..15.60 rows=560 width=116) (actual time=2.087..2.109 rows=0 loops=1)
                      ->  Materialize  (cost=0.00..22.62 rows=4 width=32) (never executed)
                            ->  Seq Scan on patient subject_patient  (cost=0.00..22.60 rows=4 width=32) (never executed)
                                  Filter: (((id || ' '::text) || (resource)::text) ~~* '%john%'::text)
  Planning Time: 6.040 ms
  Execution Time: 2.522 ms

If your query is slow and you see Seq Scans , it's time to build indexes. Do not forget to run vacuum analyze on tables involved in query. Read more about PostgreSQL Explain.

This parameter can be used for debugging too. If an SQL error happens, _explain will show the original query:

GET /fhir/Patient?error-demo=1&_explain=0

exception: |-
  ERROR: division by zero
    Where: SQL function "divide" during inlining
query:
  - 'SELECT "patient".* FROM "patient" WHERE (divide(1, ?) = 2) LIMIT ? OFFSET ? '
  - '0'
  - 100
  - 0