Reusue index sorting capabilities in ORDER BY clause processing [moved]

[lvca]

This is Issue 765 moved from a Google Code project.
Added by 2012-03-25T08:07:32.000Z by lomakin....@gmail.com.
Please review that bug for more context and additional comments, but update this bug.

Original labels: Type-Enhancement, Priority-Medium, v1.3.0

Original description

If field in ORDER BY SQL expression is used in index that has been reused for query processing it is better do not sort by this field but just relay on fact that data already has been sorted by this field in index.

This will allow to paginate data using ">=" operator and ORDER BY SQL expression. 

[Koc]

@lvca Looks like this issue isn't fixed. I've got structure from #1537 and tryed execute query:

orientdb> select from channel where id < 304444 AND id > 2 order by id limit 10

----+------+----+----------+-------------+---------
#   |@RID  |id  |name      |category_name|object_id
----+------+----+----------+-------------+---------
0   |#12:3 |3   |ride:19679|ride         |19679
1   |#12:4 |4   |user:11   |user         |11
2   |#12:5 |5   |user:3172 |user         |3172
3   |#12:6 |6   |user:2762 |user         |2762
4   |#12:7 |7   |user:7131 |user         |7131
5   |#12:8 |8   |ride:19680|ride         |19680
6   |#12:9 |9   |user:8878 |user         |8878
7   |#12:10|10  |ride:19681|ride         |19681
8   |#12:11|11  |user:13565|user         |13565
9   |#12:12|12  |ride:19633|ride         |19633
----+------+----+----------+-------------+---------

10 item(s) found. Query executed in 117.374 sec(s).

Why so slow?

[lvca]

Mhmh, can you rather try this?

select from channel where id > 2 and id < 304444 order by id limit 10

[Koc]

orientdb> select from channel where id > 2 and id < 304444 order by id limit 10

----+------+----+----------+-------------+---------
#   |@RID  |id  |name      |category_name|object_id
----+------+----+----------+-------------+---------
0   |#12:3 |3   |ride:19679|ride         |19679
1   |#12:4 |4   |user:11   |user         |11
2   |#12:5 |5   |user:3172 |user         |3172
3   |#12:6 |6   |user:2762 |user         |2762
4   |#12:7 |7   |user:7131 |user         |7131
5   |#12:8 |8   |ride:19680|ride         |19680
6   |#12:9 |9   |user:8878 |user         |8878
7   |#12:10|10  |ride:19681|ride         |19681
8   |#12:11|11  |user:13565|user         |13565
9   |#12:12|12  |ride:19633|ride         |19633
----+------+----+----------+-------------+---------

10 item(s) found. Query executed in 93.384 sec(s).

[Koc]

I've tryed orientdb-1.5.0-20130625.170746-8-distribution.zip, same behaviour occurs. Should I open new ticket or you will reopen this?

[lvca]

Can you retry with latest snapshot? Try this:

select from channel where id between 3 and 304443 order by id limit 10

If nothing changes assure you've an index:

explain select from channel where id between 3 and 304443 order by id limit 10

[Koc]

OrientDB console v.1.5.0-SNAPSHOT (build UNKNOWN@r; 20130626-0336) www.orientechnologies.com
T

orientdb> select from channel where id between 3 and 304443 order by id limit 10

10 item(s) found. Query executed in 122.534 sec(s).

orientdb> select from channel where id between 3 and 304443 limit 10

10 item(s) found. Query executed in 0.011 sec(s).

orientdb> explain select from channel where id between 3 and 304443 order by id limit 10

Profiled command '{elapsed:1.48064E-4,resultType:collection,resultSize:0}' in 0.003000 sec(s):
{"@type":"d","@version":0,"elapsed":1.48064E-4,"resultType":"collection","resultSize":0,"@fieldTypes":"elapsed=f"}

[lvca]

The index hasn't been used. Can you execute:

orientdb> create index Channel.id unique

Then retry it.

[Koc]

orientdb> list indexes

INDEXES:
----------------------------------------------+------------+-----------------------+----------------+-----------+
 NAME                                         | TYPE       |         CLASS         |     FIELDS     | RECORDS   |
----------------------------------------------+------------+-----------------------+----------------+-----------+
 dictionary                                   | DICTIONARY |                       |                |         0 |
 channel.id                                   | UNIQUE     | channel               | id             |    550256 |
 channel.name                                 | UNIQUE     | channel               | name           |    550256 |
 ORole.name                                   | UNIQUE     | ORole                 | name           |         3 |
 OUser.name                                   | UNIQUE     | OUser                 | name           |         3 |
