# Very slow queries for parent-child index

**URL:** <https://discuss.elastic.co/t/very-slow-queries-for-parent-child-index/22754>\
**Category:** Elasticsearch\
**Created:** [March 19, 2015, 6:53am UTC](https://discuss.elastic.co/t/very-slow-queries-for-parent-child-index/22754 "2015-03-19T06:53:28Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Vladi\_Feigin](https://avatars.discourse-cdn.com/v4/letter/v/258eb7/32.png) [@Vladi\_Feigin](https://discuss.elastic.co/u/Vladi_Feigin)\
**Post date:** [March 19, 2015, 6:53am UTC](https://discuss.elastic.co/t/very-slow-queries-for-parent-child-index/22754/1 "2015-03-19T06:53:28Z")

</div>

Hello,

I would like to ask for advice about our index that is built on the  
principle of Parent Child,  
Now a search word takes a very long time about 3 minutes.  
Below is the index structure.

Our database (shown below the index schema) contains information about  
purchases and reviews in different stores.  
Parent contains meta data of purchase and the children contain comments  
regarding a purchase and names of goods (products).  
Important to note that the search will always be performed for only one  
shop (for a particular store\_id). Although we have only one single index,  
no partitions so all stores are in this index

At the moment the query to find the parent whose children have the word  
takes up to a few minutes .....

Information on cluster and index:  
Number of documents 2.7 billion, the size of 360 gigs  
9 shards, one replica (of 18)  
6 physical nodes

Your help is highly appreciated

Vlad Feigin

The index structure is :

{  
"Purchase": {  
"mappings": {  
"_default_": {  
"dynamic": "false",  
"\_all": {  
"enabled": false  
},  
"\_ttl": {  
"enabled": true,  
"default": 34560000000  
},  
"\_source": {  
"enabled": false  
},  
"properties": {

```
    }
  },

  "Parent": {
    "dynamic": "false",
    "_all": {
      "enabled": false
    },
    "_ttl": {
      "enabled": true,
      "default": 34560000000
    },
    "properties": {
      "store_id": {
        "type": "string",
        "index": "not_analyzed",
        "store": true
      },
      "endTime": {
        "type": "long",
        "store": true
      },
      "startTime": {
        "type": "long",
        "store": true
      },
      "purchaseId": {
        "type": "string",
        "index": "not_analyzed",
        "store": true
      }
    }
  },
  "comments": {
    "dynamic": "false",
    "_all": {
      "enabled": false
    },
    "_parent": {
      "type": "Parent"
    },
    "_routing": {
      "required": true
    },
    "_ttl": {
      "enabled": true,
      "default": 34560000000
    },
    "_source": {
      "enabled": false
    },
    "properties": {
      "text": {
        "type": "string"
      }
    }
  },

```

"products": {  
"dynamic": "false",  
"\_all": {  
"enabled": false  
},  
"\_parent": {  
"type": "Parent"  
},  
"\_routing": {  
"required": true  
},  
"\_ttl": {  
"enabled": true,  
"default": 34560000000  
},  
"\_source": {  
"enabled": false  
},  
"properties": {  
"name": {  
"type": "string"  
}  
}  
}  
}  
}  
}

--  
This message may contain confidential and/or privileged information.  
If you are not the addressee or authorized to receive this on behalf of the  
addressee you must not use, copy, disclose or take action based on this  
message or any information herein.  
If you have received this message in error, please advise the sender  
immediately by reply email and delete this message. Thank you.

--  
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).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/9cae55d5-c702-4e82-ad86-c90f674b8a6b%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/9cae55d5-c702-4e82-ad86-c90f674b8a6b%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Chen\_Wang](https://avatars.discourse-cdn.com/v4/letter/c/b5a626/32.png) [@Chen\_Wang](https://discuss.elastic.co/u/Chen_Wang)\
**Post date:** [March 21, 2015, 12:13am UTC](https://discuss.elastic.co/t/very-slow-queries-for-parent-child-index/22754/2 "2015-03-21T00:13:14Z")

</div>

Vlad,  
I tried similar thing a while back. Of cause the query performance depends  
on your ES configuration as well.  
But I finally ended up giving up on parent/child, and flat the parent/child  
document into nested object as the parent/child never gives me the  
performance I need.  
Chen

On Wednesday, March 18, 2015 at 11:53:28 PM UTC-7, Vladi Feigin wrote:

> Hello,
> 
> I would like to ask for advice about our index that is built on the  
> principle of Parent Child,  
> Now a search word takes a very long time about 3 minutes.  
> Below is the index structure.
> 
> Our database (shown below the index schema) contains information about  
> purchases and reviews in different stores.  
> Parent contains meta data of purchase and the children contain comments  
> regarding a purchase and names of goods (products).  
> Important to note that the search will always be performed for only one  
> shop (for a particular store\_id). Although we have only one single index,  
> no partitions so all stores are in this index
> 
> At the moment the query to find the parent whose children have the word  
> takes up to a few minutes .....
> 
> Information on cluster and index:  
> Number of documents 2.7 billion, the size of 360 gigs  
> 9 shards, one replica (of 18)  
> 6 physical nodes
> 
> Your help is highly appreciated
> 
> Vlad Feigin
> 
> The index structure is :
> 
> {  
> "Purchase": {  
> "mappings": {  
> "_default_": {  
> "dynamic": "false",  
> "\_all": {  
> "enabled": false  
> },  
> "\_ttl": {  
> "enabled": true,  
> "default": 34560000000  
> },  
> "\_source": {  
> "enabled": false  
> },  
> "properties": {
> 
> ```
> }
> },
> 
> "Parent": {
> "dynamic": "false",
> "_all": {
> "enabled": false
> },
> "_ttl": {
> "enabled": true,
> "default": 34560000000
> },
> "properties": {
> "store_id": {
> "type": "string",
> "index": "not_analyzed",
> "store": true
> },
> "endTime": {
> "type": "long",
> "store": true
> },
> "startTime": {
> "type": "long",
> "store": true
> },
> "purchaseId": {
> "type": "string",
> "index": "not_analyzed",
> "store": true
> }
> }
> },
> "comments": {
> "dynamic": "false",
> "_all": {
> "enabled": false
> },
> "_parent": {
> "type": "Parent"
> },
> "_routing": {
> "required": true
> },
> "_ttl": {
> "enabled": true,
> "default": 34560000000
> },
> "_source": {
> "enabled": false
> },
> "properties": {
> "text": {
> "type": "string"
> }
> }
> },
> 
> ```
> 
> "products": {  
> "dynamic": "false",  
> "\_all": {  
> "enabled": false  
> },  
> "\_parent": {  
> "type": "Parent"  
> },  
> "\_routing": {  
> "required": true  
> },  
> "\_ttl": {  
> "enabled": true,  
> "default": 34560000000  
> },  
> "\_source": {  
> "enabled": false  
> },  
> "properties": {  
> "name": {  
> "type": "string"  
> }  
> }  
> }  
> }  
> }  
> }
> 
> This message may contain confidential and/or privileged information.  
> If you are not the addressee or authorized to receive this on behalf of  
> the addressee you must not use, copy, disclose or take action based on this  
> message or any information herein.  
> If you have received this message in error, please advise the sender  
> immediately by reply email and delete this message. Thank you.

--  
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).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/2e6cd473-3b6f-448f-bbf3-0b37c6a6d320%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/2e6cd473-3b6f-448f-bbf3-0b37c6a6d320%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<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, 12:25am UTC](https://discuss.elastic.co/t/very-slow-queries-for-parent-child-index/22754/3 "2017-07-06T00:25:16Z")

</div>