is there any way I can make the following query faster? I could always periodically pre-calculate and store the values, but maybe there is a better way to just get the query to work faster than 22s

sqlite> SELECT year, Count(DISTINCT materialsCitations.materialsCitationId) AS num
   ...> FROM treatments
   ...>     JOIN materialsCitations ON materialsCitations.treatmentId = treatments.treatmentId
   ...> WHERE treatments.deleted = 0 AND materialsCitations.deleted = 0
   ...> GROUP BY 1;
QUERY PLAN
|--SCAN treatments USING INDEX ix_treatments_year
|--SEARCH materialsCitations USING INDEX ix_materialsCitations_treatmentId (deleted=? AND treatmentId=?)
`--USE TEMP B-TREE FOR count(DISTINCT)
year  num
----  -------------
2009  15723
2010  67
2011  565
2012  1048
2013  1220
2014  3358
2015  9783
2016  49968
2017  68684
2018  43529
2019  109450
2020  151704
2021  681503
2022  89462
Run Time: real 22.174 user 1.749229 sys 2.514141
sqlite> .fullschema
CREATE TABLE treatments (
    id INTEGER PRIMARY KEY,
    treatmentId TEXT NOT NULL UNIQUE,
    …
    checkinTime INTEGER DEFAULT NULL,
    deleted INTEGER DEFAULT 0,
    created INTEGER DEFAULT (strftime('%s','now') * 1000), 
    year INTEGER AS (strftime('%Y', datetime(checkinTime/1000, 'unixepoch')));
CREATE TABLE materialsCitations (
    id INTEGER PRIMARY KEY,
    materialsCitationId TEXT NOT NULL,
    treatmentId TEXT NOT NULL,
    …
    UNIQUE (materialsCitationId, treatmentId)
);
CREATE INDEX ix_treatments_treatmentId ON treatments (deleted, treatmentId);
CREATE INDEX ix_treatments_checkinTime  ON treatments (deleted, checkinTime);
CREATE INDEX ix_treatments_deleted ON treatments (deleted);
CREATE INDEX ix_treatments_year ON treatments (year, deleted);
CREATE INDEX ix_materialsCitations_materialsCitationId ON materialsCitations (deleted, materialsCitationId);
CREATE INDEX ix_materialsCitations_treatmentId ON materialsCitations (deleted, treatmentId);
CREATE INDEX ix_materialsCitations_deleted ON materialsCitations (deleted);

One thing I noticed is that the way the index ix_treaments_year is created will potentially cause the query to jump around for every year entry, that's if there is a sizable number of deleted treatments.

One option is to switch the values deleted and year in the index such that access becomes more or less sequential.

Other than schema and query optimization I would also recommend trying to minimize IO by ensuring you have the right amount of page cache, or better yet, a big enough mmap.

You could always run the query once and save the results in a table. Then write triggers for treatments and materialsCitations that keep it up-to-date as those tables are updated. That way the time to run the query afresh is amortized over all the insert/update/delete operations that you preform on the database, and the summary results are always available and always current.

It looks the generated column year in table treatments is VIRTUAL, not STORED? I. e. it is calculated each time via functions datetime and strftime finally parsing the output to INTEGER.

https://www.sqlite.org/gencol.html: "If the trailing "VIRTUAL" or "STORED" keyword is omitted, then VIRTUAL is the default."

Edit: added "generated"

Does it matter though? Given that the value will be already stored in the index?

No. It will only make a difference when creating the index or an index entry. The computed value can be (and is) retrieved from the index.

To clarify, that first "No" is in response to "Would it matter though?", correct? In other words, whether the calculated column is VIRTUAL or STORED doesn't matter, because there is an index on it, and when the column is calculated, it is inserted in the index. Subsequently, its value is read from the index.

I am asking because I am not entirely sure about the above. If I am wrong, then I should indeed convert the column to STORED so it can be indexed properly in advance.