I'm trying to optimize my application by adding some covering indexing for important queries that we run frequently. Some of these indices would also include a string-comparison expression, which should remove the need of string compare as the result of the comparsison should be already in the index. However, it seems that no matter what, SQLite will not use the available indices unless forced with INDEXED BY.

Is there a rational reason why SQLite would do a row lookup (+ expression evaluation) instead of using the available covering index?

This is a playground (can be executed on the sqlite fiddle) similar to my setup:

CREATE TABLE IF NOT EXISTS test(id PRIMARY KEY, value1, value2);
CREATE INDEX IF NOT EXISTS test_index_1 ON test(id, value1, value2 = 'test_value');
CREATE INDEX IF NOT EXISTS test_index_2 ON test(id, value1, value2);

REPLACE INTO test VALUES ('a', 10, 'test_value'), ('b', 'abc', 'test_value'), ('c', 'abc', 'hello');

EXPLAIN QUERY PLAN SELECT id FROM test WHERE id = 'abc' AND value1 = 42 AND value2 = 'test_value';
EXPLAIN QUERY PLAN SELECT id FROM test INDEXED BY test_index_1 WHERE id = 'abc' AND value1 = 42 AND value2 = 'test_value';
EXPLAIN QUERY PLAN SELECT id FROM test INDEXED BY test_index_2 WHERE id = 'abc' AND value1 = 42 AND value2 = 'test_value';

and the output is

QUERY PLAN
`--SEARCH test USING INDEX sqlite_autoindex_test_1 (id=?)
QUERY PLAN
`--SEARCH test USING INDEX test_index_1 (id=? AND value1=?)
QUERY PLAN
`--SEARCH test USING COVERING INDEX test_index_2 (id=? AND value1=? AND value2=?)

Why isn't the expression in test_index_1 used even if the query has the same exact expression? Why doesn't the query use either test_index_1 or test_index_2 by default?

Thanks to anyone who can clarify!

Ah, that makes sense, thanks a lot!

However, there is still the second point unresolved: why isn't test_index_1 (the expression-based) a valid covering index? Even after adding the UNIQUE constraint, SQLite will refuse to choose test_index_1 (which should be a better index than test_index_2) and will not use test_index_1 as a covering index even when forced.

QUERY PLAN
`--SEARCH test USING COVERING INDEX test_index_2 (id=? AND value1=? AND value2=?)
QUERY PLAN
`--SEARCH test USING INDEX test_index_1 (id=? AND value1=?)
QUERY PLAN
`--SEARCH test USING COVERING INDEX test_index_2 (id=? AND value1=? AND value2=?)

Any idea why?

The query planner is just not smart enough to deal with that right now. You can work around the limitation by writing:

  SELECT id
    FROM test
   WHERE id='abc'
     AND value1=42
     AND (value2='test value')=TRUE;

In word: make the constraint an equality constraint rather than a bare boolean value.

Sorry for re-opening this old discussion, but I found a similar case that is not solved by setting the covering index to unique: the query planner will choose the primary key in any case. This is reproducible with the following example:

CREATE TABLE IF NOT EXISTS person(id TEXT PRIMARY KEY);
CREATE TABLE IF NOT EXISTS company(id TEXT PRIMARY KEY, profitable); 
CREATE TABLE IF NOT EXISTS personCompany(personID, companyID, PRIMARY KEY (personID, companyID));

# This covering index is there to avoid the row lookup when we want to just get the profitable value 
CREATE UNIQUE INDEX IF NOT EXISTS companyProfitableCovering ON company(id, profitable);

# With subquery and no INDEXED BY
EXPLAIN QUERY PLAN SELECT id, (
  SELECT COUNT(*) FROM personCompany
  WHERE personID = person.id AND (
    SELECT profitable FROM company
    WHERE id = personCompany.companyID
  )
) FROM person;

