# Performance of term query with sorting

**URL:** <https://discuss.elastic.co/t/performance-of-term-query-with-sorting/11448>\
**Category:** Elasticsearch\
**Created:** [April 4, 2013, 10:42am UTC](https://discuss.elastic.co/t/performance-of-term-query-with-sorting/11448 "2013-04-04T10:42:43Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ankit\_Jain](https://avatars.discourse-cdn.com/v4/letter/a/96bed5/32.png) [@Ankit\_Jain](https://discuss.elastic.co/u/Ankit_Jain)\
**Post date:** [April 4, 2013, 10:42am UTC](https://discuss.elastic.co/t/performance-of-term-query-with-sorting/11448/1 "2013-04-04T10:42:43Z")

</div>

Hi All,

we are using 0.20.2 version of ES and running five nodes of ES each has 32  
GB RAM and 8 cores.

we have indexed 60 millions(100 GB data) records into ES. We need to fire a  
term query with sorting.

If we are firing term query without sorting, then result are coming in 3  
secs ,but , a term query with sorting takes around 150 secs.  
Please somebody could explain the Mechanism the elasticsearch follows while  
performing the query(with and without sort both) ?

we have already optimized the index via external call.

Also , please suggest some optimization parameters.

Thanks,  
Ankit

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**Author:** ![q42jaap](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/q42jaap/32/2241_2.png) [@q42jaap](https://discuss.elastic.co/u/q42jaap)\
**Post date:** [April 4, 2013, 11:30am UTC](https://discuss.elastic.co/t/performance-of-term-query-with-sorting/11448/2 "2013-04-04T11:30:52Z")

</div>

Ankit,

Could you please give insight in the layout of your data and what the query  
looks like?  
How big is the resultset you're hitting with the query, total\_hits.

For more insight into how querying is done, have a look at:

> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.

I believe the default search type is query\_then\_fetch, however this is  
missing in the documentation.

Jaap Taal

[Q42 BV | tel 070 44523 42 | direct 070 44523 65 | [http://q42.nl](http://q42.nl) |  
Waldorpstraat 17F, Den Haag | Vijzelstraat 72 unit 4.23, Amsterdam | KvK  
30164662 ]

On Thu, Apr 4, 2013 at 12:42 PM, Ankit Jain [ankitjaincs06@gmail.com](mailto:ankitjaincs06@gmail.com) wrote:

> Hi All,
> 
> we are using 0.20.2 version of ES and running five nodes of ES each has 32  
> GB RAM and 8 cores.
> 
> we have indexed 60 millions(100 GB data) records into ES. We need to fire  
> a term query with sorting.
> 
> If we are firing term query without sorting, then result are coming in 3  
> secs ,but , a term query with sorting takes around 150 secs.  
> Please somebody could explain the Mechanism the elasticsearch follows  
> while performing the query(with and without sort both) ?
> 
> we have already optimized the index via external call.
> 
> Also , please suggest some optimization parameters.
> 
> Thanks,  
> Ankit
> 
> --  
> You received this message because you are subscribed to the Google Groups  
> "elasticsearch" group.  
> To unsubscribe from this group and stop receiving emails from it, send an  
> email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
> For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**Author:** ![Ankit\_Jain](https://avatars.discourse-cdn.com/v4/letter/a/96bed5/32.png) [@Ankit\_Jain](https://discuss.elastic.co/u/Ankit_Jain)\
**Post date:** [April 4, 2013, 12:33pm UTC](https://discuss.elastic.co/t/performance-of-term-query-with-sorting/11448/3 "2013-04-04T12:33:37Z")

</div>

Hi Jaap,

we have indexed 60 millions records, each record contains 31  
columns(rowId,c0,c1,c2,c3..,c29).

Below is our index mapping:

{"ipdr":{"\_source":{"enabled":false},"properties":{"c0":{"type":"long","store":"yes"},"c1":{"type":"string"},"c11":{"type":"string"},"c12":{"type":"string"},"c13":{"type":"string"},"c14":{"type":"string"},"c15":{"type":"string"},"c16":{"type":"string"},"c17":{"type":"string"},"c18":{"type":"string"},"c19":{"type":"string"},"c2":{"type":"string"},"c20":{"type":"string"},"c21":{"type":"string"},"c22":{"type":"string"},"c23":{"type":"string"},"c24":{"type":"string"},"c25":{"type":"string"},"c26":{"type":"string"},"c27":{"type":"string"},"c28":{"type":"string"},"c29":{"type":"string"},"c3":{"type":"string"},"c30":{"type":"string"},"c4":{"type":"string"},"c5":{"type":"string"},"c6":{"type":"string"},"c7":{"type":"string"},"c8":{"type":"string"},"c9":{"type":"string"},"rowId":{"type":"string","index":"no","store":"yes"}}}}

Below is sample example that we are using to retrieve 10000 records from elasticsearch.

```
public void SearchQuery() {
	QueryBuilder qb = QueryBuilders.matchAllQuery();

	QueryBuilder queryBuilder = QueryBuilders.boolQuery()
			.must(termQuery("c29", "udp"))
			;
	
	
	SearchRequestBuilder searchRequestBuilder = client
			.prepareSearch("89854","89855","89853")
			.setSearchType(SearchType.QUERY_AND_FETCH)
			.setQuery(queryBuilder)
			.setSize(10000);
	searchRequestBuilder.addSort("c0", SortOrder.DESC);
	SearchResponse response = searchRequestBuilder.execute().actionGet();
	SearchHits hits = response.getHits();
	System.out.println("Total Hits : "+hits.getTotalHits()); // output is 2
	int i = 0;
	for (SearchHit hit : hits) {
		System.out.println("id = " + hit.getId() + ""+i++); // prints out the id of the
	}

	long now = System.currentTimeMillis();
	long diff = now - start;
	Calendar cal = Calendar.getInstance();
	SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);

	System.out.println("Time Taken in millisecs = "
			+ new Long(diff).toString());
	System.out.println("Done");
}

```

Thanks & Regards,  
Ankit Jain

On Thursday, 4 April 2013 17:00:52 UTC+5:30, Jaap Taal wrote:

> Ankit,
> 
> Could you please give insight in the layout of your data and what the  
> query looks like?  
> How big is the resultset you're hitting with the query, total\_hits.
> 
> For more insight into how querying is done, have a look at:  
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/search-type/)
> 
> I believe the default search type is query\_then\_fetch, however this is  
> missing in the documentation.
> 
> Jaap Taal
> 
> [Q42 BV | tel 070 44523 42 | direct 070 44523 65 | [http://q42.nl](http://q42.nl) |  
> Waldorpstraat 17F, Den Haag | Vijzelstraat 72 unit 4.23, Amsterdam | KvK  
> 30164662 ]
> 
> On Thu, Apr 4, 2013 at 12:42 PM, Ankit Jain \<[ankitj...@gmail.com](mailto:ankitj...@gmail.com)\<javascript:\>
> 
> > wrote:
> 
> > Hi All,
> > 
> > we are using 0.20.2 version of ES and running five nodes of ES each has  
> > 32 GB RAM and 8 cores.
> > 
> > we have indexed 60 millions(100 GB data) records into ES. We need to fire  
> > a term query with sorting.
> > 
> > If we are firing term query without sorting, then result are coming in 3  
> > secs ,but , a term query with sorting takes around 150 secs.  
> > Please somebody could explain the Mechanism the elasticsearch follows  
> > while performing the query(with and without sort both) ?
> > 
> > we have already optimized the index via external call.
> > 
> > Also , please suggest some optimization parameters.
> > 
> > Thanks,  
> > Ankit
> > 
> > --  
> > You received this message because you are subscribed to the Google Groups  
> > "elasticsearch" group.  
> > To unsubscribe from this group and stop receiving emails from it, send an  
> > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \<javascript:\>.  
> > For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [April 4, 2013, 1:06pm UTC](https://discuss.elastic.co/t/performance-of-term-query-with-sorting/11448/4 "2013-04-04T13:06:31Z")

</div>

If you need to extract so much records, prefer the scan & scroll feature.

> **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)**
>
> Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today.

You probably don't need to display so mush results to a single user, do you?

That said, when sorting, ES has to load all values from your field c0 and sort them on each shard. Then, sort the resultset again on the gathering node.  
It could explain things here if c0 has 60 000 000 different values!

--  
David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com)  
@dadoonet | @elasticsearchfr | @scrutmydocs

Le 4 avr. 2013 à 14:33, Ankit Jain [ankitjaincs06@gmail.com](mailto:ankitjaincs06@gmail.com) a écrit :

> Hi Jaap,
> 
> we have indexed 60 millions records, each record contains 31 columns(rowId,c0,c1,c2,c3..,c29).
> 
> Below is our index mapping:
> 
> {"ipdr":{"\_source":{"enabled":false},"properties":{"c0":{"type":"long","store":"yes"},"c1":{"type":"string"},"c11":{"type":"string"},"c12":{"type":"string"},"c13":{"type":"string"},"c14":{"type":"string"},"c15":{"type":"string"},"c16":{"type":"string"},"c17":{"type":"string"},"c18":{"type":"string"},"c19":{"type":"string"},"c2":{"type":"string"},"c20":{"type":"string"},"c21":{"type":"string"},"c22":{"type":"string"},"c23":{"type":"string"},"c24":{"type":"string"},"c25":{"type":"string"},"c26":{"type":"string"},"c27":{"type":"string"},"c28":{"type":"string"},"c29":{"type":"string"},"c3":{"type":"string"},"c30":{"type":"string"},"c4":{"type":"string"},"c5":{"type":"string"},"c6":{"type":"string"},"c7":{"type":"string"},"c8":{"type":"string"},"c9":{"type":"string"},"rowId":{"type":"string","index":"no","store":"yes"}}}}
> 
> Below is sample example that we are using to retrieve 10000 records from elasticsearch.
> 
> public void SearchQuery() {  
> QueryBuilder qb = QueryBuilders.matchAllQuery();
> 
> ```
> QueryBuilder queryBuilder = QueryBuilders.boolQuery()
> .must(termQuery("c29", "udp"))
> ;
>   
>   
> SearchRequestBuilder searchRequestBuilder = client
> .prepareSearch("89854","89855","89853")
> .setSearchType(SearchType.QUERY_AND_FETCH)
> .setQuery(queryBuilder)
> .setSize(10000);
> searchRequestBuilder.addSort("c0", SortOrder.DESC);
> SearchResponse response = searchRequestBuilder.execute().actionGet();
> SearchHits hits = response.getHits();
> System.out.println("Total Hits : "+hits.getTotalHits()); // output is 2
> int i = 0;
> for (SearchHit hit : hits) {
> System.out.println("id = " + hit.getId() + ""+i++); // prints out the id of the
> }
> 
> long now = System.currentTimeMillis();
> long diff = now - start;
> Calendar cal = Calendar.getInstance();
> SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_NOW);
> 
> System.out.println("Time Taken in millisecs = "
> + new Long(diff).toString());
> System.out.println("Done");
> 
> ```
> 
> }
> 
> Thanks & Regards,  
> Ankit Jain
> 
> On Thursday, 4 April 2013 17:00:52 UTC+5:30, Jaap Taal wrote:  
> Ankit,
> 
> Could you please give insight in the layout of your data and what the query looks like?  
> How big is the resultset you're hitting with the query, total\_hits.
> 
> For more insight into how querying is done, have a look at:  
> [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/search-type/)
> 
> I believe the default search type is query\_then\_fetch, however this is missing in the documentation.
> 
> Jaap Taal
> 
> [Q42 BV | tel 070 44523 42 | direct 070 44523 65 | [http://q42.nl](http://q42.nl) | Waldorpstraat 17F, Den Haag | Vijzelstraat 72 unit 4.23, Amsterdam | KvK 30164662 ]
> 
> On Thu, Apr 4, 2013 at 12:42 PM, Ankit Jain [ankitj...@gmail.com](mailto:ankitj...@gmail.com) wrote:  
> Hi All,
> 
> we are using 0.20.2 version of ES and running five nodes of ES each has 32 GB RAM and 8 cores.
> 
> we have indexed 60 millions(100 GB data) records into ES. We need to fire a term query with sorting.
> 
> If we are firing term query without sorting, then result are coming in 3 secs ,but , a term query with sorting takes around 150 secs.  
> Please somebody could explain the Mechanism the elasticsearch follows while performing the query(with and without sort both) ?
> 
> we have already optimized the index via external call.
> 
> Also , please suggest some optimization parameters.
> 
> Thanks,  
> Ankit
> 
> --  
> You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
> To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com).  
> For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).
> 
> --  
> You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
> To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
> For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
For more options, visit [https://groups.google.com/groups/opt\_out](https://groups.google.com/groups/opt_out).

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 2:42am UTC](https://discuss.elastic.co/t/performance-of-term-query-with-sorting/11448/5 "2017-07-06T02:42:47Z")

</div>