How a SQL query runs in InventDB

The cost of a query is set by how much data the engine reads to answer it. A count taken from metadata reads a few bytes; the same count computed by opening every record reads the whole table. InventDB's query engine is organised around that difference. It answers from indexes, counts and column files where it can, and parses whole records only when nothing else will do.

This article follows one SELECT from the HTTP request to the rows that come back. Records are JSON documents, a table is a namespace and type such as pms.leases, and the SQL endpoint accepts SELECT only; records are written through the REST API.

The request and its limits

A query is a POST to /sql with a bearer token. The response is a JSON array of rows; adding ?metrics=1 wraps the rows with the elapsed time and the row count. Our example asks for the five most expensive active leases at or above 2,000:

POST /sql?metrics=1
Authorization: Bearer <token>
Content-Type: application/json

{ "sql": "SELECT unit, tenant, rent FROM pms.leases WHERE status = 'Active' AND rent >= 2000 ORDER BY rent DESC LIMIT 5" }

Before the engine sees the text, the server lists every table the statement names and checks the caller's grant on each one; a single missing grant returns 403. The query then runs on a pool of blocking threads, apart from the threads that serve HTTP, so a long scan cannot stall other requests. Each query has a wall-clock budget, five minutes by default, and if the client disconnects the engine stops at its next budget check.

Row rules are joined before parsing

If the caller has a row rule on any table in the statement, the engine rewrites the SQL first, joining each rule to the WHERE clause with AND. The rewrite happens inside the engine, so it applies equally to SQL from the console, MCP tools and reports. Because the rule becomes an ordinary predicate, the planner treats it like any other condition and can use an index for it. Administrators are not filtered. The row-level security article covers this step in detail.

Parsing and three cheap passes

A short rewrite runs before the parser. It quotes a column whose name collides with a SQL function, such as floor, value or level, unless an opening parenthesis follows it, so a schema does not have to avoid those names. It also turns a path into an array, such as message.content[0].text, into a single identifier. The parser then produces a syntax tree, and three passes run over it before any planning:

• Constant folding. A scalar function whose arguments are all literals is evaluated once: LOWER('Plumbing') becomes 'plumbing' and ABS(-5) becomes 5. A predicate such as trade = LOWER('Plumbing') can then use the index on trade.
• Placeholder check. InventDB does not bind parameters on the server, so values are written into the SQL text. A statement that still contains :name, ? or $1 is refused with an error that names the placeholder, instead of running with the placeholder read as null.
• Constant-false check. A WHERE or HAVING that is false from literals alone, such as WHERE 1 = 0, returns an empty result without planning.

Statements the engine does not run are refused with a specific message. INSERT, UPDATE, DELETE and DDL return Unsupported statement type; WITH and UNION each return an error that names the construct.

A planner with rules and no statistics

The planner turns the tree into a plan with one strategy per clause: filtering, joining, grouping, aggregating, ordering, limiting and DISTINCT. It keeps no histograms, estimates no cardinalities and never compares two candidate plans. Each choice follows from the syntax of the clause and from which fields have an index, which by default is every field. The same query always gets the same plan. For the WHERE clause, the rules look like this:

A condition that wraps a column in a function, such as LOWER(tenant) = 'ann lee', cannot use an index. The planner declines that query and the general executor, described below, runs it instead.

Joins are planned the same way, from the columns in the ON clause. When the join reaches the right-hand table through its _id, as in l.property_id = p._id, the planner uses a foreign-key join that fetches each right-hand record by id. When the left table's _id matches a field on the right, it uses an inverse join through the right table's index on that field. Two tables joined on a column with the same name get a sibling join, and any other equality becomes a hash join built from the right-hand table. Only INNER and LEFT OUTER joins on an equality are accepted.

Four ways to answer

Once every clause has a strategy, the planner labels the plan with an execution path. The label comes from the aggregate and grouping strategies, and it decides how much the engine reads:

