Open
Description
My Environment
- ArangoDB Version: 3.4.6-1
- Storage Engine: RocksDB
- Deployment Mode: Single Server
- Deployment Strategy: Manual Start
- Infrastructure: own
- Operating System: MacOS 10.13.4
- Total RAM in your machine: <16Gb
- Disks in use: SSD
- Used Package: other
Component, Query & Data
Affected feature:
Optimizer
AQL query (if applicable):
FOR c IN Patients
SORT c.name_given, c.name_family desc
RETURN c
AQL explain (if applicable):
Query String:
FOR c IN Patients
SORT c.name_given, c.name_family desc
RETURN c
Execution plan:
Id NodeType Calls Items Runtime [s] Comment
1 SingletonNode 1 1 0.00000 * ROOT
2 EnumerateCollectionNode 1 98 0.00008 - FOR c IN Patients /* full collection scan */
3 CalculationNode 1 98 0.00001 - LET #1 = c.`name_given` /* attribute expression */ /* collections used: c : Patients */
4 CalculationNode 1 98 0.00001 - LET #3 = c.`name_family` /* attribute expression */ /* collections used: c : Patients */
5 SortNode 1 98 0.00010 - SORT #1 ASC, #3 DESC
6 ReturnNode 1 98 0.00000 - RETURN c
Indexes used:
none
Optimization rules applied:
Id RuleName
1 move-calculations-up
2 move-calculations-up-2
Query Statistics:
Writes Exec Writes Ign Scan Full Scan Index Filtered Exec Time [s]
0 0 98 0 0 0.00058
Query Profile:
Query Stage Duration [s]
initializing 0.00000
parsing 0.00004
optimizing ast 0.00000
loading collections 0.00001
instantiating plan 0.00002
optimizing plan 0.00018
executing 0.00024
finalizing 0.00007
Query String:
FOR c IN Patients
SORT c.name_given desc, c.name_family desc
RETURN c
Execution plan:
Id NodeType Calls Items Runtime [s] Comment
1 SingletonNode 1 1 0.00000 * ROOT
7 IndexNode 1 98 0.00033 - FOR c IN Patients /* reverse skiplist index scan */
3 CalculationNode 1 98 0.00001 - LET #1 = c.`name_given` /* attribute expression */ /* collections used: c : Patients */
4 CalculationNode 1 98 0.00001 - LET #3 = c.`name_family` /* attribute expression */ /* collections used: c : Patients */
5 SortNode 1 98 0.00006 - SORT #1 DESC, #3 DESC
6 ReturnNode 1 98 0.00000 - RETURN c
Indexes used:
By Type Collection Unique Sparse Selectivity Fields Ranges
7 skiplist Patients false false 100.00 % [ `name_given` ] *
Optimization rules applied:
Id RuleName
1 move-calculations-up
2 move-calculations-up-2
3 use-indexes
Query Statistics:
Writes Exec Writes Ign Scan Full Scan Index Filtered Exec Time [s]
0 0 0 98 0 0.00073
Query Profile:
Query Stage Duration [s]
initializing 0.00000
parsing 0.00004
optimizing ast 0.00000
loading collections 0.00001
instantiating plan 0.00002
optimizing plan 0.00012
executing 0.00045
finalizing 0.00007
Steps to reproduce
- create skiplist indexes for 2 different properties. eg. name_given, name_family
execute a query which sorts this two properties with same direction.
etc:
FOR c IN Patients
SORT c.name_given desc, c.name_family desc
RETURN c
You will see, index for first sort property will be used.
- execute another query which sorts two properties with different direction
etc:
FOR c IN Patients
SORT c.name_given, c.name_family desc
RETURN c
You will see, no index used.
Problem:
Optimizer ignores indexes if different sort directions used.
Expected result: