FT.AGGREGATE performance problems

[Paul]

Hi,
We are using Redis Enterprise Cloud, and switching to FT.AGGREGATE from FT.SEARCH has introduced significant performance problems - our instance is using Redis 5.0.4/ Redisearch 1.4.6.

Here is a query that takes 3 seconds to return:

“FT.AGGREGATE” “trips_hash_index” “(@VisibleToCompanyIDs:{2}) (@TripMainTypeID:{6|4|1|3|2}) (@TripStatusID:{1|5|4|6|3})” “SORTBY” “8” “@CreatedDateTimeUTC” “DESC” “@TripStartDate” “ASC” “@TripEndDate” “ASC” “@TripID” “DESC” “LIMIT” “0” “10”

``

with FT.SEARCH it is quite quick:

“FT.SEARCH” “trips_hash_index” “(@VisibleToCompanyIDs:{2}) (@TripMainTypeID:{6|4|1|3|2}) (@TripStatusID:{1|5|4|6|3})” “SORTBY” “TripID” “DESC” “LIMIT” “0” “10”

``

And here is what the index looks like:

ft.info trips_hash_index

1. index_name

2. “trips_hash_index”

3. fields

4. 1. 1. PrimaryKey

5. type

6. TEXT

7. WEIGHT

8. “1”

9. SORTABLE

10. 1. TripID

11. type

12. NUMERIC

13. SORTABLE

14. 1. UserID

15. type

16. TAG

17. SEPARATOR

18. ,

19. 1. TripDescription

20. type

21. TEXT

22. WEIGHT

23. “1”

24. 1. TripStartDate

25. type

26. NUMERIC

27. SORTABLE

28. 1. TripEndDate

29. type

30. NUMERIC

31. SORTABLE

32. 1. TripTypeID

33. type

34. TAG

35. SEPARATOR

36. ,

37. 1. TripMainTypeID

38. type

39. TAG

40. SEPARATOR

41. ,

42. 1. TripStatusID

43. type

44. TAG

45. SEPARATOR

46. ,

47. 1. CreatedDateTimeUTC

48. type

49. NUMERIC

50. SORTABLE

51. 1. VisibleToCompanyIDs

52. type

53. TAG

54. SEPARATOR

55. ,

56. 1. ParentTripID

57. type

58. TAG

59. SEPARATOR

60. ,

61. 1. Supplier

62. type

63. TAG

64. SEPARATOR

65. ,

66. 1. TripCarrierTypeID

67. type

68. TAG

69. SEPARATOR

70. ,

71. 1. ClientIDs

72. type

73. TAG

74. SEPARATOR

75. ,

76. index_options

77. 1. “NOOFFSETS”

78. gc_stats

79. 1. current_hz

80. “3.0289804935455322”

81. bytes_collected

82. (integer) 68723

83. effectiv_cycles_rate

84. “0.036071268224674498”

85. cursor_stats

86. 1. global_idle

87. (integer) 0

88. global_total

89. (integer) 0

90. index_capacity

91. (integer) 128

92. index_total

93. (integer) 0

94. num_docs

95. (integer) 599715

96. max_doc_id

97. (integer) 600432

98. num_terms

99. (integer) 878752

100. num_records

101. (integer) 4937876

102. inverted_sz_mb

103. “4.6262814823090749e+18”

104. offset_vectors_sz_mb

105. “0”

106. doc_table_size_mb

107. “4.6329217579703337e+18”

108. key_table_size_mb

109. “4.6255695592675082e+18”

110. records_per_doc_avg

111. “8.2337043428962087”

112. bytes_per_record_avg

113. “4.2159641108849231”

114. offsets_per_term_avg

115. “0”

116. offset_bits_per_record_avg

117. “nan”

``

Any thoughts on what the problem might be?

Thanks!

[Filipe_C_Oliveira]

Hi there Paul, specifically focusing on FT.AGGREGATE performance recommendations I would recommend:

• using the MAX parameter within your sortby clauses: SORTBY … MAX , since MAX is used to optimized sorting, by sorting only for the n-largest elements. https://oss.redislabs.com/redisearch/Aggregations/#parameters_in_detail
• not using LIMIT in favor of using the cursor api, with the WITHCURSOR keyword, since cursors allow you to consume only part of the response, allowing you to fetch additional results as needed. This is much quicker than using LIMIT with offset, since the query is executed only once, and its state is stored on the server. https://oss.redislabs.com/redisearch/Aggregations/#cursor_api

Can you give it a try and let us know if that solved the issue?

[Paul]

Hi Filipe,
I am actually using MAX, but did not include it in the query - the difference between using it and not using is very negligible.

Secondly, CURSORS wouldn’t be acceptable in my case because the data changes all the time, so going from one page to the next could have different records because some may have got added, other deleted, other modified.

Do you have any other suggestions? What would be the reason search is so much faster? The only reason I need aggregate is to be able to sort by multiple fields? Is there a different way to accomplish this with search?

[Filipe_C_Oliveira]

Hi again Paul, given that using the cursor API is not a possible solution here, I think that we should investigate further why the MAX parameter on SORTBY is not having the expected effect on performance improvement ( given that the query is the same on FT.AGGREGATE and FT.SEARCH ). With that in mind, is it possible for you to create an issue on:
https://github.com/RediSearch/RediSearch/issues
with what you’ve described here, + recreation instruction, and ( if possible ) an rdb so that we can test this for you and provide a solution? The more info we have the faster we can provide an explanation/solution.

[Paul]

Hi Filipe,
I created this issue: https://github.com/RediSearch/RediSearch/issues/1016

How would I go about giving you access to our QA environment where you could troubleshoot yourself? There are only about 165k records in that index, but FT.AGGREGATE still takes about a second to return with or without MAX.

Thanks for your help.