_explain
Debug & Optimise your SQL queries for Search
With the nonstandard _explain parameter, you can inspect the search query execution plan.
1
GET /Encounter?subject:Patient._ilike=john&_explain=analyze
2
3
-- resp
4
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 || $JSONquot;,"resourceType":"Patient"}}$JSON$)::jsonb) WHERE ((("subject_patient".id || '' '' || "subject_patient".resource::text) ilike ?)) ) "encounter" LIMIT ? OFFSET ?', '%john%', 100, 0]
5
plan: |-
6
Limit (cost=94.22..94.25 rows=2 width=116) (actual time=2.188..2.196 rows=0 loops=1)
7
-> Unique (cost=94.22..94.23 rows=2 width=116) (actual time=2.169..2.178 rows=0 loops=1)
8
-> Sort (cost=94.22..94.23 rows=2 width=116) (actual time=2.152..2.161 rows=0 loops=1)
9
Sort Key: encounter.id
10
Sort Method: quicksort Memory: 25kB
11
-> Nested Loop (cost=0.00..94.21 rows=2 width=116) (actual time=2.107..2.116 rows=0 loops=1)
12
Join Filter: (encounter.resource @> ((('{"subject":{"id":"'::text || subject_patient.id) || '","resourceType":"Patient"}}'::text))::jsonb)
13
-> Seq Scan on encounter (cost=0.00..15.60 rows=560 width=116) (actual time=2.087..2.109 rows=0 loops=1)
14
-> Materialize (cost=0.00..22.62 rows=4 width=32) (never executed)
15
-> Seq Scan on patient subject_patient (cost=0.00..22.60 rows=4 width=32) (never executed)
16
Filter: (((id || ' '::text) || (resource)::text) ~~* '%john%'::text)
17
Planning Time: 6.040 ms
18
Execution Time: 2.522 ms
Copied!
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.
Last modified 8mo ago
Copy link