[BitBucket user: Денис Смирнов]
[BitBucket date: 2017-03-25.12:36:08]
Hi,
I am using Orthanc 1.2.0 with PostgreSQL plugin and have successfully indexed ~ 3Tb of DICOM images. But right now I've faced a problem than query/retrieve works not very fast. After small recerch I found out that the problem is in the way Orthanc processes query/retrieve translaion to SQL. So, here is a query for DICOM studies from 2017-03-20 to 2017-03-22:
```
#!bash
findscu -S -k QueryRetrieveLevel=STUDY -k PatientID -k StudyDate=20170320-20170322 -k StudyDescription -k StudyInstanceUID -aec ORTHANC -aet FINDSCU pacs.viveya.local 4242
```
And in PostgreSQL it translates to two heavy queries
```
#!sql
SELECT d.id FROM DicomIdentifiers AS d, Resources AS r WHERE d.id = r.internalId AND r.resourceType='1' AND d.tagGroup='8' AND d.tagElement='32' AND d.value>='20170320';
SELECT d.id FROM DicomIdentifiers AS d, Resources AS r WHERE d.id = r.internalId AND r.resourceType='1' AND d.tagGroup='8' AND d.tagElement='32' AND d.value<='20170322';
```
As you can guess is is not a good idea because the second query returns all DicomIdentifiers.id before 2017-03-22. In my case it is 135094 rows that would be intersected with 593 rows after 2017-03-20 by Orthanc. The main problem is in getting from index 135094 rows with rechecking some of them in heap due to mvcc of PostgreSQL. May be it is not a problem if you have ssd and small tables and indexes but in my situation it is pain.
A better solution would be to use a SQL query with "between" or ">= and <=" in one statement. They work ~500 times faster in my case.
```
#!sql
SELECT d.id FROM DicomIdentifiers AS d, Resources AS r WHERE d.id = r.internalId AND r.resourceType='1' AND d.tagGroup='8' AND d.tagElement='32' AND d.value between '20170320' and '20170322';
```
But the problem is that Orthanc rigth now is implementing only four constraint types
```
#!c++
enum IdentifierConstraintType
{
IdentifierConstraintType_Equal,
IdentifierConstraintType_SmallerOrEqual,
IdentifierConstraintType_GreaterOrEqual,
IdentifierConstraintType_Wildcard /* Case sensitive, "*" or "?" are the only allowed wildcards */
};
```
But I think it can be a good idea to implement an additional fifth IdentifierConstraintType_Range solving the problem above. It can be supported by extension (like PostgreSQL one) if developers deside to use it. What do you think about this idea and is it a dificult task?
P.S. I am not a C++ programmer so I can't sugest a patch to this issue((
|