Metadata only answers COUNT(*), MIN and MAX with no filter: the count from each segment's record count, the extremes from the bounds of the field's index. Index aggregates compute counts, sums, averages and extremes over indexed fields from the per-value count trees and the column files built when a segment is sealed. Ids only handles a GROUP BY on an indexed field by walking index entries, so it never parses a record. Everything else takes the full pipeline: filter, join, fetch records, group, project, order and limit.

ORDER BY with LIMIT, without sorting everything

Our example wants five rows ordered by rent. Sorting every matching lease to keep five would waste almost all of the work, so before its general machinery the full pipeline tries a set of top-N paths:

• A range on the ORDER BY field, alone or with one IN or equality on another indexed numeric or date field, is answered by an ordered merge across the segments' indexes that stops once it has n rows.
• A compound WHERE with ORDER BY on a numeric or date field ranks every candidate id by its indexed value, in a heap that never holds more than n entries.
• Two further paths handle ORDER BY on several columns, so ties on the first column are broken correctly at the LIMIT boundary.

Our query has an equality on a text field and a range on rent, so it takes the second path. The engine intersects the ids for status = 'Active' and rent >= 2000, ranks them by the rent stored in the index, and fetches five records. These paths read the immutable indexes of sealed segments and the in-memory active segment, take no locks and use no cache. The reading-less article covers the ordered walk in more depth.

The general executor

Some queries skip the planner's paths altogether. Statements inside a transaction, a WHERE with IN (SELECT ...), and expressions using CURRENT_DATE, NOW() or arithmetic between two columns go straight to the general executor. So do the shapes the planner declines: a function around a column in WHERE, semantic search with MEANING(), and some joins with filters on the joined table. The general executor walks the syntax tree, loads the records it needs and evaluates each clause directly. An IN (SELECT ...) subquery runs once and becomes a list before the outer query is evaluated.

This is the slower path, and it is also why the fast paths can stay narrow: any of them can decline a shape it does not serve exactly, and the query still gets a correct answer. After any path returns, scalar functions in the SELECT list, such as ROUND, UPPER or YEAR, are applied to the rows the same way, so the output never depends on which path produced it.

What the engine does not do

• SELECT only. Records are written through the REST API, as single calls, batches or transactions.
• No set operations or derived tables. WITH, UNION, INTERSECT and EXCEPT are refused, as are a subquery in FROM, RIGHT, FULL and CROSS joins, and joins on anything other than equality.
• No server-side parameters. Your code writes values into the SQL text, so it must escape quotes inside string values.
• No statistics. The planner will not discover that a different join order would suit your data better. It does what the shape of the query says, and a hash join always builds from the right-hand table, whatever the sizes.

What it costs

In our measurements, parsing, folding and planning a short statement takes about 150 to 230 microseconds. After that the cost depends on the path: a metadata answer reads one count per segment, a top-N path costs roughly the index entries it ranks plus n record fetches, and the general executor costs the records it scans.

On our published 143-query set over 10 million records, InventDB is 6.3x PostgreSQL, warm, with InventDB encrypted and PostgreSQL 18 tuned and unencrypted. The categories PostgreSQL wins are published alongside on our benchmarks page, together with how the runs are set up.

Writing queries that take the fast paths

• Filter on bare columns. Text equality ignores the case of ASCII letters, so status = 'active' already matches 'Active', and LOWER() in WHERE only costs you the index.
• Put wildcards in the literal. Write description LIKE '%leak%'; a pattern that starts with text, LIKE 'B%', gets a prefix lookup.
• Pair ORDER BY with LIMIT on an indexed field when you only need the first rows.
• Join on _id where the data allows, so the join fetches right-hand records by id.
• Count and group on plain fields. Both are answered from index structures without reading records.

The same rules hold on InventDB Serverless and InventDB SOAR, because both run the same engine. In InventDB SOAR, the AI that writes SQL for analytics and reports is given the same guidance, and it shows you the SQL it ran so you can check it.