FT.AGGREGATE troubleshooting

Redis modules RediSearch
1

Hello,
how would I go about troubleshooting the reason FT.AGGREGATE isn’t returning data when using a certain query:

For instance using (@CheckNumber:(2019*))" returns no results:

“FT.AGGREGATE” “checks_hash_index_1” “(@CheckFromID:{3062}) (@CheckNumber:(2019*))” “APPLY” “@CheckID” “AS” “CheckID” “APPLY” “@CheckDate” “AS” “CheckDate” “APPLY” “@CreatedDateTimeUTC” “AS” “CreatedDateTimeUTC” APPLY “@CheckNumber” AS “CheckNumber” “SORTBY” “6” “@CheckDate” “DESC” “@CreatedDateTimeUTC” “DESC” “@CheckID” “DESC”

``

whereas using (@CheckNumber:(201904*))" returns the correct ones:

“FT.AGGREGATE” “checks_hash_index_1” “(@CheckFromID:{3062}) (@CheckNumber:(201904*))” “APPLY” “@CheckID” “AS” “CheckID” “APPLY” “@CheckDate” “AS” “CheckDate” “APPLY” “@CreatedDateTimeUTC” “AS” “CreatedDateTimeUTC” APPLY “@CheckNumber” AS “CheckNumber” “SORTBY” “6” “@CheckDate” “DESC” “@CreatedDateTimeUTC” “DESC” “@CheckID” “DESC”

``

  1. (integer) 3

    1. CheckID

  3. “89825”

  4. CheckDate

  5. “6.36911424e+17”

  6. CreatedDateTimeUTC

  7. “6.36905194659e+17”

    1. CheckNumber

    2. “20190410\-3”

    1. CheckID

  9. “89824”

  10. CheckDate

  11. “6.36911424e+17”

  12. CreatedDateTimeUTC

  13. “6.36905194659e+17”

    1. CheckNumber

    2. “20190410\-2”

    1. CheckID

  15. “89823”

  16. CheckDate

  17. “6.36911424e+17”

  18. CreatedDateTimeUTC

  19. “6.36905194659e+17”

    1. CheckNumber

    2. “20190410\-1”

``

Here is part of the index, CheckNumber is TEXT SORTABLE:

ft.info checks_hash_index_1

  1. index_name

  2. checks_hash_index_1

  3. index_options

    1. “NOOFFSETS”

  5. fields

      1. CheckID

  7. type

  8. NUMERIC

  9. SORTABLE

    1. CheckNumber

  11. type

  12. TEXT

  13. WEIGHT

  14. “1”

  15. SORTABLE

``

Thank you!

2

This is probably because the MAXEXPANSIONS value is to low. You need to increase it. See configuration section for more info: https://oss.redislabs.com/redisearch/Configuring.html

3

I am using defaults for everything, and if I remove CheckFromID from the query string, I do get back some results:

“FT.SEARCH” “checks_hash_index_1” “(@CheckNumber:(2019*))” “APPLY” “@CheckID” “AS” “CheckID” “APPLY” “@CheckDate” “AS” “CheckDate” “APPLY” “@CreatedDateTimeUTC” “AS” “CreatedDateTimeUTC” APPLY “@CheckNumber” AS "CheckNumber

``

returns

  1. (integer) 201

  2. “99553”

    1. CheckID

  4. “99553”

  5. CheckDate

  6. “6.36941664e+17”

  7. CreatedDateTimeUTC

  8. “6.36941255821e+17”

    1. CheckNumber

    2. “20190522\-5173”

  9. “99552”

    1. CheckID

  11. “99552”

  12. CheckDate

  13. “6.36941664e+17”

  14. CreatedDateTimeUTC

  15. “6.36941255821e+17”

    1. CheckNumber

    2. “20190522\-5172”

…

``

4

Sorry that was FT.SEARCH, I was trying both, here is FT.AGGREGATE:

“FT.AGGREGATE” “checks_hash_index_1” “(@CheckNumber:(2019*))” “APPLY” “@CheckID” “AS” “CheckID” “APPLY” “@CheckDate” “AS” “CheckDate” “APPLY” “@CreatedDateTimeUTC” “AS” “CreatedDateTimeUTC” APPLY “@CheckNumber” AS “CheckNumber” “SORTBY” “6” “@CheckDate” “DESC” “@CreatedDateTimeUTC” “DESC” “@CheckID” “DESC”

``

  1. (integer) 201

    1. CheckID

  3. “99553”

  4. CheckDate

  5. “6.36941664e+17”

  6. CreatedDateTimeUTC

  7. “6.36941255821e+17”

  8. CheckNumber

  9. “20190522\-5173”

    1. CheckID

  11. “99552”

  12. CheckDate

  13. “6.36941664e+17”

  14. CreatedDateTimeUTC

  15. “6.36941255821e+17”

  16. CheckNumber

  17. “20190522\-5172”

…

``

5

This is just prove my assumption, increase the MAXEXPANSIONS and it will work.

6

127.0.0.1:6379> FT.CONFIG SET MAXEXPANSIONS 1000000

OK

127.0.0.1:6379> “FT.AGGREGATE” “checks_hash_index_1” “(@CheckFromID:{3062}) (@CheckNumber:(2019*))” “APPLY” “@CheckID” “AS” “CheckID” “APPLY” “@CheckDate” “AS” “CheckDate” “APPLY” “@CreatedDateTimeUTC” “AS” “CreatedDateTimeUTC” APPLY “@CheckNumber” AS “CheckNumber” “SORTBY” “6” “@CheckDate” “DESC” “@CreatedDateTimeUTC” “DESC” “@CheckID” “DESC”

  1. (integer) 0

127.0.0.1:6379> FT.CONFIG GET MAXEXPANSIONS

    1. MAXEXPANSIONS

  2. 1000000

``

Here is what I tried, I still get 0 results back:

7

My mistake, dropping the index, recreating it and re-adding the data worked!

What does MAXEXPANSIONS do, and what should I set it to, especially in our production environments since it comes with a disclaimer: “Setting it too high can cause performance issues.”?

8

MAXEXPANSIONS tells us how deep we should go inside the TRIE data-structure in order to find prefixes, the deeper we go the more time it takes. In your case if MAXEXPANSIONS is to low it does not mean that we did not find any match prefix but it means that from all the match prefixes found there was no prefix that also satisfy the second condition (@CheckFromID:{3062}). In order to find prefixes that does satisfy the second condition we needed to go deeper.

There is no clear rule of the value that you should set, it mostly depends on the number of TERMS you have in the TRIE (the more terms you the bigger you need to set the MAXEXPANSIONS).