Inspect the workload

KDBS records how the workload reaches its data and shows it through a set of read-only commands. Use SHOW ACCESS to see which access shapes run, ANALYZE to see how one query ran, SHOW PATTERNS for repeated queries, SHOW BUDGET for id space, SHOW META for instance counters, and SHOW RELAYOUT and SHOW CABIN_OPTIMIZER for what the optimizers see. None of these commands change data or results.

All output on this page was produced on the current engine with the sample table passengers(id, name, member, joined_on).

Before you begin

• With auth = scram, every command on this page needs only the readonly role.
• Access recording is on by default (access_statistics = on). If it is off, SHOW ACCESS records no new shapes.
• Run some traffic first. These examples ran each of these queries three times:

SELECT * FROM passengers WHERE id = 2;
SELECT name, member FROM passengers WHERE name = 'Park Jiho';

Step 1: See the access shapes with SHOW ACCESS

SHOW ACCESS lists one row per access shape: the kind of access, the table, and the columns it was keyed or filtered on. It never records values, so WHERE name = 'Park Jiho' and WHERE name = 'Kim Minjun' are one shape.

kdbs> SHOW ACCESS;
access_shapes=4
kind=Lookup rel=airlines columns=[id] uses=3 last_seen=1227731308332124
kind=Lookup rel=flights columns=[id] uses=3 last_seen=1227731315079413
kind=Lookup rel=passengers columns=[id] uses=11 last_seen=1227731317631367
kind=FilterScan rel=passengers columns=[name] uses=3 last_seen=1227731318418515

The airlines and flights lines, and eight of the eleven passengers lookups, are foreign-key checks that ran when the sample flights and tickets rows were loaded.

Kind	Meaning
Lookup	A primary-key equality.
Probe	A primary-key probe from a join.
Range	A primary-key range walk.
FilterScan	A scan that filters on a non-key column.
Scan	A scan with no filter.

A FilterScan with many uses on one column is the signal to consider a secondary index or a Cabin on that column. See CREATE INDEX, DROP INDEX and Cabins.

The data lives in the system relation sys.access_stats. In this build it is read through SHOW ACCESS only; SELECT from it is refused:

kdbs> SELECT * FROM sys.access_stats;
ERR no catalog view named 'sys.access_stats' (known: sys.tables, sys.columns, sys.objects, sys.types, sys.patterns)

Step 2: See how one query ran with ANALYZE

Put ANALYZE in front of a SELECT. The query runs normally, and the reply reports what each step did.

kdbs> ANALYZE SELECT name, member FROM passengers WHERE name = 'Park Jiho';
analyze rows=1 class=JoinSelect steps=1 examined=8 pages=1 opens=1 pattern_id=0x1c46798b201cf19f fetch_id=0xd233d3fa77d83084
class=JoinSelect steps=1 hoisted=0
step 0 FilterScan passengers
  filter 0:0.1 = 'Park Jiho'
project name=0:0.1, member=0:0.2
step 0 FilterScan passengers opens=1 examined=8 matched=1 sel=12% pages=1

Read examined against rows: here 8 rows were read to return 1. A primary-key lookup reads only what it returns:

kdbs> ANALYZE SELECT * FROM passengers WHERE id = 2;
analyze rows=1 class=PointSelect steps=1 examined=1 pages=1 opens=1 pattern_id=0x40a5b5e4e01959eb fetch_id=0x9b8e7707127bd8d4
class=PointSelect steps=1 hoisted=0
step 0 Lookup passengers key=2
  filter 0:0.0 = 2
project * (4 column(s) of step 0)
step 0 Lookup passengers opens=1 examined=1 matched=1 sel=100% pages=1 replays=1

replays=1 means the step was served from a Waystone trail recorded by an earlier run. A sort shows as a sort line and sorted= in the header:

kdbs> ANALYZE SELECT name FROM passengers ORDER BY joined_on;
analyze rows=8 class=JoinSelect steps=1 examined=8 pages=1 opens=1 sorted=8 pattern_id=0x77611b1a5c4ad282 fetch_id=0x9e653719ef863c1e
class=JoinSelect steps=1 hoisted=0
step 0 Scan passengers
project name=0:0.1
sort 0:0.3 asc
step 0 Scan passengers opens=1 examined=8 matched=8 sel=100% pages=1

ANALYZE works only on SELECT. Wrapping a write is refused with ERR expected a SELECT statement. See ANALYZE.

Step 3: See repeated queries with SHOW PATTERNS

A pattern is a query’s shape with its values left out. The engine registers a pattern from traffic and may record a Waystone trail for it, so a repeated lookup can skip the search. You cannot declare a pattern yourself.

kdbs> SHOW PATTERNS;
patterns=1
fetch_id=0x9b8e7707127bd8d4 oid=1 origin=auto pinned=no class=1 uses=0 last_seen=0 waystone=root=145,depth=1

Match a row to a query by fetch_id: ANALYZE prints the same fetch_id (0x9b8e7707127bd8d4 is the id = 2 lookup above). In this run only the primary-key lookup was registered, not the filter scan. uses= and last_seen= read 0 by design: running a pattern writes its trail, not its row.

