Postgress index effectively disabled when searching for Greek names

[Tony Hazirakis]

Dear all

I have installed Orthanc version: 1.11.3 on Windows 10 along with the  PostgreSQLplugin used for indexing only. I have sucessfully imported thousands of our prior images and studies using the dcmsend utility from dcmtk. Some of out patient names are Greek and so I have changed then "DefaultEncoding"  to "Greek" and the C-Find results come back ok. The problem I am facing is that when I search, through the Orthanc explorer for example, for names staring with Greek characters, the delay is huge. There is no such problem with Latin names.

I have run Orthan with a verbove log and observed that when I search for latin names the database indexing seems to work fine, as indicated by the two following lines:

I0409 10:39:40.947163 ServerContext.cpp:1445] Number of candidate resources after fast DB filtering on main DICOM tags: 8
I0409 10:39:40.962692 ServerContext.cpp:1593] Number of matching resources: 8

But when I search for Greek names the db filtering seems to be turned off, as indicated by the following two lines:

I I0409 10:29:08.881887 ServerContext.cpp:1445] Number of candidate resources after fast DB filtering on main DICOM tags: 81092
I0409 10:30:39.915161 ServerContext.cpp:1593] Number of matching resources: 14

Furthermore I turned on the PostgreSQL query log on, to monitor what queries does Orthanc perform during these searches. It seems that during the Latin name searches a LIKE were part is included in the query to filter only the names of interest, as you can see from the following postgresql  log lines, where I search for CHAZIRAKIS*:

2023-04-09 10:45:48.874 EEST [5788] LOG:  execute 8ba40901-4b98-4017-93a9-2e0008835b15: SELECT studies.publicId, MIN(instances.publicId) FROM (SELECT studies.publicId, studies.internalId FROM Resources AS studies INNER JOIN DicomIdentifiers t0 ON t0.id = studies.internalId AND t0.tagGroup = 16 AND t0.tagElement = 16 WHERE studies.resourceType = 1 AND t0.value LIKE $1 ESCAPE '\') studies INNER JOIN Resources series    ON series.parentId    = studies.internalId INNER JOIN Resources instances ON instances.parentId = series.internalId GROUP BY studies.publicId
2023-04-09 10:45:48.874 EEST [5788] DETAIL:  parameters: $1 = 'CHAZIRAKIS%'

But when I search for a Greek name, no matter what I input in the Orthanc explorer form, the "t0.value LIKE $1" vanishes and there is no relevant parameter set, as you can see from the following postgresql log lines:

2023-04-09 10:47:24.809 EEST [13968] LOG:  execute ea722c26-ae6b-4ee9-acb3-4876b24ef15c: SELECT studies.publicId, MIN(instances.publicId) FROM (SELECT studies.publicId, studies.internalId FROM Resources AS studies INNER JOIN DicomIdentifiers t0 ON t0.id = studies.internalId AND t0.tagGroup = 16 AND t0.tagElement = 16 WHERE studies.resourceType = 1) studies INNER JOIN Resources series    ON series.parentId    = studies.internalId INNER JOIN Resources instances ON instances.parentId = series.internalId GROUP BY studies.publicId

After this query the whole list of 8000 results is examined one by one to find a match for the Greek name. The following repeated log lines indicate so:

2023-04-09 10:47:27.794 EEST [12348] LOG:  execute d6d931a7-621c-48a6-a9cf-e3f6e2b4dfe0: SELECT internalId, resourceType FROM Resources WHERE publicId=$1
2023-04-09 10:47:27.794 EEST [12348] DETAIL:  parameters: $1 = '387f9198-16c91180-3ced7ce1-cce95cae-e9437a4b'
2023-04-09 10:47:27.796 EEST [12348] LOG:  execute e1d16d25-9c76-43cc-8329-4c7af04b3ee8: SELECT * FROM MainDicomTags WHERE id=$1
2023-04-09 10:47:27.796 EEST [12348] DETAIL:  parameters: $1 = '112214'
2023-04-09 10:47:27.797 EEST [12348] LOG:  execute 7e9165e8-54ff-4ab4-9bde-b89dba102a90: SELECT parentId FROM Resources WHERE internalId=$1
2023-04-09 10:47:27.797 EEST [12348] DETAIL:  parameters: $1 = '112214'
2023-04-09 10:47:27.797 EEST [12348] LOG:  execute e1d16d25-9c76-43cc-8329-4c7af04b3ee8: SELECT * FROM MainDicomTags WHERE id=$1
2023-04-09 10:47:27.797 EEST [12348] DETAIL:  parameters: $1 = '112212'
2023-04-09 10:47:27.798 EEST [12348] LOG:  execute 7e9165e8-54ff-4ab4-9bde-b89dba102a90: SELECT parentId FROM Resources WHERE internalId=$1
2023-04-09 10:47:27.798 EEST [12348] DETAIL:  parameters: $1 = '112212'
2023-04-09 10:47:27.798 EEST [12348] LOG:  execute e1d16d25-9c76-43cc-8329-4c7af04b3ee8: SELECT * FROM MainDicomTags WHERE id=$1
2023-04-09 10:47:27.798 EEST [12348] DETAIL:  parameters: $1 = '112211'

The whole process takes minutes to complete and I do get the correct results in Orthanc explorer, but this unnatural delay in searches is too big and also breaks communications with dicom viewers. Do you have any ideas or suggestions on how to solve this problem?

Best Regards

Tony Chazirakis