My colleague @Micah-Kolide and I have been trying to debug some failures in the disk_encryption and block_devices tables. I am still confused, and thus, this bug report isn't as clear as it could be.

This effects osquery 5.8.x, and prior

Some issues....

There's is an o(n^2) issue in joining these tables

On a linux machine, this SELECT * FROM mounts LEFT JOIN disk_encryption ON mounts.device_alias = disk_encryption.name can be surprising slow. If we invert the join it's faster.

I think this is because disk_encryption is indexed by device, but under the hood it calls out to block_devices, which doesn't use query context. And because this is happening in the c++ code, sqlite can't optimize.

So I think this makes an o(n^2) issue.

There's a pretty easy fix for this. I'll put up a PR

This was probably partly introduced by #7209 maybe

Determining encryption status if the underlying devices are lvm stuff

I think this is #7889 and #7314

#7382 might be a fix, though @Micah-Kolide says that's non-performant and has some things about ready to PR

Non-performance or crashes on cloud platforms

One of the community members was telling me that there's bit of non-performance around different cloud vendors. Fetching info can have a surprise network call, which in a join can result in surprising bad performance.

• Amazon Bottlerocket Linux that block_devices table caused a kernel panic and reboot
• GCP network calls