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.select
>> implementation
>>     and depends on actual several properties.
>>
>>     In this particular query, the SQL compiler when encountering an order
>> by
>>     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