COALESCE based null handling results in poor query planning

[magJ]

I have a collection/table submissionEvents, it has a column submisssionId which I have manually added an index for.

When I query my collection via the records list API, the query is very very slow, and continues to get slower as the table grows in size.

When I query the table directly using the sqlite cli, the query is very fast.
I ran pocketbase in debug mode to see what query it was producing, and saw that it is adding COALESCE in the where conditions, it seems like this confuses the sqlite query planner, and results in poor query performance.

Query without COALESCE, runs very fast, and we can see that it's using my index.

explain query plan SELECT `submissionEvents`.* FROM `submissionEvents` WHERE submissionEvents.submissionId = 47581 ORDER BY submissionEvents.created DESC LIMIT 100;
QUERY PLAN
|--SEARCH submissionEvents USING INDEX ix_submissionId (submissionId=?)
`--USE TEMP B-TREE FOR ORDER BY

Query with COALESCE, runs very very slow, does not use my index.

explain query plan SELECT `submissionEvents`.* FROM `submissionEvents` WHERE COALESCE(submissionEvents.submissionId, '') = COALESCE(47581, '') ORDER BY submissionEvents.created DESC LIMIT 100;
QUERY PLAN
`--SCAN submissionEvents USING INDEX _lyyo499fdv233kr_created_idx

It seems like COALESCE was added a while back to avoid needing to filter nulls in the application code.

If the null filtering could be done in the application, I think it would produce simpler queries and allow sqlite to do a better job of planning.

[ganigeorgiev]

This will be optimized in the next release together with the other index related tasks.

[ganigeorgiev]

The redundant COALESCE statements were removed in the develop branch. The changes will be available with v0.14.0 release (most likely next week or the week following it).