In a Where Clause With Multiple Filters, How Many Attributed Columns Benefit?

Question

A table has two columns, both with attributes applied. A query filters on both columns in the where clause. How many of those attributes are actually used to speed up the query?

Solution

/ Table with two attributed columns
t: ([] sym:`g#`AAPL`GOOG`AAPL`MSFT`GOOG; size:`u#100 200 300 400 500)

attr t`sym    / `g
attr t`size   / `u

/ Query filtering on both columns
select from t where sym=`AAPL, size > 200

/ Only `sym`'s `g# attribute is used.
/ After the sym filter, kdb+ has a sub-table of matching rows.
/ That sub-table has NO attributes — size's `u# is gone.
/ The size > 200 filter is a linear scan of the sub-table.

Explanation

Only the first constraint in a where clause can use an attribute. kdb+ applies the constraints from left to right, and each one after the first is evaluated only on the rows that passed the previous ones — a subset of the column, which no longer has the column's attribute. So every later constraint is a scan of the remaining rows.

You can see the effect on a large table: with g# on sym, select from t where sym=`abc, price>0 is fast, while select from t where price>-1, sym=`abc is slow, because the sym test then runs on the rows that passed price>-1 and cannot use the index.

The practical consequence is important: having two attributed columns in a table does not mean compound queries get two speed-ups. Only the first constraint can benefit.

How to use this knowledge:

Order your where conditions so the most selective filter comes first, and that column has an attribute. If you have a query that filters by date, sym, and size, and only sym and date have attributes, put the most selective of those two first:

/ In-memory table: put the attributed, most selective constraint first
select from trade where sym=`AAPL, price>100

/ HDB (partitioned) table: the partition column always comes first
select from trade where date=2025.01.01, sym=`AAPL

For in-memory tables, put the filter that eliminates the most rows first. That filter uses its attribute; everything after is a linear scan of the remaining rows.