Redis Search: sortby is not correct when there are more than 1,000,000 docs in DB

[xappe]

Hello,

We are using Redis Search with RedisJSON to index a list of JSON transactions based on EventDatetime.

The index ticketIdx is created with
FT.CREATE ticketIdx ON JSON PREFIX 1 ticket: SCHEMA $.eventDateTime AS eventDateTime TEXT SORTABLE $.assetType AS assetType TEXT $.salesPersonUt AS salesPersonUt TEXT $.status AS status TEXT

As long as the total number of JSON documents (with or without prefix “ticket:”) are less then 1,000,000 everything is working well.
When it is above 1,000,000, we noticed that querying the index on the sortable field eventDateTime in descending order is working well when limiting the number of results to 10 or 100, but not for 1000. When limiting to 1000, the results are incorrect as they do not contains the latest items.
See below.

Would you know why? is there a configuration parameter to change? I already tried to change MAXDOCTABLESIZE to 2000000 but it did not help

FT.SEARCH ticketIdx “@assetType:(BOND|IRD|FX|EQY|RSF|eMTN|Generic VC|Generic Margin|Securitization
|Call Account|Money Market)” SORTBY eventDateTime desc limit 0 10

1. “392681”
2. “ticket:kdbfx:d0dc1f3b-4f6a-4fcd-96d0-4c7f84a4835c”
3. 1. “eventDateTime”
   2. “2023-09-13@09:35:15.057”

FT.SEARCH ticketIdx “@assetType:(BOND|IRD|FX|EQY|RSF|eMTN|Generic VC|Generic Margin|Securitization
|Call Account|Money Market)” SORTBY eventDateTime desc limit 0 100

1. “392673”
2. “ticket:jsbond:TRADEWEB_EUGV_EUGV_20230913_5769_20230913”
3. 1. “eventDateTime”
   2. “2023-09-13@09:36:24.287”

FT.SEARCH ticketIdx “@assetType:(BOND|IRD|FX|EQY|RSF|eMTN|Generic VC|Generic Margin|Securitization
|Call Account|Money Market)” SORTBY eventDateTime desc limit 0 1000

1. “362299”
2. “ticket:kdbfx:f4aaa082-fc4e-4185-ac7c-664df06e9632”
3. 1. “eventDateTime”
   2. “2023-09-13@04:22:58.741”

Here is the configuration and index details:
RediSearch v. 2.6.9
RedisJSON v. 2.4.7

FT.CONFIG GET *

1. 1. “EXTLOAD”
   2. “null”
2. 1. “SAFEMODE”
   2. “true”
3. 1. “CONCURRENT_WRITE_MODE”
   2. “false”
4. 1. “NOGC”
   2. “false”
5. 1. “MINPREFIX”
   2. “2”
6. 1. “FORKGC_SLEEP_BEFORE_EXIT”
   2. “0”
7. 1. “MAXDOCTABLESIZE”
   2. “2000000”
8. 1. “MAXSEARCHRESULTS”
   2. “10000”
9. 1. “MAXAGGREGATERESULTS”
   2. “10000”
10. 1. “MAXEXPANSIONS”
11. “200”
12. 1. “MAXPREFIXEXPANSIONS”
13. “200”
14. 1. “TIMEOUT”
15. “500”
16. 1. “INDEX_THREADS”
17. “8”
18. 1. “SEARCH_THREADS”
19. “20”
20. 1. “FRISOINI”
21. “null”
22. 1. “ON_TIMEOUT”
23. “return”
24. 1. “GCSCANSIZE”
25. “100”
26. 1. “MIN_PHONETIC_TERM_LEN”
27. “3”
28. 1. “GC_POLICY”
29. “fork”
30. 1. “FORK_GC_RUN_INTERVAL”
31. “30”
32. 1. “FORK_GC_CLEAN_THRESHOLD”
33. “100”
34. 1. “FORK_GC_RETRY_INTERVAL”
35. “5”
36. 1. “FORK_GC_CLEAN_NUMERIC_EMPTY_NODES”
37. “true”
38. 1. “_FORK_GC_CLEAN_NUMERIC_EMPTY_NODES”
39. “true”
40. 1. “_MAX_RESULTS_TO_UNSORTED_MODE”
41. “1000”
42. 1. “UNION_ITERATOR_HEAP”
43. “20”
44. 1. “CURSOR_MAX_IDLE”
45. “300000”
46. 1. “NO_MEM_POOLS”
47. “false”
48. 1. “PARTIAL_INDEXED_DOCS”
49. “false”
50. 1. “UPGRADE_INDEX”
51. “Upgrade config for upgrading”
52. 1. “_NUMERIC_COMPRESS”
53. “false”
54. 1. “_FREE_RESOURCE_ON_THREAD”
55. “true”
56. 1. “_PRINT_PROFILE_CLOCK”
57. “true”
58. 1. “RAW_DOCID_ENCODING”
59. “false”
60. 1. “_NUMERIC_RANGES_PARENTS”
61. “0”
62. 1. “DEFAULT_DIALECT”
63. “1”
64. 1. “VSS_MAX_RESIZE”
65. “0”
66. 1. “MULTI_TEXT_SLOP”
67. “100”

