# Optimisation d'une requete

**URL:** <https://discuss.elastic.co/t/optimisation-dune-requete/271489>\
**Category:** Discussions en français\
**Created:** [April 28, 2021, 9:20am UTC](https://discuss.elastic.co/t/optimisation-dune-requete/271489 "2021-04-28T09:20:56Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![fredo33](https://avatars.discourse-cdn.com/v4/letter/f/cdc98d/32.png) [@fredo33](https://discuss.elastic.co/u/fredo33)\
**Post date:** [April 28, 2021, 9:20am UTC](https://discuss.elastic.co/t/optimisation-dune-requete/271489/1 "2021-04-28T09:20:56Z")

</div>

Bonjour , je cherche à optimiser cette requête avez vous une idée , je cherche à afficher toutes les adresses qui contiennent le libelle 61 rue du paradis avec comme option de recherche : AND et OR

{  
"from": 0,  
"size": 30,  
"query": {  
"query\_string": {  
"query": "(chainerecherche:/._61._/ )AND( chainerecherche:/._RUE._/ )AND( chainerecherche:/._DU._/ )AND( chainerecherche:/._PARADIS._/) AND (type: ligne3 OR type: voie OR type: commune OR type: cedex ) AND actif:1 AND flag\_diffusable:1",  
"fields": ,  
"type": "best\_fields",  
"default\_operator": "and",  
"max\_determinized\_states": 10000,  
"enable\_position\_increments": true,  
"fuzziness": "AUTO",  
"fuzzy\_prefix\_length": 0,  
"fuzzy\_max\_expansions": 50,  
"phrase\_slop": 0,  
"escape": false,  
"auto\_generate\_synonyms\_phrase\_query": true,  
"fuzzy\_transpositions": true,  
"boost": 1  
}  
},  
"version": true,  
"track\_scores": true,  
"highlight": {  
"pre\_tags": [  
"_"  
],  
"post\_tags": [  
"_"  
],  
"require\_field\_match": false,  
"fields": {  
"ligne3": {},  
"numero": {},  
"ext\_courte": {},  
"ext\_longue": {},  
"libelle\_voie": {},  
"libelle\_voie\_syn": {},  
"libelle\_ligne\_5": {},  
"libelle\_acheminement": {},  
"code\_postal": {}  
}  
}  
}

---

<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 28, 2021, 10:10am UTC](https://discuss.elastic.co/t/optimisation-dune-requete/271489/2 "2021-04-28T10:10:03Z")

</div>

Welcome!

Peux-tu STP (prettify) formatter ton code pour le rendre plus lisible?  
Il y a un bouton `</>` pour aussi mettre en forme ce qui concerne le code, les logs...

Y a t'il une raison particulière pour laquelle tu veux utiliser `query_string` plutôt que de faire des `bool` queries?

Dans un de [mes projets démo](https://speaker.pilato.fr/UAqIx5/enriching-postal-addresses-with-elastic-stack), je fais par exemple:

```auto
GET bano-*/_search
{
  "size": 1, 
  "query": {
    "multi_match": {
      "query": "6 allée des myrtilles cergy",
      "fields": [
        "address.city",
        "address.street_name",
        "address.number"
      ],
      "type": "most_fields"
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![fredo33](https://avatars.discourse-cdn.com/v4/letter/f/cdc98d/32.png) [@fredo33](https://discuss.elastic.co/u/fredo33)\
**Post date:** [April 28, 2021, 11:33am UTC](https://discuss.elastic.co/t/optimisation-dune-requete/271489/3 "2021-04-28T11:33:54Z")

</div>

Bonjour , merci pour ta réponse, aucune raison particulière ,si plutot j'ai repris des requetes SOLR que j 'ai migré sous Elastic, à vrai dire , je ne connais pas trop le bool query , qu'est ce que ça apporte de plus ? comment je pourrais construire ma requête si j'utilise le "bool queries" ? est ce que tu pourrais à partir de ma requete me proposer une implémentation ? Merci encore

---

<div class="post-metadata">

**Author:** ![fredo33](https://avatars.discourse-cdn.com/v4/letter/f/cdc98d/32.png) [@fredo33](https://discuss.elastic.co/u/fredo33)\
**Post date:** [April 28, 2021, 12:17pm UTC](https://discuss.elastic.co/t/optimisation-dune-requete/271489/4 "2021-04-28T12:17:14Z")

</div>

qu'est ce que tu en penses de cette solution :

autres questions : comment je peux rajouter un tri dans ma requête , j'ai voulu rajouter des étoiles \* comme ceci : "query": "_RUE_" pour faire une recherche étendue avec la valeur '\*' , malheureusement cela ne retourne pas plus de résultat , tu as une idée !

```
   GET /s7_sint_ran/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "query": "61",
            "type": "cross_fields",
            "fields": [
              "libelle_commune",
              "libelle_acheminement",
              "libelle_ligne_5",
              "libelle_pays",
              "libelle_pays_syn",
              "libelle_voie",
              "libelle_voie_syn",
              "ext_courte",
              "ext_longue",
              "ligne3",
              "ligne2",
              "ligne1",
              "libelle_acheminement_cedex",
              "code_cedex",
              "code_postal",
              "numero_str"
            ],
            "operator": "or"
          }
        },
        {
          "multi_match": {
            "query": "RUE",
            "type": "cross_fields",
            "fields": [
              "libelle_commune",
              "libelle_acheminement",
              "libelle_ligne_5",
              "libelle_pays",
              "libelle_pays_syn",
              "libelle_voie",
              "libelle_voie_syn",
              "ext_courte",
              "ext_longue",
              "ligne3",
              "ligne2",
              "ligne1",
              "libelle_acheminement_cedex",
              "code_cedex",
              "code_postal",
              "numero_str"
            ],
            "operator": "or"
          }
        },
        {
          "multi_match": {
            "query": "DU",
            "type": "cross_fields",
            "fields": [
              "libelle_commune",
              "libelle_acheminement",
              "libelle_ligne_5",
              "libelle_pays",
              "libelle_pays_syn",
              "libelle_voie",
              "libelle_voie_syn",
              "ext_courte",
              "ext_longue",
              "ligne3",
              "ligne2",
              "ligne1",
              "libelle_acheminement_cedex",
              "code_cedex",
              "code_postal",
              "numero_str"
            ],
            "operator": "or"
          }
        },
        {
          "multi_match": {
            "query": "PARADIS",
            "type": "cross_fields",
            "fields": [
              "libelle_commune",
              "libelle_acheminement",
              "libelle_ligne_5",
              "libelle_pays",
              "libelle_pays_syn",
              "libelle_voie",
              "libelle_voie_syn",
              "ext_courte",
              "ext_longue",
              "ligne3",
              "ligne2",
              "ligne1",
              "libelle_acheminement_cedex",
              "code_cedex",
              "code_postal",
              "numero_str"
            ],
            "operator": "or"
          }
        }
      ],
      "filter": {
        "bool": {
          "must": [
            {
              "term": {
                "flag_diffusable": "1"
              }
            },
            {
              "term": {
                "actif": "1"
              }
            }
          ]
        }
      }
    }
  }
}
```

---

<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:** [May 5, 2021, 7:47am UTC](https://discuss.elastic.co/t/optimisation-dune-requete/271489/5 "2021-05-05T07:47:14Z")

</div>

Mais tu demandes vraiment à tes utilisateurs de découper leur recherche dans plusieurs champs?

Genre ils saisissent `61`, `RUE`, `DU` et `PARADIS` ou `61 RUE DU PARADIS`?

Pour répondre exactement à ta question, peux-tu fournir un script qui permet de reproduire ton cas ? Comme expliqué ici : [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). Essaye de faire un exemple le plus simple possible (pas besoin de tous les champs).

Un tel script est quelque chose qu'on peut coller dans la Dev Console de Kibana et cliquer sur le bouton "RUN" pour reproduire ton cas. Un tel script permet de mieux comprendre ce que tu fais et d'obtenir une résolution plus rapide de ta question.

---

<div class="post-metadata">

**Author:** ![fredo33](https://avatars.discourse-cdn.com/v4/letter/f/cdc98d/32.png) [@fredo33](https://discuss.elastic.co/u/fredo33)\
**Post date:** [May 5, 2021, 2:04pm UTC](https://discuss.elastic.co/t/optimisation-dune-requete/271489/6 "2021-05-05T14:04:15Z")

</div>

Bonjour Dadoonet , je vais essayer d'être plus clair .  
L’utilisateur saisie « 61 RUE DU PARADIS » et on souhaite ce résultat`  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/6/f/6fe8c2954c05c4c49a375e61ede80dbb677642e8.png)

