Understanding query optimizer choices

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Understanding query optimizer choices

Deon Brewis
I have a query that joins with a virtual table where the optimizer has a choice of picking between 2 indexes for something, and it picks by far the wrong one.

I'm trying to understand how it all works.

I understand the output of both 'explain' and 'explain query plan' but I can't see from either of those why it will choose one plan over another. Subsequently I don't really know where my estimatedRows and estimatedCost from my virtual table comes into play with the decision so it's hard developing an intuitive feeling for what the correct values are to return.

Is there a way that I can view the cost metrics that goes into each query plan choice to see how it calculates the best index?

- Deon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Understanding query optimizer choices

Richard Hipp-3
On 11/3/17, Deon Brewis <[hidden email]> wrote:
>
> Is there a way that I can view the cost metrics that goes into each query
> plan choice to see how it calculates the best index?
>

(1) Get a copy of the canonical source tarball
(2) ./configure --enable-debug; make
(3) ./sqlite3 $yourdatabase
(4) At the "sqlite>" prompt, type ".wheretrace 0xfff".
(5) Enter your query

The 0xfff is a mask of different things to be traced.  You have to
refer to the source code to decode the mask.  There is no
documentation explaining the output of .wheretrace.  The output of
.wheretrace is subject to change.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Understanding query optimizer choices

Simon Slavin-3
In reply to this post by Deon Brewis


On 4 Nov 2017, at 12:34am, Deon Brewis <[hidden email]> wrote:

> I have a query that joins with a virtual table where the optimizer has a choice of picking between 2 indexes for something, and it picks by far the wrong one.

Execute the SQL command ANALYZE.  You can add this to your app temporarily, or use the SQLite command-line tool.

Then try everything again.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users