On 2014/09/24 16:52, Rob Golsteijn wrote:
Hi List,

I was looking at the query plan of a rather simple query, but I don't 
understand why sqlite would choose this query plan.

...I was surprised that sqlite came up with the inferior query plan...

Note: After an "analyze aaa" (on a decently populated table) sqlite chooses the 
full table scan instead of creating an automatic index (but our application never uses 
'analyze' to avoid that other (bad performing) query plans are used during operation than 
during testing)....
Note 2: Adding "NOT INDEXED" to aa1 gives the desired query plan, but of course 
I prefer that sqlite choses the right query plan.

I think this issue is fixed since it does not do it in my version, but... even if it isn't, let me just note that basically SQLite chooses a Query Plan that might in many circumstances work perfectly since it has no information available about the data. Further to this SQLite provides not one, but two explicit methods for you to improve the query planning should the default not fit the best for your specific query - yet you choose to ignore BOTH of them and expects SQLite to pick a good plan without any knowledge of the data, by default. [and ironically you are very happy to circumvent a whole part of SQLite Query planning prowess to hide "other" QP issues in stead of fixing it or requesting a fix, but won't add anything legal and valid that would actually improve it. That is just bizarre.]

If I was the asker of this question I would concentrate on what you mention in passing in Note1 with regards to avoiding "other" bad plans. If you do use Analyze and then at any point find the QP comes up with a bad plan WITH access to analyze data - now THAT would be a reason to complain and I have seen requests such as that cause a QP overhaul many times here, you'd actually improve it for everyone should you find a use-case with real QP problems based on proper knowledge of data shape.


_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to