How Would You Troubleshoot a Slow Historical Query?

Question

A query against the HDB is taking much longer than expected. Walk through a systematic approach to diagnosing and fixing the performance issue.

Solution

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

/ Step 2: Verify the where clause order — date MUST be first
/ SLOW: scans all partitions
\t select from trade where sym=`AAPL, date=2025.01.01

/ FAST: prunes to one partition
\t select from trade where date=2025.01.01, sym=`AAPL

/ Step 3: Check p# on sym
meta trade                                                / sym should show p in the a column

/ Step 4: Fix missing p# if needed
`sym xasc `:/data/hdb/2025.01.01/trade/
@[`:/data/hdb/2025.01.01/trade/; `sym; `p#]
\l /data/hdb

/ Step 5: Reduce column reads — only select what you need
select price, size from trade where date=2025.01.01, sym=`AAPL

/ Step 6: Check how many partitions are scanned for date-range queries
count .Q.pv                            / total number of partitions
\t select from trade where date within 2025.01.01 2025.01.31, sym=`AAPL

/ Step 7: Restrict which partitions queries can see, if only recent history is needed
.Q.view .Q.pv where .Q.pv>=2025.01.01  / only these partitions are visible
.Q.view[]                              / reset: all partitions visible again

Explanation

Historical query performance issues follow a hierarchy. Work through these in order — fixing earlier issues often resolves later ones automatically.

Priority 1: Date filter position. Place date=... or date within (...) as the absolute first condition in every HDB where clause. kdb+ uses the first condition to determine which partitions to open. A sym filter first forces kdb+ to open all partitions; a date filter first prunes the partition set before any sym filter runs. This single rule is the highest-impact habit for HDB query performance.

Priority 2: p# on sym. After partition pruning, the sym filter within each partition is the next bottleneck. p# lets kdb+ read just that symbol's block instead of scanning the whole column. Verify with attr. Re-apply if missing (requires on-disk xasc + attribute + \l).

Priority 3: Column selectivity. Each named column in a splayed table is a separate file on disk. select * reads all files; select price, size reads two. For a 10-column table, explicit column selection reduces disk I/O by 80%.

Priority 4: Partition count for date ranges. A query spanning date within 2024.01.01 2025.01.01 reads ~252 partitions. Each partition incurs a disk read overhead regardless of how few rows match. Consider whether the date range is genuinely needed, or whether .Q.view can be used to restrict which partitions are visible for the most common query patterns.

Priority 5: Page cache. The first query on a cold partition is slow because pages must be read from physical disk. Subsequent queries on the same partition may be faster because the OS has cached those pages. In a benchmarking context, clear the page cache between runs to get representative timings; in production, the cache effect is beneficial and most queries will hit warm pages.