On Wed, Sep 06, 2017 at 10:57:41PM +0200, R Smith wrote: > On 2017/09/06 8:26 PM, Nico Williams wrote: > >On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: > >>-- Another interesting thing to note: The INTERSECT test produces ORDERED > >>-- output, which suggests that an ORDER-BY addition to the query would > >>-- favour the INTERSECT method. > >Nothing about INTERSECT requires it to produce ordered output. > > No, and it was not suggested, it was just noted that it does, suggesting > that it could be the more performant choice when adding an ORDER BY clause, > which turned out to not only be true in terms of being the better choice, > but also that it itself sped up by simply adding the ORDER BY clause as was > demonstrated in Test 6.
I point this out only because users should know not to assume result set order without an ORDER BY. > >Nothing about the JOIN case makes it not possible to produce ordered > >output by accident. > > Yet it doesn't seem to by accident, which would suggest that an ORDER BY > clause when added to the JOIN statements would incur an additional time See my explanation below. > penalty for having to actually order the results - Yet, as again > demonstrated in Test 6, the ORDER BY actually sped up the JOIN query too > (perhaps via forcing the Index earlier or used in a different way) - which > was most interesting, and, as you noted, there is nothing about the JOIN > that precludes it from having ordered output, so this optimization might be > worthwhile. > > >You'll want to re-measure with an ORDER BY added. > > I did. It was done in Test 6. It showed significantly interesting results. > Was my explanation lacking in clarity or did it fall down the TLDR; rabbit > hole? :) I saw it. I think the ORDER BY helped the JOIN because it caused SQLite3 to scan a covering index (the primary) key instead of scanning the table. That without it SQLite3 didn't use that index is rather inefficient, though it may not be a win in real-world use-cases to fix that. Of course, IF you had used WITHOUT ROWIDs you would have found (I'm sure) that the JOIN also produced ordered results by default and was as fast as in your 6th test. In fact, INTERSECT does an implicit ordering step by building a b-tree that the JOIN with the index scan optimization does not have to build at all, so JOIN has a leg up on INTERSECT in that sense. > I agree, and not only the INTERSECT optimization but the tests suggest > adding a silent ORDER BY would also be an optimization, though not sure if I think here the ORDER BY merely forced SQLite3 to pick the more efficient query plan, and that it's probably a (rather minor) optimizer bug that it didn't do so to begin with without the ORDER BY. > the effort-to-pleasure ratio is low enough yet. Perhaps if re-doing the > tests with tables using several more non-Integer columns to see if the > optimization could be generalized across all kinds of data in some way. I > might pursue this later when I have some time. If you'll redo this I'd urge you to use WITHOUT ROWIDS. First, that's almost always the right thing to do anyways. Second, it won't perform worse but likely will perform better. Third, write performance definitely should improve with WITHOUT ROWIDS. Fourth, I think users are starting to use WITHOUT ROWIDS more, so testing that seems more useful. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
