[BitBucket date: 2017-05-11.13:03:26]
I am using Orthanc in production for about a year (pg database ~ 3 gb, dicom data ~ 3k gb). I faced with many performance problems and some of them were fixed by improving indexes for dicomidentifiers and resources tables. Others can't be fixed right now cause they come from bad dicom-to-sql translation ([Issue41](bug 41)). So I want to suggest some index modification for pg plugin schema.
### Wildcard search ###
Our hospital uses RadiAnt. When you are searching patient by id in RadiAnt you are doing a wildcard search ("%patient_id%"). Btree indexes can't work with this kind of queries, but gin trigrams can.
```
#!sql
SELECT d.id FROM DicomIdentifiers AS d, Resources AS r WHERE d.id = r.internalId AND r.resourceType=$1 AND d.tagGroup=$2 AND d.tagElement=$3 AND d.value LIKE $4
```
This query is working very slow when pg uses sequence scan. So I changed a DicomIdentifiersIndexValues index from
```
#!sql
CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value);
```
to
```
#!sql
create extension pg_trgm;
create index dicomidentifiersindexvalues_new on dicomidentifiers using gin(value gin_trgm_ops);
drop index dicomidentifiersindexvalues;
alter index dicomidentifiersindexvalues_new rename to dicomidentifiersindexvalues;
```
### Time period search ###
When I am quering studies in period between date1 and date2, Orthanc core translates them into two sql queries and intersects there results
```
#!sql
SELECT d.id FROM DicomIdentifiers AS d, Resources AS r WHERE d.id = r.internalId AND r.resourceType=$1 AND d.tagGroup=$2 AND d.tagElement=$3 AND d.value>=$4
SELECT d.id FROM DicomIdentifiers AS d, Resources AS r WHERE d.id = r.internalId AND r.resourceType=$1 AND d.tagGroup=$2 AND d.tagElement=$3 AND d.value<=$4
```
It is easy to guess that the second query returns hundreds of thousands rows. It is a real problem when tables are hundreds of Mb. So to improve performance I had to avoid any work with heap and use only covering indexes. In this query two indexes should be modified: dicomidentifiersindex2 and resourcetypeindex from
```
#!sql
CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
CREATE INDEX ResourceTypeIndex ON Resources(resourceType);
```
to
```
#!sql
create index dicomidentifiersindex2_new on dicomidentifiers (taggroup, tagelement, value, id);
drop index dicomidentifiersindex2;
alter index dicomidentifiersindex2_new rename to dicomidentifiersindex2;
create index resourcetypeindex_new on resources (resourcetype, internalid);
drop index resourcetypeindex;
alter index resourcetypeindex_new rename to resourcetypeindex;
```
After that if autofreeze works well and updates visibility map we have index only scan in a date period query and never recheck heap. It improved my queries hundreds of times. I am not afraid of penalty on insert for a covering index cause pacs database is mostly for read operation, the number of columns is not horribly big and I haven't seen any problems with it in production.
|