I need help with indexes for a new sql-on-fhir queries

[pavlushkin]

Problem

Some sql-on-fhir queries work very slow. We used indexes to optimise queries, but is does not work anymore, probably, because of that - resource || jsonb_build_object('id', id)) as resource.

Questions

1. How to add indexes that works for new SQL-on-FHIR ViewDefinitions?
2. If it is not possible now, should we use another materialisation method (materialized view or table)? What disadvantages of this options should we know (like, manual trigger for update data etc.)?

It looks like a significant update compared with a previous version.

We use a ViewDefinition:

{
  "description": "Example",
  "where": [
    {
      "path": "app = 'some-app'"
    },
    {
      "path": "code.coding.exists(system='some-system' and code='some-code')"
    }
  ],
  "name": "example",
  "extension": [
    {
      "url": "materialization",
      "value": {
        "code": "view"
      }
    }
  ],
  "status": "active",
  "id": "example",
  "resource": "Observation",
  "select": [
    {
      "column": [
        {
          "name": "id",
          "path": "id"
        }
      ]
    }
  ]
}

It produces a query:

SELECT jsonb_path_query_first( r.resource , '$ . id' ) #>> '{}' as "id" 
FROM ( SELECT (resource || jsonb_build_object('id', id)) as resource, ts, cts, status FROM "observation" ) as r 
WHERE jsonb_path_match( resource , '$ . app == "some-app"' ) 
  AND jsonb_path_match( resource , 'exists( $ . code . coding ? ( @ . system == "some-system" && @ . code == "some-code" ) )' ) 
LIMIT 100

An attempt to add a GIN or Btree index produces an error: "ERROR: functions in index expression must be marked IMMUTABLE"

It currently affects some services in production.

Thank you for your help!

Aidbox version

version: '2312' channel: latest commit: 2ee3bf78f zen-fhir-version: 0.6.30-1

[Yngwarr]

@pavlushkin Thank you for reaching out! What you've run into is a bug, we've fixed it on the edge channel and the fix will be available on the latest channel in a couple of days.

On the fixed version your ViewDefinition example will produce the following query:

SELECT id as "id"
FROM "observation" as r
WHERE jsonb_path_match(resource, '$ . app == "some-app"')
  AND jsonb_path_match(resource, 'exists( $ . code . coding ? ( @ . system == "some-system" && @ . code == "some-code" ) )')
LIMIT 100

which should be the same query you would've got before the braking update.

[Nesmeshnoy]

@pavlushkin please test it on our side and reopen the issue if there're any problems