Query optimization

Query optimization src/query-optimizers is a set of opt-in performance tools: EXPLAIN plans with recommendations, dialect-aware query hints, slow-query logging, batched bulk writes, and two in-process caches. None of it is wired into Prorm, Model or the dialect pipeline automatically — you construct what you want and call it explicitly. That is deliberate: these tools change how statements run, and that should be a decision you make, not a default you inherit. Everything below is also usable standalone — ExplainPlanGenerator, QueryHintManager, SlowQueryLogger, BatchQueryOptimizer, PreparedStatementCache — if you would rather not take the facade. EXPLAIN plans The statement issued depends on the dialect: Dialect | Statement | --- | --- | MySQL / MariaDB | EXPLAIN [ANALYZE] [FORMAT=JSON\|TREE] <sql> | PostgreSQL | EXPLAIN [ANALYZE] [BUFFERS] [TIMING] [(FORMAT JSON)] <sql> | SQLite | EXPLAIN QUERY PLAN <sql> | Oracle, SQL Server, others | a bare EXPLAIN <sql>, with no parsing | Plan parsing and recommendations exist only for MySQL/MariaDB, PostgreSQL and SQLite. What each looks for: Dialect | Flagged as a problem | --- | --- | MySQL / MariaDB | type: ALL scans, missing possiblekeys/key, Using filesort, Using temporary | PostgreSQL | Seq Scan nodes anywhere in the plan tree, nodes estimating PlanRows > 10000 | SQLite | SCAN steps (as opposed to SEARCH, which means an index was used) | Each recommendation carries a type (INDEX, OPTIMIZATION, REWRITE, STATISTICS) and a severity (HIGH, MEDIUM, LOW). They are heuristics over the plan text, not a cost model — read them as prompts to investigate. enableExplain defaults to process.env.NODEENV === 'development'. Set it explicitly if you want plans in production. Query hints Hints are rendered into a /+ … / comment placed immediately after SELECT (or prepended for non-SELECT statements), and only for hints the dialect actually supports: Dialect | Supported hints | --- | --- | MySQL / MariaDB | FORCEINDEX, USEINDEX, IGNOREINDEX, JOINFIXEDORDER, MAXEXECUTIONTIME, SQLBIGRESULT, SQLSMALLRESULT, SQLBUFFERRESULT, SQLCACHE, SQLNOCACHE, INDEXMERGE | PostgreSQL | MAXEXECUTIONTIME (as SET LOCAL statementtimeout), plus SEQSCAN / NOSEQSCAN / INDEXSCAN / NOINDEXSCAN — these need the pghintplan extension installed to have any effect | SQLite | OPTIMIZE, QUERYPLAN | Everything else | none — applyHints() returns the SQL unchanged | Unsupported hints are dropped silently rather than producing SQL the engine rejects. Note that the exported MERGEHASH and NOMERGE constants (and NESTEDLOOP) have no dialect entry, so they are always filtered out today. setEnabled(false) turns hint rendering off globally without changing call sites. Slow-query logging SlowQueryLogger is fed manually — it does not hook the query pipeline. If you use optimizer.executeQuery() it is called for you; otherwise call logQuery() yourself after timing a statement. Analysis helpers on the logger: Method | Returns | --- | --- | getStatistics() | count, avg/min/max, p50/p95/p99 duration | getSlowQueries() | entries over the threshold | getQueriesByType() | grouped by SELECT/INSERT/… | getQueriesOnTable(name) | entries whose SQL mentions the table | findSimilarQueries() | grouped after replacing literals with ? | exportLogs() | JSON dump | For always-on logging of every query, use the connection's own logging option instead — it is wired into the pipeline. Batched bulk writes BatchQueryOptimizer chunks large arrays and emits one statement per chunk instead of one per record. Operation | SQL per chunk | Default chunk | --- | --- | --- | optimizeBatchInsert | INSERT INTO t (…) VALUES (…), (…), … | 1000 | optimizeBatchUpdate | UPDATE t SET col = CASE id WHEN … THEN … END WHERE id IN (…) | 500 | optimizeBatchDelete | DELETE FROM t WHERE id IN (…) | 500 | Values are always bound parameters, with the placeholder style each dialect uses ($1, @p1, :1, ?) — never interpolated literals. If a chunk's batched statement fails, the optimizer retries that chunk record-by-record so one bad row does not lose the other 999; stopOnError: true rethrows instead, and skipDuplicates swallows duplicate-key failures during the fallback. For ordinary application writes, Model.bulkCreate() and Model.bulkUpdate() are the simpler path — see Bulk operations. The two caches Both live in prepared-statement-cache.ts and both are process-local in-memory maps — they are not shared between workers and do not survive a restart. For a distributed cache see Caching. PreparedStatementCache Despite the name, this does not hold driver-level prepared-statement handles. It tracks statement shapes — how often a normalized SQL string has been seen and executed — so you can find your hottest statements. Keys are the SQL with whitespace collapsed, comments stripped and keywords upper-cased (normalizeSql: true), so the same query with different literals shares an entry. Supplying params appends them to the key instead. LRU eviction by last use at maxSize (default 500), TTL expiry at 1 hour, swept lazily on lookup or eagerly with prune(). getStats() gives size, hit rate, total execution time, evictions, and the top ten statements by hit count. QueryResultCache Caches actual result sets, default TTL 60 s. invalidateTable(name) evicts every entry whose SQL text contains that table name — a substring match, not a parse, so it is deliberately coarse: it over-invalidates rather than serving stale rows. It is not re-exported from the module barrel; import it from ts-prorm-orm/dist/query-optimizers/prepared-statement-cache if you want it standalone, or reach it via optimizer.getQueryResultCache(). Tying it together executeQuery() runs one statement through hints → result cache → statement cache → slow-query log: Note that the result cache is only consulted when the call supplies no params — a parameterised call always goes to the database. Housekeeping | Effect | --- | --- | getStats() | merged stats from the slow-query logger and both caches | clearCaches() | empty both caches | pruneCaches() | drop expired entries only | invalidateTableCache(table) | evict result-cache entries mentioning the table | A practical order of attack 1. Turn on query logging with benchmark: true and find the statements that are actually slow. 2. analyzeQuery() those, and read the recommendations. 3. Add the index the plan is asking for and re-check the plan. 4. Only then reach for hints, and only on MySQL/MariaDB where they are more than advisory. 5. Cache last — a cache hides a slow query, it does not fix one. Related reading query-optimizers — internals, file by file Logging — always-on query logging and slow-query events Caching — the L1/L2 Redis cache Indexes & constraints Bulk operations