# With subquery and INDEXED BY
EXPLAIN QUERY PLAN SELECT id, (
  SELECT COUNT(*) FROM personCompany
  WHERE personID = person.id AND (
    SELECT profitable FROM company INDEXED BY companyProfitableCovering
    WHERE id = personCompany.companyID
  )
) FROM person;

# With join and no INDEXED BY
EXPLAIN QUERY PLAN SELECT person.id, COUNT(company.id) FROM person
LEFT JOIN personCompany on person.id = personID
LEFT JOIN company ON company.id = companyID
WHERE profitable
GROUP BY person.id;

# With join and INDEXED BY
EXPLAIN QUERY PLAN SELECT person.id, COUNT(company.id) FROM person
LEFT JOIN personCompany on person.id = personID
LEFT JOIN company INDEXED BY companyProfitableCovering ON company.id = companyID
WHERE profitable
GROUP BY person.id;

The 4 queries print the following plans:

# With subquery and no INDEXED BY
QUERY PLAN
|--SCAN person
`--CORRELATED SCALAR SUBQUERY 2
   |--SEARCH personCompany USING COVERING INDEX sqlite_autoindex_personCompany_1 (personID=?)
   `--CORRELATED SCALAR SUBQUERY 1
      `--SEARCH company USING INDEX sqlite_autoindex_company_1 (id=?)

# With subquery and INDEXED BY
QUERY PLAN
|--SCAN person
`--CORRELATED SCALAR SUBQUERY 2
   |--SEARCH personCompany USING COVERING INDEX sqlite_autoindex_personCompany_1 (personID=?)
   `--CORRELATED SCALAR SUBQUERY 1
      `--SEARCH company USING COVERING INDEX companyProfitableCovering (id=?)

# With join and no INDEXED BY
QUERY PLAN
|--SCAN person USING COVERING INDEX sqlite_autoindex_person_1
|--SEARCH personCompany USING COVERING INDEX sqlite_autoindex_personCompany_1 (personID=?) LEFT-JOIN
`--SEARCH company USING INDEX sqlite_autoindex_company_1 (id=?)

# With join and INDEXED BY
QUERY PLAN
|--SCAN person USING COVERING INDEX sqlite_autoindex_person_1
|--SEARCH personCompany USING COVERING INDEX sqlite_autoindex_personCompany_1 (personID=?) LEFT-JOIN
`--SEARCH company USING COVERING INDEX companyProfitableCovering (id=?)

Indicating that the covering index is there and usuable, but not chosen by the planner until forced with INDEXED BY. In our application we deal with lots of records and want to optimize as much as possible reading speed, for which we though about using such a covering index. However, it doesn't work as expected.

Any clues on what we could be doing wrong?

Thank you!

We ... want to optimize as much as possible reading speed,

Have you actually measured suboptimal performance, or are you just guessing based on what you see in the EXPLAIN QUERY PLAN output? Do you have an test case (with test data) that we can run and time to see suboptimal performance? Can you please share your test data?

In our case, the query without covering index takes on average ~2300ms, where the same query with the covering index (forced by INDEXED BY) takes on average ~800ms.

The dataset in our test case (taken from a real-world user case) has the following number of entries (in respect to the example above):

• person: ~5000 rows
• company: ~150k rows
• personCompany: ~670k rows (on average each company has ~4 people)

The base query with the subquery generates the following .scanstat on:

