Subject: ATLAS System : PostgreSQL Search Query Improvement Request!

[stmghj (Hyeongjun Gu)]

Hello!

A while ago, at a real hospital operation site where customers are using the ATLAS system based on PostgreSQL large amount of data
a performance problem occurred, I checked the system.

The cause of the performance problem was found to have a problem in the structure of the Query statement created in PostgreSQL
as a result of condition search in “Cohort Definitions” of the ATLAS system.

The query statement created in PostgreSQL is also requested to create a query in the same way as the results of the “Template OHDSI.SQL” and “MSSQL Server” tabs.

====================================================================================
[Refer]

Please refer to the attached picture file.

---

WITH primary_events (event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id) AS (
…
WHERE P.ordinal = 1
– End Primary Events

Upper part

---

CREATE TEMP TABLE qualified_events
AS

Move it over the upper part

==>

---

CREATE TEMP TABLE
primary_events (event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id) AS (
…
WHERE P.ordinal = 1
– End Primary Events

…

CREATE TEMP TABLE qualified_events
AS
SELECT
event_id, person_id, start_date, end_date, op_start_date, op_end_date, visit_occurrence_id

FROM
(
…

If the Query is changed to, the result is the same and the performance is improved.
(I tested and confirmed it several times)

I analyzed Source code.
As a result,
C:\Git\OHDSI\WebAPI\target\WebAPI\WEB-INF\lib\SqlRender-1.6.3.jar
In the file
\org\ohdsi\sql\SqlTranslate.class
There seems to be an improvement.

I found that the SqlTranslate.java file is included in the SqlRender-1.6.3.jar file when compiling “mvn clean …”.

https://ohdsi.github.io/SqlRender/news/index.html
Looking at the OHDSI Forum notice, I noticed that SqlRender improvements are in progress in the OHDSI Forum.

Besides,
OHDSI Forum Notice
From WebAPI/Atlas Release v2.8.0 (stale do not use) · Issue #981 · OHDSI/WebAPI · GitHub
I saw that the OHDSI Forum is planning to release WebAPI/Atlas Release v2.8.0.

When developing the WebAPI/Atlas Release v2.8.0 release,
As a result of condition search in “Cohort Definitions” of ATLAS system, the structure of Query statement created in PostgreSQL is improved.
Please consider and improve PostgreSQL performance by including it as soon as possible.

Thank you!

[Konstantin_Yaroshove (Konstantin Yaroshovets)]

Hi @stmghj,

Thank you for suggestion.
I think the best option will be to open GitHub ticket instead of forum post. You can find issue tracker here:

OHDSI/WebAPI

[Chris_Knoll]

In addition, I’ve posted about the postgres limitatoin to CTEs here:

Auto-ganerated SQL for postgres problem in the Atlas

Hello, Hansu Chung, I’m sorry for the difficulties you’ve having with PostgreSQL. There is a known behavior with common table expressions (CTE) prior to version 12 where the query in the CTE is materialized as a separate, un-indexed table. However, things have improved with version 12 and 13: common table expressions are optimized together with the rest of the query such that indexes and other query optimizations can be applied. This is when the CTE is NOT MATERIALIZED. You can read about …

If you can try PG 13.0, there’s optimizations there.

The reason why this request is tricky is that some of the primary criteria results in a full table copy (such as, ‘find all death’ or ‘find all visits’) and with the ‘non-materialized’ mode in PG 13, that means that it can leverage the additional criteria outside of the CTE to limit the intial primary events to a smaller working-set. If we pull out the CTE into a temp table, the temp table will simply be a copy of the domain table, and that’s something I’d like to try to avoid, but I’m not saying that there isn’t something we can do…just pointing out that we may need to look at alternatives.