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 | db.inf.uni-tuebingen.de
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> | 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
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> 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> | 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
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
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
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
participants (3)
-
Lefteris -
Martin Kersten -
Torsten Grust