How does cockroach handle IS NULL check in SQL query in text column?


#1

Let’s say I have a text column storing about 5 to 10 MB of data and also I am using SQL queries which is making IS NULL check on this text column.

So will there be any performance gain if I add a BOOL IS_NULL column in that table and perform the null check on the IS_NULL column rather than on the text column?


(Alex Robinson) #2

Hi @tuk, there will be a performance gain if you add a BOOL IS_NULL column IF you put that column in a separate column family (https://www.cockroachlabs.com/docs/stable/column-families.html). That will store it separately from the text field on disk, keeping it from needing to be read quite as far up into the system.

If you don’t put it into its own column family, I don’t think you’d see much performance difference in cockroach v2.0.


(Alex Robinson) #3

But note that

  1. You’re best off putting the large text field into its own column family, not the small bool field.
  2. Doing full-table scans on a table with such large rows is going to be much slower than on a table with smaller rows. Using secondary indexes for tables with large rows like this is advised if you want low-latency queries.

(Alex Robinson) #4

And I have to retract my initial answer. You won’t get a performance boost just by using a separate IS NULL column. You’ll need to use a secondary index on it as well to get a speedup.