Optimizer limitation with partial indexes

I'm running into a problem with partial indexes; apparently the query optimizer isn't smart enough.

I currently have indexes of the form
	CREATE INDEX Index1 ON Table (expr1)
	CREATE INDEX Index2 ON Table (expr2)
where expr1 and expr2 are expressions involving table columns.

The problematic queries are of the form
	SELECT * FROM Table WHERE (expr1 > val1 OR expr2 > val2) AND expr3
Such a query correctly uses the above indexes — the EXPLAIN command shows it's using a multi-index OR combining two 'search table using index' loops.

If, however, I try to make the indexes smaller by changing them to
	CREATE INDEX Index1 ON Table (expr1) WHERE expr3
	CREATE INDEX Index2 ON Table (expr2) WHERE expr3
the query stops using the indexes effectively. It's reduced to doing 'scan table using index', i.e. O(n).

It looks like what happens is that the optimizer doesn't associate the "AND expr3" clause with the "expr1" and "expr2" comparisons. In other words, it doesn't realize that (A OR B) AND C is equivalent to (A AND C) OR (B AND C).

If this were a hand-written SELECT statement it would be easy to work around this, but it's not. It's the output of a query translator that generates SQL, and it can generate arbitrary queries with arbitrary combinations of operators.

I know the SQLite optimizer isn't a Mathematica-grade symbolic logic analyzer! But I'm wondering if in this case there's a way around this limitation?

—Jens
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users