cds-dbs
cds-dbs copied to clipboard
parked: fix(sqlite): Reduce expand materialization for SQLite
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 ...