#110645Ensure RootlineUtility respects the full visibility aspect
Description
RootlineUtility does not consistently consider the full visibility aspect when resolving related records and generating cache identifiers. This can cause hidden relations to be incorrectly included or omitted, or cached results to be reused across different visibility settings.
Relation resolution and cache identifiers should account for all relevant visibility settings. Extend the sys_refindex.lookup_rec index with ref_table to keep queries filtering by the referenced table efficient.
Reviews 1
Related issues 1
Files 4
History 46
-
Matthias Vogel changed Related to #110322 <span data-processor="relativetime" data-value="2026-09-07T09:45:37+00:00" > 09/07/26, 09:45 </span>
-
Gerrit Code Review pushed patch set 1 for main to change 95679 status
NewUnder Review <span data-processor="relativetime" data-value="2026-09-07T10:02:31+00:00" > 09/07/26, 10:02 </span> -
Gerrit Code Review pushed patch set 2 for main to change 95679 <span data-processor="relativetime" data-value="2026-09-07T11:24:21+00:00" > 09/07/26, 11:24 </span>
-
Matthias Vogel commented and changed attached queries.sql attached queries-output.txt attached report.txt attached rootline-performance-110645.tar.gz <span data-processor="relativetime" data-value="2026-10-19T09:04:00+00:00" > 10/19/26, 09:04 </span>
-
Matthias Vogel commented <span data-processor="relativetime" data-value="2026-10-19T09:24:28+00:00" > 10/19/26, 09:24 </span>
Tested SQLite and PostgreSQL against the same dataset. The clearest overhead across all three databases is the pages-only preview, mainly from planning/preparation.
Median time for the 15 relation queries, before → after, including preparation/planning and execution:
Hidden content Hidden pages MariaDB 10.11 SQLite 3.46 PostgreSQL 17 false false 5.658 → 5.384 ms 0.861 → 0.851 ms 5.400 → 5.462 ms false true 5.507 → 6.622 ms 0.859 → 1.000 ms 5.447 → 6.658 ms true false 5.698 → 7.218 ms 0.890 → 0.945 ms 5.885 → 6.350 ms true true 5.469 → 5.617 ms 0.899 → 0.892 ms 5.388 → 5.716 ms For content=false, pages=true, that is +20.2% / +16.5% / +22.2%, respectively. The smaller content-only increases on SQLite and PostgreSQL are not clearly distinguishable from measurement variability.
All plans retain indexed access. The extended index enables earlier filtering in MariaDB and SQLite. PostgreSQL still evaluates the OR expression as a subsequent filter, so its plans show no equivalent benefit.
SQLite and PostgreSQL each pass 12 regression tests with 24 assertions. Across all engines, 21,600 measured queries were evaluated. Original data remains unchanged; temporary databases were removed.
Absolute times should not rank the engines: SQLite uses PDO timing, while MariaDB/PostgreSQL provide server-side timings.
-
Matthias Vogel commented <span data-processor="relativetime" data-value="2026-10-19T12:42:51+00:00" > 10/19/26, 12:42 </span>
Multiple checks done and here is the report of the AI:
Recommendation: keep the visibility correction and extend the existing lookup_rec with ref_table. Do not add a separate second index. Before drawing a database-wide performance conclusion, repeat the dense fixture on SQLite and PostgreSQL; the 1,000-relations-per-page result is currently verified on MariaDB only.
The correctness fix and the index change have separate purposes. Previously, includeHiddenContent controlled related pages as well as other content. The patch respects includeHiddenPages independently. Both flags false and both flags true retain the same visibility predicates; mixed flag states require table-aware conditions. Keeping the original index would preserve correctness and indexed access, but gives up earlier filtering in the affected local query.
The initial fixture had 50,000 site pages and a 15-node rootline, but very few relations on that rootline. Increasing the hidden share to approximately 50% still showed no clear benefit from extending the key. With 500 media references and 500 category relations on each rootline page, exactly half hidden, the benefit became clear. The important workload characteristic is the relation count on the queried pages; the total site page count alone did not make the initial fixture representative of this case.
For the dense fixture, MariaDB 10.11.19 used three identical full-table copies, 10 warm-up rounds and 60 measured rounds in random order. Every cell below is a median in milliseconds for all 15 relation queries, including optimization and execution. The first column uses old SQL and the old key; the remaining columns use patched SQL:
Hidden content Hidden pages Before patch Patch + original key Patch + extended key Patch + extra index false false 41.337 40.691 42.815 42.201 false true 41.492 58.010 45.926 59.517 true false 71.186 74.930 75.322 75.789 true true 73.133 72.963 72.215 75.813 For hidden content=false / hidden pages=true, extending the existing key improves server time by 20.8% versus keeping the original key. Actual SELECT time including PDO, network and fetching improves by 21.3% (69.269 to 54.544 ms). Local access reads 3,750 rows instead of 7,500; captured local InnoDB page accesses fall from 22,930 to 11,696. Both exploratory 95% comparison intervals exclude zero. The other three states show no clear difference between the original and extended keys.
This does not mean the complete patch makes the query faster than before the fix. Pages-only preview is still 10.7% slower in the server metric than the pre-patch query, or 4.3% slower in actual SELECT time. Without the key extension, its server median would be about 40% above that pre-patch baseline.
The separate extra index was never selected in the captured MariaDB plans: the optimizer continued to choose the original lookup_rec. It provided no clear benefit on the dense fixture. On sparse fixtures it increased optimization time, producing clearly higher total timings. Keeping both indexes is therefore unsupported by the tested MariaDB workloads.
Earlier SQLite 3.46.1 and PostgreSQL 17.11 checks used the sparse fixture. All captured plans retained indexed access without full table scans. SQLite bytecode can read ref_table from the extended index before a table lookup. PostgreSQL retained a filter after its index scan, so the equivalent early-filtering benefit was not observed there. The visibility regression tests passed on both SQLite and PostgreSQL: 12 tests and 24 assertions per database.
The write algorithm is unchanged: unchanged relation hashes cause no writes; new hashes are bulk inserted and obsolete hashes deleted. Replacing a changed relation is INSERT followed by DELETE, not SQL UPDATE. INSERT, replacement and DELETE were benchmarked separately, both with a shared batch transaction and with statement-level autocommit, using 1,000 logical relations per batch and 30 measured rounds.
Representative replacement medians in milliseconds with statement-level autocommit, including commit:
Database Original key Extended key MariaDB 68.722 72.178 SQLite 508.393 502.502 PostgreSQL 65.418 70.633 No clear write-time difference was established for any measured operation or transaction mode: all exploratory 95% difference intervals include zero. This does not prove zero overhead or equivalence; MariaDB timing variation remained substantial, including in a controlled repeat. These write measurements used the earlier 342,718-row dataset and model database batches, not a complete DataHandler save or referenceindex:update run. Hash generation and deferred cleanup were outside the timed batches.
The definite cost is index space. On the original dataset, lookup_rec grew by 16.4% on MariaDB, 21.7% on SQLite and 22.0% on PostgreSQL. These percentages describe this one index, not the whole database. Commit settings and chunk sizes differ between engines, so absolute write times are not a database ranking.
The recommendation balances a demonstrated benefit for a relation-heavy pages-only preview against the larger index, with no clear write-time regression found so far. Appending ref_table retains the existing column order and the indexed access seen in the plans. A separate index has no demonstrated benefit. The next focused verification should use the same dense fixture on SQLite and PostgreSQL; their query-plan behavior differs from MariaDB.
All figures are warm query-level measurements. The dense fixture contains file and category relations, while the rootline page records are visible; hidden related-page behavior is covered by the separate regression tests. Absolute timings across fixture runs should not be attributed solely to a fixture change because host load also differs. All benchmark results were validated; source data/schema were checked unchanged during each run and temporary resources removed.
I compared commit e1f638daa8 with its parent on MariaDB 10.11.19, using site 268 (“Big Rootline”): 50,000 pages, 342,718 reference-index entries, and a deepest rootline containing 15 pages.
Both query versions were tested with both index layouts on identical data, using five warm-up rounds and 30 measurement rounds.
Times are medians for all 15 relation queries, including query optimization and SELECT execution. “Before” uses the original queries and index; “after” uses the patched queries and extended index.
The two mixed states add approximately 1.11 ms and 1.52 ms, primarily in query optimization. Actual SELECT execution remains around 1.1 ms. Differences in the unchanged states fall within measurement variability.
EXPLAIN and ANALYZE show indexed range access throughout, with no full table scans. The extended lookup_rec enables index condition pushdown for the new local OR condition, avoiding unnecessary row lookups. Its measured size increases by approximately 6 MiB (+16%).
These measurements cover warm database access for relation resolution, not complete frontend response times.