Mon CPU est tres vite saturé à 100% car il manipule 130Millions de données pour les trier par la suite alors que je lui demande seulement 30 premiere lignes, mon CPU n 'aime pas trop ça 🙂

Voici ma requete a optimiser  
Le champ chainerecherche est un "copy\_to" de plusieurs field : par exemple libelle\_voie,libelle\_commune,libelle\_ligne\_5,ligne3, ... , voir le shema ci-dessous

Requête :

{  
"from": 0,  
"size": 30,  
"query": {  
"query\_string": {  
"query": "(chainerecherche:/._61._/ )AND( chainerecherche:/._RUE._/ )AND( chainerecherche:/._DU._/ )AND( chainerecherche:/._PARADIS._/) AND (type: ligne3 OR type: pdi OR type: voie OR type: commune ) AND actif:1 AND flag\_diffusable:1",  
"fields": ,  
"type": "best\_fields",  
"default\_operator": "and",  
"max\_determinized\_states": 10000,  
"enable\_position\_increments": true,  
"fuzziness": "AUTO",  
"fuzzy\_prefix\_length": 0,  
"fuzzy\_max\_expansions": 50,  
"phrase\_slop": 0,  
"escape": false,  
"auto\_generate\_synonyms\_phrase\_query": true,  
"fuzzy\_transpositions": true,  
"boost": 1  
}  
},  
"version": true,  
"track\_scores": true,  
"highlight": {  
"pre\_tags": [  
"\<span style='color:"  
],  
"post\_tags": [  
""  
],  
"require\_field\_match": false,  
"fields": {  
"ligne3": {},  
"numero": {},  
"ext\_courte": {},  
"ext\_longue": {},  
"libelle\_voie": {},  
"libelle\_voie\_syn": {},  
"libelle\_ligne\_5": {},  
"libelle\_acheminement": {},  
"code\_postal": {}  
}  
}  
}

