Skip to main content

Learned Optimization: Inspecting What AutoML Has Picked Up

· 4 min read
Gnok Team

After watching ten thousand queries, Gnok has opinions about your workload. Learned scorers now feed the planner's partition and index advisors, so those opinions reflect the cost savings actually observed across your queries — not just the original heuristics.

The advisors run continuously as part of the service. This post walks through the surface that lets you see what they've learned: three SHOW … RECOMMENDATIONS commands that expose the live state of the join-order, materialized-view, and overall AutoML caches.

SHOW RECOMMENDATIONS — is AutoML running?​

SHOW RECOMMENDATIONS;
enabled | total_queries_recorded | tables_tracked
true    | 12,847                 | 84

That's the AutoML status summary. enabled shows whether AutoML advisors are on; total_queries_recorded counts the plans AutoML has observed; tables_tracked is the number of distinct base tables in the workload profile.

If enabled = false and you expected otherwise, check with an administrator: Studio shows the same status under Operations → AutoML Control. See AutoML Advisors for what is available to your account.

SHOW JOIN ORDER RECOMMENDATIONS​

The join-order advisor watches the planner's choices and remembers which join orders consistently produced the cheapest plans for each table-set:

SHOW JOIN ORDER RECOMMENDATIONS;
tables_hash | recommended_order                      | preferred_root | confidence | obs_count | sql_hint
8a2f1b…     | analytics.sales.orders, …customers, … | orders         | 0.92       | 184       | /*+ JOIN_ORDER(orders, customers, payments) */
b41c…       | analytics.events.clicks, …users        | users          | 0.71       | 36        | /*+ JOIN_ORDER(users, clicks) */

Each row represents one join-shape the advisor has observed enough times to score. confidence is bounded in [0, 1]; the sql_hint is a paste-ready optimizer hint you can drop into a query that the planner is mis-ordering. obs_count tells you how much weight to put on the recommendation — a 0.92-confidence row backed by 184 observations is a different story than a 0.92 backed by 4.

The advisor surfaces these as passive recommendations — the planner already uses the underlying cache when it can; the hint is for the cases where the user wants to pin the choice.

SHOW MV RECOMMENDATIONS​

Repeated query patterns the advisor scores as worth materializing:

SHOW MV RECOMMENDATIONS;
fingerprint | tables                          | filter_columns | repeated_count | savings_score | last_seen           | sql_hint
4a91b3      | analytics.sales.orders          | created_at     | 47             | 22.1          | 2026-05-04T14:22:01 | CREATE MATERIALIZED VIEW mv_4a91b3 AS …
e7203c      | analytics.events.clicks         | event_date,    | 28             | 18.7          | 2026-05-05T10:11:33 | CREATE MATERIALIZED VIEW mv_e7203c AS …
            |                                 | user_id        |                |               |                     |

savings_score is the planner's estimate of "total cost saved across observed queries if this MV existed" — a function of estimated MV scan cost, base-table scan cost, and how often the fingerprint shows up. repeated_count is the raw count; last_seen lets you spot stale recommendations (a workload shift means yesterday's top candidate may not be today's).

The sql_hint column is a paste-ready CREATE MATERIALIZED VIEW statement.

What's actually learned​

The partition and index advisors score candidate strategies for each base table the workload touches. Learned scorers (gradient-boosted models trained on query history) augment the original heuristics:

  • The partition advisor scores (column, strategy) pairs across the candidate set. Strategies include identity, hash, bucket, truncate, day, hour-of. Features: column statistics, workload filter/join/group-by frequencies, predicate distribution, estimated build cost.
  • The index advisor scores (column, kind) pairs — kinds are btree, bloom, bitmap, hnsw, bm25. Same feature base, different fit checks.

Gnok applies recommendations passively (e.g. the planner consults the join-order cache during its own search, even when no SHOW is called). The SHOW … RECOMMENDATIONS commands surface what has accumulated for human inspection.

What's not in scope (yet)​

  • No per-table SQL command. There's no SUGGEST PARTITION FOR <table> form today. The advisor runs over the full workload-profile cache; surfacing per-table is on the roadmap alongside an EXPLAIN ADVISOR introspection command.
  • Auto-apply. AutoML doesn't apply schema- changing recommendations on its own. Materialized-view creation and index builds remain explicit actions you take (with the sql_hint as a paste-ready starting point).
  • Cross-tenant generalization. The learned scorers start from benchmark-trained baselines; tuning for your workload happens as your queries accumulate.

Try it​

SHOW RECOMMENDATIONS;
SHOW JOIN ORDER RECOMMENDATIONS;
SHOW MV RECOMMENDATIONS;

Run those after your account has been serving real workloads for a few hours. If the second and third return zero rows, either the workload is too uniform to recommend anything useful, or AutoML is disabled (check the first command's enabled column).

The recommendation cache is rebuilt continuously — re-running the SHOWs after a few hundred more queries should show the scores stabilize as the advisor accumulates evidence.