#23041·arangodb

Multiple filter clauses on same index field + sort on other field causes wrong index selection

Author: matchoCreated Aug 4, 2026Updated Sep 15, 2026

Hello,

Query 1 uses index on computed.image_tags[*].name, computed.image_tags[*].value as expected, and is very fast.

Query 2 (multiple filter clauses) switches to index on date_created.

This is inconsistent, and doesn't seem relevant as 2 filter clauses on the same field are more restrictive than 1.

edit: version3.12.8 enterprise

Thank you, Best regards,

Mathias

indexes in the collection (among many others) :

name	type	fields	unique	selectivity
idx_1700732696947326976	Persistent	date_created, geoloc.lat	false	62.96%
idx_1825111849464496128	Persistent	computed.image_tags[].name, computed.image_tags[].value	false	1.15%

query 1

FOR o IN z_obs
  FILTER 'report_ai' IN o.computed.image_tags[*].name
  SORT o.date_created DESC
  LIMIT 0, 100
  RETURN o._key

explain query 1

Query String (127 chars, results cachable: true):
 FOR o IN z_obs
   FILTER 'report_ai' IN o.computed.image_tags[*].name
   SORT o.date_created DESC
   LIMIT 0, 100
   RETURN o._key
 

Execution plan:
 Id   NodeType          Par     Est.   Comment
  1   SingletonNode                1   * ROOT 
 10   IndexNode               162447     - FOR o IN z_obs   /* persistent index scan, scan only */    /* with late materialization */
 11   MaterializeNode         162447       - MATERIALIZE o INTO #7 /* (projections: `_key`, `date_created`) */   LET #8 = #7.`_key`, #9 = #7.`date_created`
  6   SortNode                162447       - SORT #9 DESC   /* sorting strategy: constrained heap */
  7   LimitNode                  100       - LIMIT 0, 100
  9   ReturnNode                 100       - RETURN #8

Indexes used:
 By   Name                      Type         Collection   Unique   Sparse   Cache   Selectivity   Fields                                                              Stored values   Ranges
 10   idx_1825111849464496128   persistent   z_obs        false    false    false        1.15 %   [ `computed.image_tags[*].name`, `computed.image_tags[*].value` ]   [  ]            ("report_ai" IN o.`computed`.`image_tags`[*].`name`)

Optimization rules applied:
 Id   Rule Name                                  Id   Rule Name                                  Id   Rule Name                         
  1   move-calculations-up                        6   remove-filter-covered-by-index             11   push-down-late-materialization    
  2   move-filters-up                             7   remove-unnecessary-calculations-2          12   materialize-into-separate-variable
  3   move-calculations-up-2                      8   sort-limit                                 13   optimize-projections              
  4   move-filters-up-2                           9   reduce-extraction-to-projection            14   remove-unnecessary-calculations-4 
  5   use-indexes                                10   batch-materialize-documents       

59 rule(s) executed, 1 plan(s) created, peak mem [b]: 0, exec time [s]: 0.00120

query 2

FOR o IN z_obs
  FILTER 'report_ai' IN o.computed.image_tags[*].name
  FILTER 'report_screen' IN o.computed.image_tags[*].name
  SORT o.date_created DESC
  LIMIT 0, 100
  RETURN o._key

explain query 2

Query String (185 chars, results cachable: true):
 FOR o IN z_obs
   FILTER 'report_ai' IN o.computed.image_tags[*].name
   FILTER 'report_screen' IN o.computed.image_tags[*].name
   SORT o.date_created DESC
   LIMIT 0, 100
   RETURN o._key
 

Execution plan:
 Id   NodeType        Par      Est.   Comment
  1   SingletonNode               1   * ROOT 
 12   IndexNode             3248955     - FOR o IN z_obs   /* reverse persistent index scan, index scan + document lookup (filter projections: `computed`.`image_tags`) (projections: `_key`) */    LET #9 = o.`_key`   FILTER (("report_ai" IN o.`computed`.`image_tags`[*].`name`) && ("report_screen" IN o.`computed`.`image_tags`[*].`name`))   /* early pruning */   
  9   LimitNode                 100       - LIMIT 0, 100
 11   ReturnNode                100       - RETURN #9

Indexes used:
 By   Name                      Type         Collection   Unique   Sparse   Cache   Selectivity   Fields                             Stored values   Ranges
 12   idx_1700732696947326976   persistent   z_obs        false    false    false       62.96 %   [ `date_created`, `geoloc.lat` ]   [  ]            *

Optimization rules applied:
 Id   Rule Name                                 Id   Rule Name                                 Id   Rule Name                        
  1   move-calculations-up                       5   use-indexes                                9   move-filters-into-enumerate      
  2   move-filters-up                            6   use-index-for-sort                        10   reduce-extraction-to-projection  
  3   move-calculations-up-2                     7   remove-unnecessary-calculations-2         11   optimize-projections             
  4   move-filters-up-2                          8   fuse-filters                              12   remove-unnecessary-calculations-4

Optimization rules with highest execution times:
 RuleName                                    Duration [s]
 use-indexes                                      0.00022

59 rule(s) executed, 1 plan(s) created, peak mem [b]: 0, exec time [s]: 0.00124

Source: arangodb/arangodb

View original on GitHub View discussion on GitHub