On a db with multiple indexes how do I have query use a specific index to:
a) evaluate the SELECT
b) ORDER by.
thanks
-nat
Just noticed (with query.explain()). It’s automatic. Neat. Makes pretty intelligent choices based on select and order by, and sometimes builds a temp order index as well as using another index for the select.
Cool, thanks.
-nat
2 Likes
All credit for that goes to SQLite, of course. Its query optimizer has gotten pretty good recently.
But when the wrong index is chosen, can that be changed? I have a situation where I have multiple indexes. One is perfectly designed for my query and one is way more general. The latter is being used and really hurting the performance of my query. Is there a way to tell Couchbase / SQLite to use the index I think it needs to?
Upon further research, this happens only on a filtered index.
For instance:
Database.CreateIndex(“IX_OrderHistory_OrderRowId”, IndexBuilder.ValueIndex(ValueIndexItem.Expression(Expression.Property(nameof(Data.DocumentBase.Type)).EqualTo(Expression.String(nameof(OrderHistory)))),
ValueIndexItem.Expression(Expression.Property(nameof(OrderHistory.OrderRowId)))));
fails to select the proper index. But this one works:
Database.CreateIndex(“IX_OrderHistory_OrderRowId”, IndexBuilder.ValueIndex(ValueIndexItem.Expression(Expression.Property(nameof(Data.DocumentBase.Type))),
ValueIndexItem.Expression(Expression.Property(nameof(OrderHistory.OrderRowId)))));
By “filtered index” do you mean a partial index on a subset of the documents? We don’t have that (yet).
It looks like the first statement you gave creates an index whose primary key is a boolean, which is usually a bad idea. Are you mistaking that syntax for a way to create a partial index?
Yes, I was thinking that would give me a partial index. So at this point to index a specific type of document and one of its fields, the index will be on the ENTIRE database? Any idea when filtered indexes might be brought to CBL?
I have this query:
QueryBuilder.Select(SelectResult.Expression(Expression.Property(nameof(Order.RowId)).From("O")))
.From(DataSource.Database(Manager.MainDb.Database).As("H"))
.Join(Join.InnerJoin(DataSource.Database(Manager.MainDb.Database).As("O"))
.On(Expression.Property(nameof(Order.CurrentTaskId)).From("O").EqualTo(Expression.Property(nameof(OrderHistory.OrderRowId)).From("H"))))
.Where(DatabaseBase.CreateWhereDocumentTypeEquals(nameof(OrderHistory), "H")
.And(Expression.Property(nameof(OrderHistory.CompleteDate)).From("H").IsNullOrMissing()))
Which give this using Explain():
SELECT fl_result(fl_value("O".body, 'RowId')) FROM kv_default AS "H" CROSS JOIN kv_default AS "O" ON (fl_value("O".body, 'CurrentTaskId') = fl_value("H".body, 'OrderRowId')) AND ("O".flags & 1) = 0 WHERE (fl_value("H".body, 'Type') = 'OrderHistory' AND (fl_value("H".body, 'CompleteDate') = x'' OR fl_value("H".body, 'CompleteDate') IS NULL)) AND ("H".flags & 1) = 0
0|0|0| SEARCH TABLE kv_default AS H **USING INDEX IX_User_ClientRowId (<expr>=?)**
0|1|1| SCAN TABLE kv_default AS O
I have these indexes created (among others):
Database.CreateIndex("IX_OrderHistory_CompleteDate", IndexBuilder.ValueIndex(ValueIndexItem.Expression(Expression.Property(nameof(Data.DocumentBase.Type))),
ValueIndexItem.Expression(Expression.Property(nameof(OrderHistory.CompleteDate)))));
// User indexes
Database.CreateIndex("IX_User_ClientRowId", IndexBuilder.ValueIndex(ValueIndexItem.Expression(Expression.Property(nameof(Data.DocumentBase.Type))),
ValueIndexItem.Expression(Expression.Property(nameof(User.ClientRowId)))));
Why isn’t it choosing IX_OrderHistory_CompleteDate? The query never ever uses the UserClientRowId field!
Yes, I was thinking that would give me a partial index.
If there was something in the method documentation that misled you to think that, or you think it could be clearer, let us know; there should be a Send Feedback button at the bottom of the page.
So at this point to index a specific type of document and one of its fields, the index will be on the ENTIRE database? Any idea when filtered indexes might be brought to CBL?
Yes. Is this causing noticeable performance problems? We know this is a limitation, but so far no one’s called it out as an actual issue in their app, so the priority hasn’t been high.
The selection of the wrong index is causing the performance issue. A query that should take less than 200ms to run takes a couple of minutes. So I need a way to get around this, and soon! Either partial indexes or the ability to provide an index hist would work. Both are ideal.
I don’t see a feedback button. What was misleading is that the ValueIndexItem allows or expressions that include Property.EqualTo a value. Since this filters a query I assumed that it would filter an index as well. I guess that combined with the lack of specifically stating that filtered indexes are not supported (unless I missed it) caused me to go down this path.
The query builder API tries to constrain what you can do using type-checking, but I don’t know if it’s capable of disallowing comparison operators in index expressions. Hm, probably it shouldn’t disallow them, because there are valid uses, like as input to a case.
I can’t figure out what’s going on with the wrong index being used. This can be frustrating to diagnose in SQLite even without the extra layers of abstraction we add on top. I wonder if the OR operator added by the IsNullOrMissing() expression is contributing toward it…
As an experiment, could you try changing IsNullOrMissing() to a simpler test like a string comparison, and see whether that causes the proper index to be used?
Same result. Wrong index chosen. I changed it to .EqualTo(Expression.Date(DateTimeOffset.Now)) since it’s a DateTimeOffset? property. It should return 0 documents very quickly.
Is this reproducible without the other indexes you haven’t shown here? If you can find the minimal setup that will reproduce this, and file a bug report here, I can take a look … if it’s a SQLite bug I can pass it to them. Thanks.
@jens, I have a repro and am submitting it.