Voici les infos infra :

Mon index contient toutes les rues de France:

• 25 462 993 entrées  
• Taille de l’indice de 10 Go

Nous avons 3 primaires et 1 répliquat sur un cluster de 6 serveurs (32 Go de RAM / 8cpu)  
Nous avons appliqué toutes les recommandations [elastic.co](http://elastic.co) « tune-for-search-speed »

La configuration java est comme ceci :  
root@ELK001:~# ps aux | grep java  
elastic+ 45085 170 61.9 31052544 20460004 ? SLsl 03:18 860:27 /bin/java -Xms16g -Xmx16g -XX:+UseConcMarkSweepGC -XX:CMSInitiatingOccupancyFraction=75 -XX:+UseCMSInitiatingOccupancyOnly -Des.networkaddress.cache.ttl=60 -Des.networkaddress.cache.negative.ttl=10 -XX:+AlwaysPreTouch -Xss1m -Djava.awt.headless=true -Dfile.encoding=UTF-8 -Djna.nosys=true -XX:-OmitStackTraceInFastThrow -Dio.netty.noUnsafe=true -Dio.netty.noKeySetOptimization=true -Dio.netty.recycler.maxCapacityPerThread=0 -Dlog4j.shutdownHookEnabled=false -Dlog4j2.disable.jmx=true -Djava.io.tmpdir=/tmp/elasticsearch-3573362637254362332 -XX:ErrorFile=/var/log/elasticsearch/hs\_err\_pid%p.log -Des.path.home=/usr/share/elasticsearch -Des.path.conf=/etc/elasticsearch -Des.distribution.flavor=default -Des.distribution.type=rpm -cp /usr/share/elasticsearch/lib/\* org.elasticsearch.bootstrap.Elasticsearch -p /var/run/elasticsearch/elasticsearch.pid

Les requêtes les plus lentes sont celles qui utilisent des termes très fréquents dans l’indice out comme « 61 » ou « rue » (rue en français) « rue » est présent 13.287.097 fois plus de 25.462.993 enregistrements

Lorsque j’utilise le profileur, nous voyons que le temps est principalement consacré aux mots clés les plus courants de l’index : 1 (nombre de la rue) rue (rue en français) du (de en français) et dans la phase « build\_scorer »

**Type Auto temps Total Time % Temps**  
BooleanQuery +querystring:/parad._/ +requête... 0.5ms 116.6ms **99.25%**  
MultiTermQueryConstantScoreWrapper requête:/1._/ 52.0ms 52.0ms **44.25%**  
MultiTermQueryConstantScoreWrapper requête:/rue._/ 38.1ms 38.1ms **32.45%**  
MultiTermQueryConstantScoreWrapper requête:/du._/ 24.7ms 24.7ms 21.03%  
MultiTermQueryConstantScoreWrapper requête:/parad.\*/ 1.3ms 1.3ms 1.07%

**Ventilation du calendrier**  
build\_scorer 24,6 m **99,7 %**  
avance de 60,4μs 0,2 %  
score 24.6μs 0.1%  
create\_weight 110,0ns 0,0%  
match 0.0ns 0.0%  
next\_doc 0,0ns 0,0%

Cartographie de l’index :  
{  
"mapping": {  
"proficDocument": {  
"properties": {  
"cea": {  
"type": "keyword"  
},  
"querystring": {  
"type": "text"  
},  
"querystring\_ligne4": {  
"type": "text"  
},  
"querystring\_ligne6": {  
"type": "text"  
},  
"code\_afnor": {  
"type": "keyword",  
"index": false  
},  
"code\_cedex": {  
"type": "keyword",  
"copy\_to": [  
"querystring"  
]  
},  
"code\_insee": {  
"type": "keyword"  
},  
"code\_insee\_ancienne\_commune": {  
"type": "keyword"  
},  
"code\_postal": {  
"type": "keyword"  
},  
"commentaires": {  
"type": "text"  
},  
"coordonnees": {  
"type": "geo\_point"  
},  
"dateExport": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"dateRef": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"desc\_voie": {  
"type": "text",  
"index": false  
},  
"desc\_voie\_syn": {  
"type": "text",  
"index": false  
},  
"ext\_courte": {  
"type": "keyword",  
"copy\_to": [  
"querystring"  
]  
},  
"ext\_longue": {  
"type": "keyword",  
"copy\_to": [  
"querystring",  
"querystring\_ligne4"  
]  
},  
"id": {  
"type": "alias",  
"path": "\_id"  
},  
"idza": {  
"type": "keyword",  
"index": false  
},  
"libelle\_acheminement": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
},  
"copy\_to": [  
"querystring",  
"querystring\_ligne6",  
"libelle\_acheminement\_str"  
]  
},  
"libelle\_acheminement\_cedex": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
},  
"copy\_to": [  
"querystring"  
]  
},  
"libelle\_acheminement\_str": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
}  
},  
"libelle\_commune": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
},  
"copy\_to": [  
"querystring"  
]  
},  
"libelle\_ligne\_5": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
},  
"copy\_to": [  
"querystring"  
]  
},  
"libelle\_pays": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
},  
"copy\_to": [  
"querystring"  
]  
},  
"libelle\_pays\_syn": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
},  
"copy\_to": [  
"querystring"  
]  
},  
"libelle\_projection": {  
"type": "keyword",  
"index": false  
},  
"libelle\_raison": {  
"type": "keyword",  
"index": false  
},  
"libelle\_voie": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
},  
"copy\_to": [  
"querystring",  
"querystring\_ligne4"  
]  
},  
"libelle\_voie\_syn": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
},  
"copy\_to": [  
"querystring"  
]  
},  
"ligne1": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
},  
"copy\_to": [  
"querystring"  
]  
},  
"ligne2": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
},  
"copy\_to": [  
"querystring"  
]  
},  
"ligne3": {  
"type": "text",  
"fields": {  
"keyword": {  
"type": "keyword",  
"ignore\_above": 256  
}  
},  
"copy\_to": [  
"querystring"  
]  
},  
"matvoie": {  
"type": "keyword",  
"index": false  
},  
"matvoie\_syn": {  
"type": "keyword",  
"index": false  
},  
"mention\_speciale": {  
"type": "text"  
},  
"mot\_directeur": {  
"type": "keyword",  
"index": false  
},  
"mot\_directeur\_syn": {  
"type": "keyword",  
"index": false  
},  
"new\_cea": {  
"type": "keyword"  
},  
"numero": {  
"type": "integer",  
"copy\_to": [  
"querystring",  
"querystring\_ligne4",  
"numero\_str"  
]  
},  
"numero\_mention\_speciale": {  
"type": "text"  
},  
"numero\_str": {  
"type": "keyword"  
},  
"type": {  
"type": "keyword"  
},  
"type\_pays": {  
"type": "keyword",  
"index": false  
},  
"type\_projection": {  
"type": "integer",  
"index": false  
},  
"type\_raison": {  
"type": "keyword",  
"index": false  
},  
"type\_synonyme": {  
"type": "keyword",  
"index": false  
},  
"type\_voie": {  
"type": "keyword",  
"index": false  
},  
"type\_voie\_syn": {  
"type": "keyword",  
"index": false  
},  
"x": {  
"type": "float"  
},  
"y": {  
"type": "float"  
}  
}  
}  
}  
}

---

<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:** [May 5, 2021, 5:38pm UTC](https://discuss.elastic.co/t/optimisation-dune-requete/271489/7 "2021-05-05T17:38:20Z")

</div>

Merci de formater ton code, les logs etc.

Please format your code, logs or configuration files using `</>` icon as explained in [this guide](https://discuss.elastic.co/t/about-the-elasticsearch-category/21) and not the citation button. It will make your post more readable.

Or use markdown style like:

````
```
CODE
```

````

This is the icon to use if you are not using markdown format:

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/7/e/7e6e239431ec2d71cbf1beef741f2e93e7cc762c.jpg)

There's a live preview panel for exactly this reasons.

Lots of people read these forums, and many of them will simply skip over a post that is difficult to read, because it's just too large an investment of their time to try and follow a wall of badly formatted text.  
If your goal is to get an answer to your questions, it's in your interest to make it as easy to read and understand as possible.  
Please update your post.

Peux-tu faire un script **simple** pour qu'on puisse bosser à partir de là ?

---

<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:** [June 2, 2021, 5:38pm UTC](https://discuss.elastic.co/t/optimisation-dune-requete/271489/8 "2021-06-02T17:38:27Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.