Hi Torsten,
the reason why you don't see anything in the EXPLAIN statement is that the use of an index is decided at runtime by the respective operator. The algebra.sort code will have an if statement that determines at query evaluation time if there is a usable ordered index and use it.
The best way (for now) to see if the index is used or not is to start the mserver5 with the --algorithms flag. This flag will produce alot of output on the runtime decissions. For your example, I get the following output:
#BATproject(l=tmp_523#3-sorted-key,r=tmp_433#3[int]-revsorted-key) #BATproject(l=tmp_523,r=tmp_433)=tmp_323#3-revsorted-key #BATcheckorderidx: reusing persisted orderidx 283 #BATproject(l=tmp_322#3-key,r=tmp_323#3[int]-revsorted-key) #BATproject(l=tmp_322,r=tmp_323)=tmp_417#3-key 11us #BATgroup(b=tmp_417#3[int],s=NULL#0,g=NULL#0,e=NULL#0,h=NULL#0,subsorted=1): trivial case: 1 element per group #BATprojectchain with 3 (2) BATs, size 3, type int #BATproject(l=tmp_322#3-key,r=tmp_323#3[int]-revsorted-key) #BATproject(l=tmp_322,r=tmp_323)=tmp_523#3-key 9us
And the third line says "BATcheckorderidx: reusing persisted orderidx 283" which is an indication that MonetDB has found the index and is using it. As a side note, I think in this case it has also figured out that x is revsorted-key (aka unique reverse sorted) but I dont know which one wins over the other:)
So in short, if you start mserver5 with --algorithms, redirect the console output in a file and search for BATcheckorderidx you will see if the ordered index is used.
Please let us know if there are cases that ordered index should be used but it is not.
lefteris
On Fri, Feb 16, 2018 at 10:28 AM, Martin Kersten martin.kersten@cwi.nl wrote:
Hi Same holds for ordered indices
On 16/02/2018 10:27, Torsten Grust wrote:
Hi Martin,
thanks for the super-quick reply. You are talking of /imprints/ here but I assume that the same is true for /order indexes/ (these are separate concepts, right?).
Best wishes, —Torsten
On Fri, Feb 16, 2018 at 9:55 AM Martin Kersten <martin.kersten@cwi.nl mailto:martin.kersten@cwi.nl> wrote:
Dear Torsten The use of an imprint is only part of the algebra.selectimplementation and depends on actual several properties.
In this particular query, the SQL compiler when encountering an orderby issues a sort statement, even if this is strictly not needed. That will be detected by the sort implementation.
regards, Martin On 15/02/2018 23:31, Torsten Grust wrote: > Dear all, > > given a table definition and (explained) SQL query as follows: > > |CREATE TABLE foo (x INT,y INT); INSERT INTO foo VALUES (3,1),(2,2), (1,3); ALTER TABLE foo SET READ ONLY; CREATE ORDERED INDEX foo_x ON foo(x); EXPLAIN SELECT * FROM foo ORDER BY x; | > > In the |EXPLAIN| output, am I supposed to see that the order index is indeed used? I currently do see an |algebra.sort| operation, of course, but do not see any reference to the |foo_x| index. I had expected to find an |bat.getorderidx| operation in the > MAL program. > > Can anyone shed light on this? I'd be grateful. > > Cheers, > > —Torsten > > -- > | Prof. Dr. Torsten Grust > | Database Systems — Universität Tübingen (Germany) > | ✉︎ torsten.grust@uni-tuebingen.de mailto:torsten.grust@uni-tuebingen.de <mailto:torsten.grust@uni-tuebingen.de mailto:torsten.grust@uni-tuebingen.de> > | db.inf.uni-tuebingen.de http://db.inf.uni-tuebingen.de http://db.inf.uni-tuebingen.de > > > _______________________________________________ > users-list mailing list > users-list@monetdb.org mailto:users-list@monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list > _______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Prof. Dr. Torsten Grust | Database Systems — Universität Tübingen (Germany) | ✉︎ torsten.grust@uni-tuebingen.de mailto:torsten.grust@uni-tuebingen.de | db.inf.uni-tuebingen.de http://db.inf.uni-tuebingen.de
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list