I came across the issue mentioned in this post where checking a blob for null in a query is quite slow.

The post suggests a workaround using length(blob) IS NULL. There are many timings in the above link, but these are my timings on a small table with blob sizes around 100k (SQLite version 3.39.4):

sqlite> select count(*) from movie;
606
Run Time: real 0.016 user 0.001014 sys 0.001111
sqlite> select count(*) from movie where poster is null;
19
Run Time: real 0.044 user 0.014245 sys 0.027969
sqlite> select count(*) from movie where length(poster) is null;
19
Run Time: real 0.002 user 0.000662 sys 0.000883

In my case, the workaround is 22x faster. This is rather counterintuitive as a simple IS NULL should be just as fast.

So when you do an explain on the two things you can see there's a slight difference.

explain select count(*) from t where content is null;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
...
3     Rewind         0     8     0                    0
4       Column         0     1     2                    0   r[2]=t.content
5       NotNull        2     7     0                    0   if r[2]!=NULL goto 7
6       AggStep        0     0     1     count(0)       0   accum=r[1] step(r[0])
7     Next           0     4     0                    1
...

explain select count(*) from t where length(content) is null;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
...
3     Rewind         0     9     0                    0
4       Column         0     1     3                    64  r[3]=t.content
5       Function       0     3     2     length(1)      0   r[2]=func(r[3])
6       NotNull        2     8     0                    0   if r[2]!=NULL goto 8
7       AggStep        0     0     1     count(0)       0   accum=r[1] step(r[0])
8     Next           0     4     0                    1
...

You can see that the p5 argument to Column is different. If we look at the Column opcode information, we see this:

"If the OPFLAG_LENGTHARG and OPFLAG_TYPEOFARG bits are set on P5 then the result is guaranteed to only be used as the argument of a length() or typeof() function, respectively. The loading of large blobs can be skipped for length() and all content loading can be skipped for typeof()."

So it knows the field is only getting used in a call to length(), so it doesn't bother loading it at all, because the length is part of the field header.

Going by the note we can also test this with

select count(*) from t where typeof(content) = 'null';

and you'll find that is also fast.

Apparently that optimization only goes for when the field is used as the argument to length() or typeof() and not when it's only used with a "is null" or "is not null" postfix operator.

Nice analysis, thanks.

that optimization only goes for [...] length() or typeof()

Those functions only look at the record header, not the record body, thus they don't indeed need the values.
But the same goes for the IS [NOT] NULL operators, so in theory the same optimization should apply (eventually).
At least that's my understanding of this.

The team being the awesome group they are, it looks like "eventually" = "today".

https://www.sqlite.org/src/info/cb94350185f555c3