aderm edited a comment on issue #4291: Improve ES query performance URL: https://github.com/apache/skywalking/issues/4291#issuecomment-580990054 Use index **service_inventory** as test data. Index mapping structure is unchanged. Constructed test document data totals 91002 and 255002. The test code is as follows: ` /** * @author aderm */ @Warmup(iterations = 5, time = 5, timeUnit = TimeUnit.SECONDS) @Measurement(iterations = 5, time = 5, timeUnit = TimeUnit.SECONDS) @State(Scope.Benchmark) public class FilterQueryTest { private RestHighLevelClient client; private final long startTimestamp = 1580287478790L; private final long endTimestamp = 1680480640000L; private final long MULTIPLE = 10000000L; private final int SEARCH_SIZE = 5000; private final String INDEX_NAME = "service_inventory"; @Setup public void createClient() { client = new RestHighLevelClient((RestClient.builder(new HttpHost("127.0.0.1", 9200, "http")))); } @TearDown public void closeClient() throws IOException { client.close(); } /** * Benchmark test fix es query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testFixEsQuery() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.must().add(timeRangeQueryBuild(startTimestamp, endTimestamp)); boolQueryBuilder.must().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.must().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(boolQueryBuilder); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test fix es filter query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testFixEsFilterQuery() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.must().add(timeRangeQueryBuild(startTimestamp, endTimestamp)); boolQueryBuilder.must().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.must().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(QueryBuilders.boolQuery().filter(boolQueryBuilder)); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test dynamic es query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testDynamicEsQuery() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); long ranFactor = (long) (Math.random() * MULTIPLE); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.must().add(timeRangeQueryBuild(startTimestamp + ranFactor, endTimestamp + ranFactor)); boolQueryBuilder.must().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.must().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(boolQueryBuilder); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test dynamic es filter query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testDynamicEsFilterQuery() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); long ranFactor = (long) (Math.random() * MULTIPLE); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.must().add(timeRangeQueryBuild(startTimestamp + ranFactor, endTimestamp + ranFactor)); boolQueryBuilder.must().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.must().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(QueryBuilders.boolQuery().filter(boolQueryBuilder)); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); client.search(searchRequest, RequestOptions.DEFAULT); } public static void main(String[] args) throws RunnerException, IOException { Options opt = new OptionsBuilder() .include(FilterQueryTest.class.getSimpleName()) .forks(1) .build(); new Runner(opt).run(); } private BoolQueryBuilder timeRangeQueryBuild(long startTimestamp, long endTimestamp) { BoolQueryBuilder boolQuery1 = QueryBuilders.boolQuery(); boolQuery1.must().add(QueryBuilders.rangeQuery("heartbeat_time").gte(endTimestamp)); boolQuery1.must().add(QueryBuilders.rangeQuery("register_time").lte(endTimestamp)); BoolQueryBuilder boolQuery2 = QueryBuilders.boolQuery(); boolQuery2.must().add(QueryBuilders.rangeQuery("register_time").lte(endTimestamp)); boolQuery2.must().add(QueryBuilders.rangeQuery("heartbeat_time").gte(startTimestamp)); BoolQueryBuilder timeBoolQuery = QueryBuilders.boolQuery(); timeBoolQuery.should().add(boolQuery1); timeBoolQuery.should().add(boolQuery2); return timeBoolQuery; } } ` ### total 91002 <img width="188" alt="WX20200201-113442@2x" src="https://user-images.githubusercontent.com/2892433/73586765-d4b7b000-44ec-11ea-9130-56fbd5eb728c.png";> Benchmark | Mode | Cnt | Score | Error | Units | --- | --- | --- | --- | --- | --- | FilterQueryTest.testDynamicEsFilterQuery | avgt | 5 | 94588.959 | ± 19644.516 | us/op FilterQueryTest.testDynamicEsQuery | avgt | 5 | 101178.972 | ± 12495.434 | us/op FilterQueryTest.testFixEsFilterQuery | avgt | 5 | 93911.966 | ± 6468.336 | us/op FilterQueryTest.testFixEsQuery | avgt | 5 | 98692.350 | ± 2927.478 | us/op ### total 255002 <img width="212" alt="WX20200201-121350@2x" src="https://user-images.githubusercontent.com/2892433/73586768-de411800-44ec-11ea-8d83-b4c5ea6da3ad.png";> Benchmark | Mode | Cnt | Score | Error | Units | --- | --- | --- | --- | --- | --- | FilterQueryTest.testDynamicEsFilterQuery | avgt | 5 | 99628.544 | ± 10410.227 | us/op FilterQueryTest.testDynamicEsQuery | avgt | 5 | 120000.360 | ± 49276.525 | us/op FilterQueryTest.testFixEsFilterQuery | avgt | 5 | 102391.063 | ± 29189.827 | us/op FilterQueryTest.testFixEsQuery | avgt | 5 | 116538.992 | ± 10316.892 | us/op It can be seen from the test results that the improvement result is relatively obvious, especially in dynamic data scenarios, the larger the total number of indexes, the more obvious the improvement effect. filter concept refer [https://www.elastic.co/guide/en/elasticsearch/reference/7.x/query-filter-context.html](url)
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
![https://user-images.githubusercontent.com/2892433/73586765-d4b7b000-44ec-11ea-9130-56fbd5eb728c.png"](https://user-images.githubusercontent.com/2892433/73586765-d4b7b000-44ec-11ea-9130-56fbd5eb728c.png"){kind=link}
![https://user-images.githubusercontent.com/2892433/73586768-de411800-44ec-11ea-8d83-b4c5ea6da3ad.png"](https://user-images.githubusercontent.com/2892433/73586768-de411800-44ec-11ea-8d83-b4c5ea6da3ad.png"){kind=link}