mORMot Open Source

Kabiri

Member

Registered: 2024-06-22

Posts: 107

JOIN across external PostgreSQL tables runs one SELECT per row in the

Hi Arnaud and all,

Setup: mORMot 2 (commit 8d3b6b4fb, 2026-07-20), FPC + Linux64.
TRestServerDB with an in-memory SQLite3 main DB; every table is mapped to
PostgreSQL through VirtualTableExternalRegister(Model, TOrmX, Props) using
TSqlDBPostgresConnectionProperties.

Single-table reads work perfectly: AdaptSqlForEngineList turns them into
a single PostgreSQL statement, even with GROUP BY.

But any multi-table read (a JOIN or a correlated subquery) run through
Orm.ExecuteList() is rejected by DoAdaptSqlForEngineList and executed by the
SQLite3 virtual engine. TOrmVirtualTableCursorExternal.Search then issues one
"select <all fields> from public.X where ...=?" for each xFilter call, which
means once per outer row.

Simplified example:

  SELECT t.rowid AS id, t.Title, ws.Name AS StepName,
    (SELECT COUNT(*) FROM Attachment a WHERE a.TaskID=t.rowid) AS HasAttachment,
    (SELECT InternalStatus FROM TaskUserStatus
      WHERE TaskID=t.rowid AND UserID=t.AssigneeID
      ORDER BY rowid DESC LIMIT 1) AS InternalStatus
  FROM Task t
  LEFT JOIN WorkflowStep ws ON ws.rowid=t.CurrentStepID
  WHERE t.TenantID=? AND t.AssigneeID=?
  ORDER BY t.UpdatedAt DESC LIMIT 100

For N result rows we measured exactly 8*N+2 PostgreSQL statements per call
(13 rows -> 106 statements):

  select ... from public.Task where TenantID=? and AssigneeID=? order by UpdatedAt desc
  select ... from public.WorkflowStep where ID=?                        -- x N
  select ... from public.TaskUserStatus where TaskID=? and UserID=?
         and TenantID=? order by ID desc                               -- x N, LIMIT 1 not forwarded
  select ... from public.Attachment where TaskID=?                      -- x N
  select ... from public.ActivityLog where TaskID=? and TenantID=?
         and Action=? order by CreatedAt desc, ID desc                  -- x N

What I found reading the source:
- vt_BestIndex only maps EQ/GT/LE/LT/GE/MATCH, so IN, <>, LIKE and IS NULL
  are evaluated by SQLite after a wider fetch.
- ComputeSql always starts from fSelectAllDirectSQL (all columns), and LIMIT
  is never forwarded.
- The outer ORDER BY/LIMIT is applied only after every row, and its
  subqueries, has been computed, so paging does not reduce the cost.

Questions:
1. Is this expected? Is there an option I missed to forward the whole
   statement to the external DB when every referenced table belongs to the
   same TSqlDBConnectionProperties?
2. If not, is the recommended practice to run such read-only queries directly,
   e.g. TRestStorageExternal.Instance(TOrmTask, Server).Properties.Execute(...)
   plus FetchAllAsJson(true)? Are there caveats compared to ExecuteList:
   per-thread connections, a transaction opened by the ORM in the same thread,
   the JSON layout versus TOrmTable.GetJsonValues(true), or the lower-cased
   column names returned by PostgreSQL?
3. Is "WITH x AS MATERIALIZED (...)" inside the virtual engine a reasonable
   intermediate workaround, or does it have pitfalls with external virtual
   tables?
4. Are there plans for, or would a pull request be welcome for, forwarding
   LIMIT in ComputeSql, fetching only the columns actually used
   (sqlite3_index_info.colUsed), or handling IN through sqlite3_vtab_in()
   (SQLite 3.38+)?

Thanks a lot for mORMot!

Last edited by Kabiri (2026-09-11 17:32:37)