Thanks for your reply.

I went as high as using (number of rows)^4, resulting in a cost of ~440 
trillion for the unindexed case, along with setting the cost to 1 for the 
indexed case, and it still won't use my index.

I'd like to step out of my xBestFilter implementation into SQLite code to see 
if I can tell what is going on there, but when I step out, the call stack knows 
where I'm supposed to be, but the debugger does not find the right line in 
sqlite3.c.  I have built sqlite3.c simply by adding the amalgamation to my 
Microsoft Visual Studio 2013 C++ project.  Is there something I can do to make 
the debugger work?

I will postpone index creation until the call to xFilter, I reckon, once I work 
out these other issues.  Thanks for the tip!

Eric

-----Original Message-----
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Friday, May 15, 2015 12:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes?

On 5/15/15, Eric Hill <Eric.Hill at jmp.com> wrote:
>
> So, in the first case, I do not create an index (which I signify by 
> setting idxNum to -999), and I set the cost (and, est. rows) to 4581.  
> In the second case, I create an index (0) and set cost to log10(4581) 
> = 3.66 and est. rows to 4.  Yet, later, whenever xFilter is called for 
> the inventory table, SQLite passes in idxNum = 999 and nConstraints = 
> 0.  The index I dutifully created is never asked for.  In cases where 
> there is a single constraint, SQLite does ask request the index in the 
> xFilter call, but it seems that for all the cases where multiple 
> constraints are involved, the index is not being used.
>

Two things:

(1) You probably shouldn't be "creating an index" in response to an xBestIndex 
call.  xBestIndex should be thought of as a "what-if"
function.  It is asking your virtual table what it could do with a query given 
certain constraints.  SQLite makes no guarantees that it will actually ever 
call your virtual table that way - it is merely exploring possibilities.

(2) The query planner looks at many different cost factors and tries to pick 
the best overall query plan.  You've told it that running your virtual table 
without an index is 1252 times slower than running it with an index.  And it 
takes this into consideration.  That SQLite is not choosing to use the virtual 
table index indicates that some other part or parts of the join would be more 
than 1252 times slower if the virtual table index were in fact used, and so the 
overall query plan is faster even without the virtual table index.  If these 
estimates are incorrect, then an obvious work-around is merely to increase the 
cost of not using the indexing mode on the virtual table.  Have it return 10x 
or 100x the cost (45810 or 458100) when not using an index, and see if that 
helps.

--
D. Richard Hipp
drh at sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to