On 2019/09/20 2:49 PM, Fredrik Larsen wrote:
Hi Ryan
Nobody is proposing that QP should automagically add an index, I'm only
asking why the QP does not use already added index, that is specially added
for this specific case. I don't thinks this is a very "obscurest of
use-case" or to much to ask for, in fact, this is the expected behavior for
even the simplest SQL engines, and so especially sqlite.
Apologies if I was unclear, I'm not shouting at you for asking - this is
not that kind of forum, and it's a perfectly good request.
To be clear, I did not mean you are asking for it to index, I am saying
that it already (automagically) makes an index to use for itself in
service of the group-by, which is grand because it also solves the
forward ordering without the need of an explicit index.
I further tried to make the case that It only makes this index as a
temporary index that cannot/will not currently be traversed in the
reverse, which is where it falls short to your specific (yes, very
obscure) use case.
However, my point was forged more towards you not depending on the Index
it makes for anything, upon reading DD's post, but after re-reading your
post, I see the question is not so much that you expect the behaviour to
get a certain output, but that the output comes at the cost of speed
where it should not carry any speed penalty.
In this regard, let me adjust my original statement more in the
direction of ambivalence. The optimization should be implemented if
easy. The use case is obscure, but definitely valid, and more
importantly, I don't think there is another way to fix it. If, on the
other hand, it is CPU heavy in some way, or IO heavy for very large
selects, avoid it rather. I doubt more than 0.00001% of all goup-by
output is DESC ordered[1].
Cheers,
Ryan
[1] - That is a completely made up statistic (obviously) and I will
happily abide by a higher figure if claimed, but that burden of proof is
not on me. :)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users