QUERY PLAN
|--SCAN person                                                                                  (loops=1 rows=5353)
`--CORRELATED SCALAR SUBQUERY 2                                                                 (loops=5353)
   |--CORRELATED SCALAR SUBQUERY 1                                                              (loops=672858)
   |  `--SEARCH company USING INDEX sqlite_autoindex_company_1 (id=?)                           (loops=672858 rows=672858)
   `--SEARCH personCompany USING COVERING INDEX sqlite_autoindex_personCompany_1 (personID=?)   (loops=5353 rows=672858)

By increasing the cache size to 4GB (enough to hold the whole DB in memory), the timing goes to ~1400ms for the query without covering index and ~620ms for the same query with the covering index (forced by INDEXED BY).

If you find it useful, I can send you an stripped-down/anonymized version of the database.

Please do send the stripped-down/anonymized version of the database.

In the test script below, I'm getting essentially identical timings regardless of whether or not the companyProfitableCoverging index is used:

CREATE TABLE person(id TEXT PRIMARY KEY);
WITH RECURSIVE cnt(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM cnt WHERE n<10000)
  INSERT INTO person(id) SELECT n FROM cnt;
CREATE TABLE company(id TEXT PRIMARY KEY, profitable);
WITH RECURSIVE cnt(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM cnt WHERE n<100)
  INSERT INTO company(id, profitable) SELECT n, n%2 FROM cnt;
CREATE TABLE personCompany(personID,companyID,PRIMARY KEY(personID,companyID));
WITH RECURSIVE c1(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c1 WHERE x<1000),
               c2(y) AS (VALUES(1) UNION ALL SELECT y+1 FROM c2 WHERE y<100)
  INSERT INTO personCompany(personID,companyID) SELECT x, y FROM c1, c2;
CREATE UNIQUE INDEX companyProfitableCovering ON company(id, profitable);

.echo on
.mode count
.timer on

/* With subquery and no INDEXED BY */
SELECT id, (
  SELECT COUNT(*) FROM personCompany
   WHERE personID = person.id
     AND (
           SELECT profitable 
             FROM company
            WHERE id = personCompany.companyID
         )
) FROM person;
/* With subquery and INDEXED BY */
SELECT id, (
  SELECT COUNT(*) FROM personCompany
   WHERE personID = person.id
     AND (
            SELECT profitable
              FROM company INDEXED BY companyProfitableCovering
             WHERE id = personCompany.companyID
         )
) FROM person;

/* With join and no INDEXED BY */
SELECT person.id, COUNT(company.id)
  FROM person
  LEFT JOIN personCompany on person.id = personID
  LEFT JOIN company ON company.id = companyID AND profitable
 GROUP BY person.id;
/* With join and INDEXED BY */
SELECT person.id, COUNT(company.id)
  FROM person
  LEFT JOIN personCompany on person.id = personCompany.personID
  LEFT JOIN company INDEXED BY companyProfitableCovering
                    ON company.id = personCompany.companyID AND profitable
 GROUP BY person.id;

I am cringing reading what this query appears to be doing. Are you tracking people based on profitability to a company? seems like it's awfully close to the lines regarding the license terms of sqlite.

(now walking away quickly, everybody carry on! - sure there will be blowback, but it needed to be said)

I believe the example database has been greatly simplified from a real-world case and the simplifications distort the semantics significantly. I would not be alarmed.

Why not just drop the "companyProfitableCovering" index and make the "company" table a WITHOUT ROWID table. I think that will resolve any performance issues you are having, with the added benefit of making your database smaller. (You have your data, and I don't, so you will have to be the one to verify the previous sentence.) For the schema you show, you would do well, I think, to make all three tables WITHOUT ROWID.

The provided schema is a minimal reproducible example. In practice our schema has more columns for both person and company, company in particular has 40 columns, so each row is quite big. IDs are standard uuid strings. I tried moving both tables to WITHOUT ROWID (and even just moving moving company), the performance greatly deteriorates: the same query now takes around ~10500ms. The query plan seems as expected:

QUERY PLAN
|--SCAN person                                                                                  (loops=1 rows=5353)
`--CORRELATED SCALAR SUBQUERY 2                                                                 (loops=5353)
   |--CORRELATED SCALAR SUBQUERY 1                                                              (loops=672858)
   |  `--SEARCH company USING PRIMARY KEY (id=?)                                                (loops=672858 rows=672858)
   `--SEARCH personCompany USING COVERING INDEX sqlite_autoindex_personCompany_1 (personID=?)   (loops=5353 rows=672858)

Please use the ANALYZE command on that database, then check to see whether it changes either times or query plans.