cds-dbs icon indicating copy to clipboard operation
cds-dbs copied to clipboard

parked: fix(sqlite): Reduce expand materialization for SQLite

Open BobdenOs opened this issue 10 months ago • 0 comments

When processing queries that have ORDER BY or HAVING clauses. SQLite materializes the whole result before applying them. Which has to happen before LIMIT and OFFSET can be applied. Therefor moving the expand expression out of the native query prevents them from being materialized.

When applying the same approach to Postgres there is no change in performance. Indicating that Postgres is able to identify what is required to be materialized and what can be materialized after reducing the result set.

An impacted query was found in the sflight example application:

-- before
 95.92 → 108.79 =  12.88 ms - better-sqlite3 - SELECT json(jsonb_insert('{}','$."BeginDate"',BeginDate,'$."Book ...

--- after
 76.72 → 78.70  =   1.98 ms - better-sqlite3 - SELECT json(jsonb_insert('{}','$."BeginDate"',BeginDate,'$."Book ...

BobdenOs avatar Mar 27 '24 13:03 BobdenOs