Performance & Troubleshooting

How Attributes Speed Up Queries

Without attributes, every filter requires a full linear scan — kdb+ checks every row to find matches. With the right attribute, kdb+ can jump directly to the relevant data:

trade: ([] sym:10000000?`$string til 5000; price:10000000?100.0)

/ No attribute: linear scan — O(n)
select from trade where sym=`AAPL      / checks every row

/ With `g#: inverted index — O(1) + read
@[`trade; `sym; `g#]
select from trade where sym=`AAPL      / looks up AAPL's position list directly

/ With `p#: reads only the AAPL block (the table must be sorted by sym first)
trade: update `p#sym from `sym xasc trade
select from trade where sym=`AAPL

For a table with 10 million rows and 5,000 distinct symbols, the difference is the scan of 10M rows vs the direct read of ~2,000 rows.

Appending Data and Losing Attributes

Appending data can silently remove attributes if the structural guarantee is broken. This is the most common source of unexpected performance degradation in kdb+ systems:

/ `s# is lost if you append a value smaller than the last
L: `s# 1 2 3 5
L,: 0            / breaks sorted order — attribute silently dropped
attr L           / returns ` (no attribute)

/ `p# is lost on any in-memory append
syms: `p# `AAPL`AAPL`GOOG`GOOG
syms,: `AAPL     / attribute dropped
attr syms        / returns `

/ `g# is maintained on every append
syms: `g# `AAPL`GOOG`AAPL`MSFT
syms,: `GOOG     / index updated — attribute retained
attr syms        / returns `g

On the HDB, attributes are applied after the EOD sort. Appending to a column file on disk keeps only s#, so if you append new rows to an on-disk partition, the p# on sym is lost and sym queries on that partition fall back to a scan.

Re-applying Attributes After Appending to the HDB

If you need to add rows to an existing HDB partition, the correct process is:

/ 1. Append the new rows (p# is lost); enumerate symbols against the HDB root's sym file
`:/data/hdb/2025.01.01/trade/ upsert .Q.en[`:/data/hdb; ] newRows

/ 2. Re-sort the partition on disk
`sym xasc `:/data/hdb/2025.01.01/trade/

/ 3. Re-apply the parted attribute
@[`:/data/hdb/2025.01.01/trade/; `sym; `p#]

/ 4. Reload the HDB (in the HDB process)
\l /data/hdb

Skipping steps 2 and 3 will leave the partition without p#, causing all subsequent sym-filtered queries to scan the entire partition.

Troubleshooting Slow In-Memory Queries

/ Step 1: Time the slow query
\t select from trade where sym=`AAPL

/ Step 2: Check the attribute on the filter column
attr trade`sym     / should be `g; if it is ` then the attribute is missing

/ Step 3: Check the table size
count trade        / large tables without attributes will be slow

/ Step 4: Re-apply the attribute if missing
@[`trade; `sym; `g#]
\t select from trade where sym=`AAPL    / should be significantly faster

/ Step 5: Check for other slow patterns
/ Avoid string comparisons (use sym filter, not like)
/ Avoid computed columns in where clause (pre-compute with update)

Common causes of slow in-memory queries:

Missing g# on sym — most frequent
Filtering on a derived column (where size%price > 1) — no attribute can help here; pre-compute the column with update
Using like pattern matching — inherently a linear scan; cannot be indexed
Large result sets — selecting all columns from a large table; be explicit about which columns you need

Troubleshooting Slow Historical Queries

/ Step 1: Time the query
\t select from trade where date=2025.01.01, sym=`AAPL

/ Step 2: Confirm date comes FIRST in the where clause
/ WRONG — loads all partitions then filters sym (slow)
select from trade where sym=`AAPL, date=2025.01.01

/ CORRECT — prunes to one partition first, then filters sym
select from trade where date=2025.01.01, sym=`AAPL

/ Step 3: Check the attribute on sym (meta shows it for the partitioned table)
meta trade                                          / sym should show p in the a column

/ Step 4: Check column selection — only read what you need
/ WRONG — reads every column from disk
select from trade where date=2025.01.01, sym=`AAPL

/ CORRECT — reads only price and size columns from disk
select price, size from trade where date=2025.01.01, sym=`AAPL

/ Step 5: Check how many partitions are being scanned
count .Q.pv            / how many date partitions exist?
/ Narrow the date range to reduce the number of partitions read

Common causes of slow HDB queries:

sym filter before date filter — defeats partition pruning
Missing p# on sym — full partition scan instead of block read
select * — reads all columns from disk; specify only needed columns
Wide date ranges — spanning many partitions multiplies read cost
No .Q.view — if users only need recent history, .Q.view can restrict which partitions queries see, e.g. .Q.view .Q.pv where .Q.pv>=2025.01.01