https://medplum.com logo
Join Discord
Powered by
Degraded batch upsert performance after upgrading ...
# support
n
EDIT: Submitted a github [issue](https://github.com/medplum/medplum/issues/7310) with better detail for visibility Hi Medplum team and community- any guidance or information here would be greatly appreciated! We recently upgraded from 4.1.12 to 4.3.10 (via 4.2.6) and have noticed degradation of batch upserts. For context, we schedule jobs to sync from source data to Medplum for each resource type (Patients, then Encounters, etc). Each resource type is up to tens of thousands of resources, chunked into batch bundles. Tracing seems to show that something related to the new token search implementation is causing duration of reads, rows scanned, cpu utilization, and query latency to increase significantly We also see a large number of serialization errors, though that [may be a red herring?](https://github.com/medplum/medplum/issues/7019) Example query
Copy code
SELECT "Patient"."id", "Patient"."lastUpdated", "Patient"."content" FROM "Patient" WHERE ("Patient"."deleted" = 'false' AND "Patient"."projectId" IN ('0123456-0123-0123-0123-01234567','0123456-0123-0123-0123-01234567' AND "Patient"."__identifier" @> ARRAY['0123456-0123-0123-0123-01234567']::UUID[])) LIMIT 3
Explain output
Copy code
Limit (cost=0.00..169.32 rows=3 width=1085)
-> Seq Scan on "Patient" (cost=0.00..329770.80 rows=5843 width=1085)
Filter: ((NOT deleted) AND (__identifier @> '{0123456-0123-0123-0123-01234567}'::uuid[]) AND ("projectId" = ANY ('{0123456-0123-0123-0123-01234567,0123456-0123-0123-0123-01234567}'::uuid[])))
Things we have tried: 
enable_seqscan off
Saw it referenced [here](https://github.com/medplum/medplum/issues/3860) (recognizing this isn't documented or intended for production), it did change the queries from sequential scan to index scan but minimal impact
Copy code
Limit (cost=0.43..178.13 rows=3 width=1083)
-> Index Scan using "Patient_projectId_idx" on "Patient" (cost=0.43..365719.35 rows=6174 width=1083)
Index Cond: ("projectId" = ANY ('{0123456-0123-0123-0123-01234567,0123456-0123-0123-0123-01234567}'::uuid[]))
Filter: ((NOT deleted) AND (__identifier @> '{0123456-0123-0123-0123-01234567}'::uuid[]))
enable_indexscan off
This was just to see the impact, seems to solve this specific problem but also seems highly likely to cause issues elsewhere...
Copy code
Bitmap Heap Scan on "Patient" (cost=2470.04..53353.51 rows=5843 width=1085)
Recheck Cond: (__identifier @> '{0123456-0123-0123-0123-01234567}'::uuid[])
Filter: ((NOT deleted) AND ("projectId" = ANY ('{0123456-0123-0123-0123-01234567,0123456-0123-0123-0123-01234567}'::uuid[])))
-> Bitmap Index Scan on "Patient___idnt_idx" (cost=0.00..2468.58 rows=5843 width=0)
Index Cond: (__identifier @> '{0123456-0123-0123-0123-01234567}'::uuid[])
We also tried changing from batch bundles to individual upserts with no significant improvement. Other details- - PG 16.8, 16vCPU, 128GB - All migrations for each upgrade completed successfully - No findings when running 
$db-schema-diff
operation - 
$db-invalid-indexes
operations returns 
{"resourceType":"Parameters"}
- It seems the 
LIMIT 3
from the generated query is what's causing it to use the sequential scan. Removing it leverages bitmap heat scan
k
could you re-run the query with this?
Copy code
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
more info this way
n
Hey @Karl - thanks for taking a look and congrats on joining the medplum team The latest updates here are in the [github issue](https://github.com/medplum/medplum/issues/7310) I've recovered performance in each environment through a combination of tweaking 
random_page_cost
, 
effective_cache_size
, and 
autovacuum_analyze_threshold/autovacuum_vacuum_threshold
. I'll work on getting a new env stood up to repro and provide the more detailed 
EXPLAIN
, since that will definitely be helpful in determining root cause
92 Views
PreviousNext
Powered by