Troubleshooting Secondary Indexes
Describes how to debug and troubleshoot usage of secondary indexes.
The following table lists problems you may encounter when using secondary indexes. Based on the symptoms listed in the first column, refer to the section in the third column to further troubleshoot the issue.
Symptom | Possible Cause | Troubleshooting Steps |
---|---|---|
Query performance is slow | Query is not using secondary indexes | |
Non-optimal OJAI query plan chosen | Examining the OJAI Query Plan | |
Non-optimal query plan chosen by OJAI Distributed Query Service | Determining Index Use | |
Inconsistent query results | Secondary index update lag | Identifying Secondary Index Lag |
Unresolved encoding errors | Troubleshooting Secondary Index Encoding Errors | |
Query runs out of memory | Memory configuration in the OJAI Distributed Query Service set too low | Adjusting Memory Settings in the OJAI Distributed Query Service |
Secondary Index Restrictions
When troubleshooting secondary indexes, you should also keep in mind the following restrictions:
- Name Restrictions
- You cannot use the following characters in the index name and in the indexed fields:
To use the following characters in the index name and in the indexed fields, enclose them either in single or double quotes:< > ? % \
For example:; | ( ) /
maprcli table index add -path /volume1/MYTABLE -index "MYTABLE1_ANALYSIS_1設備^=#;{}&()/" \ -indexedfields "日時_timestamp":desc,"設備タイプ","LOTNo" -includedfields \ "データタイプ","達成度^=#;{}&()/" (or) maprcli table index add -path /volume1/MYTABLE -index 'MYTABLE1_ANALYSIS_1設備^=#;{}&()/' \ -indexedfields "日時_timestamp":desc,"設備タイプ","LOTNo" -includedfields \ 'データタイプ','達成度^=#;{}&()/'
To use either the ' or the " character in the index name and in the indexed fields, enclose:For example:- the ' character within double quotes (")
- the " character within single quote (')
maprcli table index add -path /volume1/MYTABLE -index "'MYTABLE1_ANALYSIS_1設備^=#;{}&()/" \ -indexedfields "日時_timestamp":desc,"設備タ'イプ","LOTNo" -includedfields \ "データタイプ'","達成度^=#;{}&()/" (or) maprcli table index add -path /volume1/MYTABLE -index '"MYTABLE1_ANALYSIS_1設備^=#;{}&()/' \ -indexedfields "'日時_timestamp":desc,"設備タイプ","LOTNo" -includedfields \ 'データタイプ"',"達成度^=#;{}&()/"
- Type Restrictions
-
Indexed fields must contain scalar types.
Scalar types include integer, character, boolean, string, and byte. See Data Types and Secondary Index Fields for the complete list of types.Indexed fields cannot contain nested documents.
An indexed field can be a subfield of a nested document, provided the subfield contains scalar types.Indexed fields cannot contain arrays.
- Size Restrictions
-
The maximum size of all indexed fields in an index is 32 KB.
If the collective size exceeds 32 KB, then an insert of the corresponding document results in an encoding error (INDEX_ROW_KEY_ENCODER_ERROR_ENCODING_IS_TOO_LONG
).The maximum number of indexes that you can create on a JSON table is 32.
- Field Definition Restrictions
-
You cannot specify individual array elements as indexed fields.
-
You cannot specify a table's
_id
field as an indexed field. -
You can include a specific field only once as either an indexed or included field, except if you cast the field to different types.
For example:
You can create an index in which the
The included field retains the original data type of thescore
field is an indexed field cast as adouble
type, andscore
is also an included field.score
field.maprcli table index add -path /castTable \ -index castIdx1 \ -indexedfields '$CAST(score@DOUBLE)' \ -includedFields score
You can create an index in which the
score
field is an indexed field, cast as adouble
type, and thescore
field is also another indexed field, cast as along
type.maprcli table index add -path /castTable \ -index castIdx2 \ -indexedfields '$CAST(score@DOUBLE)','$CAST(score@LONG)'
-
You cannot use casts with included fields.
You cannot specify a field as either an indexed or included field if the field is also specified as a column family JSON path name.
For example, suppose you have the following JSON table:{ "_id" : "ID", "a" : { "b" : { "c" : "value", "d" : "value" }, "e" : "value" } }
If you create a column family at fieldc
in the JSON patha.b.c
, you cannot define fielda.b.c
as either an indexed or included field. You can define the fieldsa
,a.b
, anda.b.d
as either indexed or included fields.You cannot specify an included field in which the data in the field spans more than one column family.
In the following example, the included fieldsl1.sl2
spans column families,cf2
andcf3
:maprcli table cf list -path /cftab compressionperm readperm traverseperm jsonfamilypath writeperm minversions maxversions compression ttl inmemory cfname memoryperm u:root u:root u:root u:root 0 1 lz4 2147483647 false default u:root u:root u:root u:root sl1 u:root 0 1 lz4 2147483647 false cf1 u:root u:root u:root u:root sl1.sl2.sl3 u:root 0 1 lz4 2147483647 false cf2 u:root u:root u:root u:root sl1.sl2.sl3.sl4 u:root 0 1 lz4 2147483647 false cf3 u:root maprcli table index add -path /cftab -index i1 -indexedfields sl1.sl2.sl3.sl4.l4a, sl1.l1a -includedfields sl1.sl2,sl1.sl2.sl3.sl4.sl5.l5b -json { "timestamp":1507419777919, "timeofday":"2017-10-07 04:42:57.919 GMT-0700 PM", "status":"ERROR", "errors":[ { "id":22, "desc":"Data for included field sl1.sl2 may not span more than one column family." } ] }
- Option Restrictions
-
Because indexes are automatically split, you cannot disable splits when you create your index.
- Index Use Restrictions
-
Indexes optimize filter conditions if you compare indexed fields against scalar values, and the field and scalar value have comparable types.
Indexes do not optimize non-existence filter conditions.