Yeap. And as I said, the predicted result sizes by ANALYZE are pretty accurate, the data is pretty linearly distributed. The unsorted plan is perfect, so a wrong prediction is not at fault, but the sorted plan is completely different. The sorted column is an aggregate anyway, so no index can help, but a sort in place could deal with the problem. After some more research it appears sqlite does not have a sort buffer (someone correct me if I'm wrong), but then what is the TEMP B-TREE?
On 14.03.2015 18:40, Bert Huijben wrote: > >> -----Original Message----- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of Dinu Marina >> Sent: donderdag 12 maart 2015 11:34 >> To: sqlite-users at mailinglists.sqlite.org >> Subject: Re: [sqlite] Weird (slow) TEMP B-TREE ORDER BY >> >> Hi Richard, >> >> Like said, we've already tried: >> CREATE TEMP TABLE x AS (...query without outer sort...); CREATE xind ON >> x (price); SELECT * FROM x ORDER BY 2 >> but it also takes 30 seconds; for some really esoteric reason, CREATE >> TABLE x AS ... takes all of those 30s, even though the execution plan >> looks identical to the simple select (the same 72 steps). This is what I >> would dare call "spooky", any explanation would be appreciated here too. > Did you try running analyze after adding some data and before looking at the > query plans? > > With indexes that contain multiple columns adding the sqlite_stat1 table > (via analyze, or in another way) may introduce huge differences in query > plans and performance. > > We found that out the hard way in Subversion, because we have a pretty much > static first column wc_id in most of our indexes in preparation for some new > features. Even when it used the right indexes in query plans, it sometimes > didn't use all the columns that it should. > > We now add an initial sqlite_stat1 table and we can trust to see a stable > (good) performance from Sqlite. > https://www.sqlite.org/optoverview.html#manctrl > > > Bert > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
