Systematic Approach to NoSQL Indexing: Validating the ESR (Equality, Sort, Range) Heuristic for Query Performance - Narut Butploy; Kanokwan Khiewwan; Jindaporn Ongate; Thep Kueathaweekun; Pakin Maneechot (IAES Indonesian Section, 2026)
In this paper we describe the ESR heuristic, a simple and repeatable way to build compound indexes in MongoDB. The method prioritizes the query requirements in order of equality, sort, and range. The ESR heuristic can boost the query performance without the need for a complicated machine learning model. The experiment was conducted on four real-world datasets and two kinds of mixed queries on Equality, Sort, and Range (ESR). The results show that the systematic technique for compound indexing based on the ESR principle reduces the mean query latency in most cases. For example, the Online Retail dataset had a clear improvement with p-values less than 0.01 in both Welch’s t-test and the Mann-Whitney U-test. For the NYC Taxi dataset, the non-parametric test indicated a significant change in the latency distribution (p=0.032), implying that many queries have decreased tail latency. The Abalone dataset indicated a favorable trend, but it was not very significant statistically . The filter condition had low selectivity, hence only a little improvement was observed in the Bank Marketing dataset. The experiment also highlighted the COLLSCAN contradiction where MongoDB still scanned the entire collection with a proper compound index. However, the ESR heuristic decreased variance and tail latency in most circumstances.