SQLite query using rtree and normal index slow

Question

I have geospatial data, names with coordinates, in a SQLite table and created an rtree for the location and a normal index on the name-column.

Rtree is used according to this docs: http://www.sqlite.org/rtree.html

When I query records in a specific area, the rtree is used and it works fast:

SELECT demo_data.* FROM demo_data, demo_index
WHERE demo_data.id=demo_index.id
   AND minX>=-81.0 AND maxX<=-79.6
   AND minY>=35.0 AND maxY>=36.2;

When I query just for names, it also goes fast, because the name-index is used:

SELECT demo_data.* FROM demo_data
WHERE objname="Test"

But when I combine the two, its very slow, seems like the whole table is scanned:

SELECT demo_data.* FROM demo_data, demo_index
WHERE demo_data.id=demo_index.id
   AND objname="Test"
   AND minX>=-81.0 AND maxX<=-79.6
   AND minY>=35.0 AND maxY>=36.2;

Why is this combined query using two indexes so slow?

Update:

After more investigation with EXPLAIN QUERY PLAN, it turned out, that the indexes are actually used by each individual condition. But the time for the execution of the combined query depends on the count of records in the first condition. This table demo_data has 10mio records. but the combination is only slow, if the first condition returns a lot of records. In this case, there is some 1000 records with objname="Test" and the combined query takes 4 seconds. A combined query for objname="Test12345", which only exist once, is very fast, only 10ms

Answer 1

Queries involving more than one index are hard to accelerate, and may require statistics, maybe even from earlier queries.

The database could (I don't know what SQLite implemented) e.g.:

1. Bail out, and always do a slow scan
2. Use the first index only, scan the matching rows
3. Use the second index only, scan the matching rows
4. Get IDs from both indexes, intersect, then reconstruct all rows

It's the task of the query optimizer to choose the best strategy. Often 2 or 3 is best, if we can predict which index yields the smaller result. It requires statistics obtained when calling ANALYZE.

Use EXPLAIN QUERY PLAN SELECT ... to see what SQLite decided to do: https://sqlite.org/eqp.html

You can also try nested queries to nudge the optimizer towards the better plan, and you should read the query optimizer documentation: https://www.sqlite.org/optoverview.html