FT.INFO ticketIdx

1. “index_name”
2. “ticketIdx”
3. “index_options”
4. (empty list or set)
5. “index_definition”
6. 1. “key_type”
   2. “JSON”
   3. “prefixes”
   4. 1. “ticket:”
   5. “default_score”
   6. “1”
7. “attributes”
8. 1. 1. “identifier”
      2. “$.eventDateTime”
      3. “attribute”
      4. “eventDateTime”
      5. “type”
      6. “TEXT”
      7. “WEIGHT”
      8. “1”
      9. “SORTABLE”
      10. “UNF”
   2. 1. “identifier”
      2. “$.assetType”
      3. “attribute”
      4. “assetType”
      5. “type”
      6. “TEXT”
      7. “WEIGHT”
      8. “1”
   3. 1. “identifier”
      2. “$.salesPersonUt”
      3. “attribute”
      4. “salesPersonUt”
      5. “type”
      6. “TEXT”
      7. “WEIGHT”
      8. “1”
   4. 1. “identifier”
      2. “$.status”
      3. “attribute”
      4. “status”
      5. “type”
      6. “TEXT”
      7. “WEIGHT”
      8. “1”
9. “num_docs”
10. “387918”
11. “max_doc_id”
12. “994493”
13. “num_terms”
14. “1490”
15. “num_records”
16. “3629904”
17. “inverted_sz_mb”
18. “29.600536346435547”
19. “vector_index_sz_mb”
20. “0”
21. “total_inverted_index_blocks”
22. “943874”
23. “offset_vectors_sz_mb”
24. “10.037736892700195”
25. “doc_table_size_mb”
26. “42.581501007080078”
27. “sortable_values_size_mb”
28. “17.387529373168945”
29. “key_table_size_mb”
30. “14.226670265197754”
31. “records_per_doc_avg”
32. “9.3573999404907227”
33. “bytes_per_record_avg”
34. “8.5507526397705078”
35. “offsets_per_term_avg”
36. “2.8996167182922363”
37. “offset_bits_per_record_avg”
38. “8”
39. “hash_indexing_failures”
40. “8”
41. “total_indexing_time”
42. “60379.194000000003”
43. “indexing”
44. “0”
45. “percent_indexed”
46. “1”
47. “number_of_uses”
48. “775656”
49. “gc_stats”
50. 1. “bytes_collected”
51. “31444555”
52. “total_ms_run”
53. “1710673”
54. “total_cycles”
55. “3247”
56. “average_cycle_time_ms”
57. “526.84724360948564”
58. “last_run_time_ms”
59. “738”
60. “gc_numeric_trees_missed”
61. “0”
62. “gc_blocks_denied”
63. “16178”
64. “cursor_stats”
65. 1. “global_idle”
66. “0”
67. “global_total”
68. “0”
69. “index_capacity”
70. “128”
71. “index_total”
72. “0”
73. “dialect_stats”
74. 1. “dialect_1”
75. “1”
76. “dialect_2”
77. “0”
78. “dialect_3”
79. “0”

Best regards,

Xavier Appe

[xappe]

Well, just after writing this post, I found the issue, it was the timeout parameter set to 500ms which was preventing the search to finish properly.