The same data is readable with SELECT. Integers in catalog views are unsigned and print in decimal:

kdbs> SELECT fetch_id, stmt_class, use_count, waystone_root FROM sys.patterns;
fetch_id,stmt_class,use_count,waystone_root
11209027394830325972,1,0,145

LIMIT, OFFSET and WHERE work on sys.* views. ORDER BY, joins and aggregates are refused. See Waystone trails.

Step 4: Check id space with SHOW BUDGET

Every table has a 48-bit id space, [1, 281474976710655]. SHOW BUDGET shows how much each table has used. warning= and exhausted= in the first line count tables past the warning level and tables with no ids left.

kdbs> SHOW BUDGET;
relations=14 warning=0 exhausted=0 capacity=281474976710655 warn_at=90.000%
rel=types issued=0 remaining=281474976710655 used=0.000% warn=no exhausted=no
...
rel=airlines issued=3 remaining=281474976710652 used=0.000% warn=no exhausted=no
rel=passengers issued=8 remaining=281474976710647 used=0.000% warn=no exhausted=no
rel=flights issued=4 remaining=281474976710651 used=0.000% warn=no exhausted=no
rel=tickets issued=9 remaining=281474976710646 used=0.000% warn=no exhausted=no

The budget is measured from the table’s high-water mark, not its row count. A table fed sparse, large keys uses up its space with fewer rows. DESCRIBE <table> shows the same numbers for one table as next_id, ids_issued, ids_remaining and budget_used.

Step 5: Read instance counters with SHOW META

SHOW META returns one long line of key=value fields: the volume, the buffer pool, the WAL, checkpoints, the last mount’s recovery, backups and contention. An excerpt:

kdbs> SHOW META;
version=23 ... wal_topology=single cabin_optimizer=off core=0 ... pages_allocated=32 pages_freed=0 pages_reused=0 reclaim_pending=0 ... pool_budget=1024 pool_resident=30 ... pool_hits=594 pool_misses=30 ... wal_durable_lsn=35872 wal_syncs=13 ... wal_mean_group_batch=1.000 ... recovery_records=0 recovery_committed=0 recovery_rolled_back=0 ... backup_state=idle ...

Fields worth watching:

Field	Tells you
pool_hits, pool_misses, pool_resident, pool_budget	Buffer pool use against buffer_pool_frames.
wal_mean_group_batch	Commits per log sync. 1.000 means every commit paid for its own sync.
wal_durable_lsn	The end of the durable log. Note it before an operation you may want to undo; see Back up and restore.
wal_stopped	1 after a failed log write has stopped all writes.
pages_allocated, pages_freed, pages_reused, reclaim_pending	Page reclaim after DROP TABLE. The data file never shrinks.
recovery_*	What the last mount’s recovery did.
contention_*	Waits between cores, summed over every core.

On an instance with several cores, read the WAL fields on core 0, which owns the log.

Step 6: See what the optimizers would do

SHOW RELAYOUT

The physical optimizer runs in shadow mode by default: it plans and reports, and moves nothing. SHOW RELAYOUT weighs the access shapes per table. SHOW RELAYOUT <table> reports one table.

kdbs> SHOW RELAYOUT passengers;
relayout_relations=1
rel=passengers clustered=btree shapes=3 walk_weight_q8=1280
shape kind=Lookup columns_mask=0x1 uses=12 weight_q8=3072
shape kind=FilterScan columns_mask=0x2 uses=4 weight_q8=1024
shape kind=Scan columns_mask=0x0 uses=1 weight_q8=256
plans=none reason=btree-outside-v1-mover-scope

• weight_q8 is a decayed weight in fixed point: 256 is a weight of 1.0. Weights halve after decay_half_life seconds (default 600) without use.
• plans=none reason=btree-outside-v1-mover-scope means no relayout plan exists for a B+ tree table in this version. It is not a health check.
• With physical_optimizer = off, the reply is RELAYOUT off (physical_optimizer=off).

Not built yet: a mover. No relayout plan is ever carried out. See Physical optimizer.

SHOW CABIN_OPTIMIZER

The Cabin optimizer can create and drop Cabins on its own for columns declared CABIN AUTO. It is off by default.

kdbs> SHOW CABIN_OPTIMIZER;
cabin_optimizer=off managed=0 pages_committed=0 page_budget=1024 ticks=0 creates=0 extends=0 heals=0 drops=0 deferred=0 failures=0

The counters are actions carried out. When the optimizer manages Cabins, one line per Cabin follows with its state (CANDIDATE, BUILDING, ACTIVE or DECAYING) and the scores behind its last decision. Turn it on with the server key cabin_optimizer = on, or at runtime with SET CABIN_OPTIMIZER ON (needs the admin role).

Verify

• SHOW ACCESS lists the shapes of the queries you ran.
• The fetch_id that ANALYZE prints for a repeated lookup appears in SHOW PATTERNS.

Next steps

• SHOW and DESCRIBE: every SHOW command.
• Limits: the caps a query can reach.
• Statements and execution plans: why the written query is the plan.