EXPLAIN scoring

Every EXPLAIN analysis in tdfs4ds returns four signals side by side. The first two are LLM-assessed scores; the last two are deterministic counters parsed from the raw EXPLAIN text.

Key	Type	Source	Meaning
user_score	1–5 int	LLM, after [You]-item validation	SQL quality — what the author controls
global_score	1–5 int	LLM	Overall plan quality including infrastructure factors
n_steps	int	Regex on raw EXPLAIN	Numbered execution steps (plan depth)
n_spool_objects	int	Regex on raw EXPLAIN	Distinct Spool N objects referenced (intermediate materialisation count)

All four are stored in FS_PROCESS_DOCUMENTATION_EXPLAIN and returned via retrieve_explain_documentation() / get_process_info() as USER_SCORE, OPTIMIZATION_SCORE, EXPLAIN_N_STEPS, EXPLAIN_N_SPOOL_OBJECTS.

Why two scores

Score	What it answers
user_score	"How good is the SQL itself? Is the data scientist writing efficient queries?"
global_score	"How good is the execution plan? Is the database tuned for this workload?"

A user_score = 5 with global_score = 2 means the SQL is well written but the underlying tables need a DBA action (Primary Index re-design, stats collection, partitioning). A user_score = 2 is the data scientist's problem — try the query optimizer.

Why two counters

The counters complement the scores by giving you deterministic evidence:

• n_steps — fewer steps usually means less work. Halving the number of steps is a strong signal that a rewrite paid off.
• n_spool_objects — fewer spools means less intermediate materialisation (a proxy for memory + IO).

Both come from a regex on the raw EXPLAIN text — they are not opinions, so they are useful as a sanity check on the LLM scores.

Helper functions

from tdfs4ds.genai import (
    document_sql_query_explain,
    _parse_explain_metrics,
    _parse_explain_signals,
)

scored = document_sql_query_explain(sql_query="SELECT ...", classify=True)
# {
#     'explanation':    '...',
#     'user_score':     4,
#     'global_score':   3,
#     'n_steps':        9,
#     'n_spool_objects':4,
#     'warnings':       ['...'],
#     'recommendations':['[You] ...', '[DBA] ...'],
# }

# Pure deterministic counters (no LLM)
metrics = _parse_explain_metrics(scored['raw_plan'])
# {'n_steps': 9, 'n_spool_objects': 4}

# Ground-truth booleans + scan counts
signals = _parse_explain_signals(scored['raw_plan'])
# {
#   'has_product_join':     False,
#   'table_scan_counts':    {'TRANSACTIONS': 1},
#   'has_duplicate_scans':  False,
#   'has_redistribution':   True,
# }

document_sql_query_explain(classify=False) keeps raw [You] items as flagged by the LLM. Use classify=True to filter out false positives like uncorrelated scalar subqueries that Teradata evaluates once.

Schema migration on upgrade

The columns USER_SCORE, N_STEPS, and N_SPOOL_OBJECTS were added in v0.2.9. On upgrade, documentation_tables_creation() and migrate_documentation_explain_schema() add them idempotently — a SHOW TABLE probe runs first. Older rows read back as NULL until re-documented; retrieve_explain_documentation() probes the live DDL and only selects the new columns when present, so the call works on un-migrated installs.

Visual flow diagram

For interactive HTML flow diagrams of any raw EXPLAIN output — including ones not coming from a registered tdfs4ds process — use the td-explain skill.