kdb: Where phrase AND performance

user_3927314 0
Viewed 126

KX reference says #1 should be faster than #2.

q)select from t where c2>15,c3<3.0
q)select from t where (c2>15) and c3<3.0

In the first example, only c3 values corresponding to c2 values greater than 15 are tested.

In the second example, all c2 values are compared to 15, and all c3 values are compared to 3.0. The two result vectors are ANDed together.

---

However, I noticed the contrary is true in my example below. Am I missing something?

N:100000000;
t:([]a:N?1000;b:N?1000;c:N?1000)
\t select from t where a>500,b>500;        / ~500ms
\t select from t where (a>500) and b>500;  / ~390ms
kdb
1 Answers

It's a general statement - when they say that it should be faster, they mean in most practical situations. There will always be edge-cases where certain approaches aren't faster based on the shape of the data.

In your case since the first filter isn't reducing the dataset by a huge amount (it's halving it), the overhead of "halving" the second column prior to the second filter just happens to be greater than just outright applying the second filter to the whole second column (kdb is very fast at vector operations afterall).

If, for example, your first filter reduced the dataset by a lot then you would see more speed gains as the reduced overhead of the second filter being applied to a smaller set is more dominant:

q)\t select from t where a<10,b>500;
263
q)\t select from t where (a<10) and b>500;
450

q)\t select from t where a=950,b>500;
208
q)\t select from t where (a=950) and b>500;
422

The speed improvement would be even more pronounced if the first column had attributes applied, say `g# (in-memory) or `p# (on disk). And since in most high-volume production scenarios there would be attributes speeding up the first filter, they make the statement that it should be faster (almost implying that if it isn't then you probably aren't making use of attributes!).

Here's an extreme example where the a column has sorted attribute:

q)`a xasc `t;
q)\t select from t where a=950,b>500;
1
q)\t select from t where (a=950) and b>500;
428
user_38956970
Related