----------------------------------------------+------------+-----------------------+----------------+-----------+
 TOTAL = 6                                                                                              2513103 |
----------------------------------------------------------------------------------------------------------------+

orientdb> drop index channel.id

Removing index...
Dropped index in 2.818000 sec(s).
Index removed successfully

orientdb> create index channel.id unique

Creating index...
Created index successfully with 550256 entries in 118.476997 sec(s).
Index created successfully

orientdb> select from channel where id between 3 and 304443 order by id limit 10
10 item(s) found. Query executed in 84.497 sec(s).

orientdb> explain select from channel where id between 3 and 304443 order by id limit 10
Profiled command '{elapsed:1.29346E-4,resultType:collection,resultSize:0}' in 0.004000 sec(s):
{"@type":"d","@version":0,"elapsed":1.29346E-4,"resultType":"collection","resultSize":0,"@fieldTypes":"elapsed=f"}

[lvca]

So for strange reason the index isn't used. Is the order by or limit? Please can you try:

explain select from channel where id between 3 and 304443 limit 10

and

explain select from channel where id between 3 and 304443 order by id

[Koc]

I cann't explain 2nd query - got out of memory exception.

orientdb> explain select from channel where id between 3 and 304443 limit 10

Profiled command '{compositeIndexUsed:1,involvedIndexes:[1],indexReads:10,current:#12:6,documentAnalyzedCompatibleClass:10,elapsed:0.1247977,resultType:collection,resultSize:10}' in 0.126000 sec(s):
{"@type":"d","@version":0,"compositeIndexUsed":1,"involvedIndexes":["channel.id"],"indexReads":10,"current":"#12:6","documentAnalyzedCompatibleClass":10,"elapsed":0.1247977,"resultType":"collection","resultSize":10,"@fieldTypes":"compositeIndexUsed=l,involvedIndexes=e,indexReads=l,documentAnalyzedCompatibleClass=l,elapsed=f"}
orientdb> explain select from channel where id between 3 and 304443 order by id

Error: com.orientechnologies.orient.core.exception.OStorageException: Error on executing command: sql.explain select from channel where id betw...
Error: java.lang.ClassCastException: java.lang.OutOfMemoryError cannot be cast to java.lang.Exception

[andrii0lomakin]

Guys never use approach "create index Channel.id unique" to create index this syntax deprecated years ago. I will check data structure but reason seems that index was not created properly.

[a-unite]

Guys never use approach "create index Channel.id unique" to create index this syntax deprecated years ago.

This is not the first time I see this kind of advice.
Andrey, may you please explain it in more details?
Is it wrong syntax for automatic index? Does it mean that examples for automatic index creation in docs (http://orientdb.com/docs/last/Indexes.html) are obsolete?

Sorry for off-topic.

[lvca]

Deprecating an API doesn't mean it should be broken, so it's a bug anyway. @Laa is checking if this is the problem.

[andrii0lomakin]

Yeah that is actually sql engine issue not storage level , but lets check it.

[andrii0lomakin]

I tried on 2.1-SNAPSHOT
`orientdb {db=cnt}> explain select from channel where id between 3 and 304443 order by id limit 10

Profiled command '{documentReads:8,fullySortedByIndex:true,documentAnalyzedCompatibleClass:8,recordReads:8,fetchingFromTargetElapsed:1,indexIsUsedInOrderBy:true
,compositeIndexUsed:1,current:#11:9,involvedIndexes:[1],limit:10,evaluated:8,user:#5:0,elapsed:2.349393,resultType:collection,resultSize:8}' in 0.004000 sec(s):

{"@type":"d","@Version":0,"documentReads":8,"fullySortedByIndex":true,"documentAnalyzedCompatibleClass":8,"recordReads":8,"fetchingFromTargetElapsed":1,"indexIs
UsedInOrderBy":true,"compositeIndexUsed":1,"current":"#11:9","involvedIndexes":["channel.id"],"limit":10,"evaluated":8,"user":"#5:0","elapsed":2.349393,"resultT
ype":"collection","resultSize":8,"@fieldTypes":"documentReads=l,documentAnalyzedCompatibleClass=l,recordReads=l,fetchingFromTargetElapsed=l,compositeIndexUsed=l
,current=x,involvedIndexes=e,evaluated=l,user=x,elapsed=f"}`

So as you can see indexes are used. I close this issue as invalid.