New subject: Order indexes: used or not?

20 Feb 2018

      Hi Torsten,
To start mserver5 manually on a database created by monetdb/monetdb, you need to use the same command line (-options) the monedtd use to start mserver5, in particular the valley option. You find the details in the merovingian.log in your dbfarm
Best,Stefan

-------- Original message --------From: Torsten Grust <torsten.grust@gmail.com> Date: 2/20/18  17:08  (GMT+01:00) To: Lefteris <lsidir@gmail.com> Cc: Communication channel for MonetDB users <users-list@monetdb.org> Subject: Re: Order indexes: used or not? 
Hi Lefteris,
that's very interesting.  Thanks!  I was about to experiment with this here, but I stumbled.
When you start a database via mserver5 directly (instead of using monetdbd which, as I understand, is the recommend method), does the method of authentication change?  I fail to login to my scratch database:
$  mserver5 --dbpath=<path>/scratch
# MonetDB 5 server v11.27.13 "Jul2017-SP4"
# Serving database 'scratch', using 4 threads
# [...]
# MonetDB/SQL module loaded
...
[switch to other terminal]
$ mclient -d scratch -u monetdb
password: <supply password "monetdb">
InvalidCredentialsException:checkCredentials:invalid credentials for user 'monetdb'
Whereas the login works just fine below:
$ monetdbd start <path>
$ mclient -d scratch -u monetdb
password: <supply password "monetdb">
Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2017-SP4)
Database: MonetDB v11.27.13 (Jul2017-SP4), 'mapi:monetdb://closure:50000/scratch'
[...]
sql>

I must be doing something stupid.
Cheers,  —Torsten

On Tue, Feb 20, 2018 at 3:40 PM Lefteris <lsidir@gmail.com> wrote:
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
-- 
| Prof. Dr. Torsten Grust
| Database Systems — Universität Tübingen (Germany)| ✉︎ torsten.grust@uni-tuebingen.de| db.inf.uni-tuebingen.de

Re: Order indexes: used or not?

Stefan Manegold

Torsten Grust

tags

participants (2)