From shape to queries

3 / 11 · planning in the full document · PDF

The planner turns a shape into a tree of queries: one root query, and one per to-one reference, per collection and per variant table, at any depth, each batched by the keys of the rows above it. The number of queries depends on the shape, never on the number of rows, and no query joins two collections. The plan, and the SQL of each query, can be printed before anything runs.

The plan of TaskView on PostgreSQL as five queries with their SQL. $root selects id, name and assignee_id as $ref.assignee from task with a filter, order and limit. assignee, a to-one by $ref.assignee, selects from person where id = ANY($1), with the distinct $ref.assignee keys of the root rows. notes, to-many by task_id, selects id, task_id as $parent and body from note where task_id = ANY($1), ordered by created_at and id. subtasks, to-many by parent_id, selects from task where parent_id = ANY($1). subtasks.assignee, to-one, takes the $ref.assignee keys of the subtasks. Notes below: keys flow down and rows flow up; one array on PostgreSQL or a padded list elsewhere; decoding by alias. Right: the view's code, and for 20 tasks with 4 subtasks each: 5 queries with Mabat, 181 queries one per row, or one query with joins that repeats every note for every subtask.

One query per relationship. A view is loaded by one root query plus one query per reference, collection and variant table, each batched by the keys of the rows above: never one query per row, and never a join of two collections MPA-PLAN-1. Each query is named by its path, the root being $root MPA-CORE-5; overrides and nested arguments address queries by these names. mabat::plan::<T>() returns the plan and Mabat::explain::<T>() its SQL, generated or overridden MPA-PLAN-5.

Aliases do the joining. Every column is selected under its path; the system aliases are $key, $parent (the parent key of a collection row), $ref.<field> (a reference's foreign key), <prefix>$tag, $index and $map_key MPA-PLAN-2. Rows are decoded by alias, never by position, so a query may select its columns in any order MPA-PLAN-3 — which is what lets a DBA rewrite it. Children are attached in the order of their query: order_by, then the key MPA-LOAD-8.

The arithmetic. For 20 tasks with 4 subtasks each, loading this view row by row — the pattern lazy loading falls into — costs 1 + 20 + 20 + 20 + 80 = 181 queries. One query with joins costs one round trip but returns, for each task, every note repeated for every subtask. Mabat runs the five queries of the plan, whatever the number of rows. Keys are bound as one array on PostgreSQL, and on MySQL and SQLite as a list padded to a power of two so statements are reused, at most 1,000 to a child statement MPA-DB-4.