Hi all,
I've run into a surprisingly slow query and it seems that sqlite query planner doesn't figure out what seems to be a simple optimization.
Below is a use case where
```SELECT x FROM t WHERE c1 OR c2```
is incredibly slow, where equivalent, but much less clear
```SELECT x FROM t WHERE c1 UNION ALL SELECT x FROM t WHERE c2```
is lightning fast.
Here's the sqlite console output:
```
# sqlite3
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table file(id integer primary key autoincrement, name TEXT);
sqlite> insert into file(id) select value from generate_series(1, 10 * 1000 * 1000);
sqlite> create index file_index on file ("name");
sqlite> create table event(id integer primary key autoincrement, file_id integer, new_name TEXT);
sqlite> .timer on
sqlite> .eqp on
sqlite> select id from file where name = 'aaa.txt';
QUERY PLAN
`--SEARCH TABLE file USING COVERING INDEX file_index (name=?)
Run Time: real 0.001 user 0.000000 sys 0.000000
```
Here comes the slow query (note that the `event` table is empty):
```
sqlite> select file.id from file left join event on file.id = event.file_id where coalesce(event.new_name, file.name) = 'aaa.txt';
QUERY PLAN
|--SCAN TABLE file
`--SEARCH TABLE event USING AUTOMATIC COVERING INDEX (file_id=?)
Run Time: real 1.589 user 1.593750 sys 0.000000
```
Equivalent query, that avoids using `coalesce`, is also slow.
```
sqlite> select file.id from file left join event on file.id = event.file_id where event.new_name = 'aaa.txt' OR (event.new_name IS NULL AND file.name = 'aaa.txt');
QUERY PLAN
|--SCAN TABLE file
`--SEARCH TABLE event USING AUTOMATIC COVERING INDEX (file_id=?)
Run Time: real 1.731 user 1.734375 sys 0.000000
```
Now this above is what I find surprising. If broken into two queries, both are lightning fast.
```
sqlite> select file.id from file left join event on file.id = event.file_id where event.new_name = 'aaa.txt';
QUERY PLAN
|--SCAN TABLE event
`--SEARCH TABLE file USING INTEGER PRIMARY KEY (rowid=?)
Run Time: real 0.001 user 0.000000 sys 0.000000
sqlite> select file.id from file left join event on file.id = event.file_id where event.new_name IS NULL AND file.name = 'aaa.txt';
QUERY PLAN
|--SEARCH TABLE file USING COVERING INDEX file_index (name=?)
`--SCAN TABLE event
Run Time: real 0.001 user 0.000000 sys 0.000000
```
And finally, the two fast queries written as a compound (`UNION ALL`):
```
sqlite> select file.id from file left join event on file.id = event.file_id where event.new_name = 'aaa.txt' UNION ALL select file.id from file left join event on file.id = event.file_id WHERE event.new_name IS NULL and file.name = 'aaa.txt';
QUERY PLAN
`--COMPOUND QUERY
|--LEFT-MOST SUBQUERY
| |--SCAN TABLE event
| `--SEARCH TABLE file USING INTEGER PRIMARY KEY (rowid=?)
`--UNION ALL
|--SEARCH TABLE file USING COVERING INDEX file_index (name=?)
`--SCAN TABLE event
Run Time: real 0.001 user 0.015625 sys 0.000000
```
Rewriting my real-life query (which has 3 similar joins instead of 1) to such a `UNION`, even if possible, will yield an incomprehensible mess and I'd rather avoid that. Is there another way to speed this up?
Thank you for any insight,
Juraj