# Elastic Performance for Spatial Queries Slows Down when Geometries are in Millions **URL:** \ **Category:** Elasticsearch\ **Created:** [January 29, 2024, 6:39am UTC](https://discuss.elastic.co/t/elastic-performance-for-spatial-queries-slows-down-when-geometries-are-in-millions/351959 "2024-01-29T06:39:33Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![purijs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/purijs/32/131162_2.png) [@purijs](https://discuss.elastic.co/u/purijs)\ **Post date:** [January 29, 2024, 6:39am UTC](https://discuss.elastic.co/t/elastic-performance-for-spatial-queries-slows-down-when-geometries-are-in-millions/351959/1 "2024-01-29T06:39:33Z")
I have a usecase to query ES for spatial intersection query with a `Multipolygon`. I tried two approaches where I store documents in flat structure, where 1 Polygon represents `1 document` and in another setup I aggregate docs before ingesting and so 1 Polygon now represents `n documents` using `nested` type. When I do spatial intersection query, I get very similar query time, average difference of `+ - 2sec` but the reduction of documents between the two approaches is around 5x, meaning second approach has only `3 Million` documents as compared to `13M` previously I want to understand what can I optimise in terms of my spatial query or index settings. I intend to have all my spatial queries perform at around `1s` irrespective of polygon complexity. My intention is to get unique values for some `metadata key ` based on a `spatial intersection`, in this case, `areaName`. I'm aware of different complexity of polygons will give different response times but comparing 13M vs 3M doc index, I expected a considerable improvement. I'm running everything using Python ES API. On checking with `Kibana Profiler`, I understand that `IndexOrDocValuesQuery` takes longest time in which specifically `build_scorer` is taking 90% of the time. Initially, I tried to ingest my documents in a flat structure with this kind of mapping: ```auto { "mappings": { "properties": { "areaName": { "type": "keyword" }, "location": { "type": "geo_shape" } } } } ``` Here's a sample of docs for this index: ```auto { "_index": "catalog.a", "_id": "71c0fca6-45a0-4c15-942a-a31a674f5d0d", "_score": 0.0025355585, "_source": { "location": "POLYGON ((-76.4891423210711707 37.0441858681596372, -76.4891423210711707 37.0503207513172370))", "areaName": "Dataset A" }, "_index": "catalog.a", "_id": "71c0fca6-45a0-4c15-942a-a31a674f5d0d", "_score": 0.0015355585, "_source": { "location": "POLYGON ((-76.4891423210711707 37.0441858681596372, -76.4891423210711707 37.0503207513172370))", "areaName": "Dataset B" }, "_index": "catalog.a", "_id": "71c0fca6-45a0-4c15-942a-a31a674f5d0d", "_score": 0.002355585, "_source": { "location": "POLYGON ((-76.4891423210711707 37.0441858681596372, -76.4891423210711707 37.0503207513172370))", "areaName": "Dataset C" } } ``` This index is around `80GB` in size and has `3 primary` shards and has around 13 Million such docs. I'm testing using this query for performance: ```auto response = es.search(index = catalog.a, body = { "size":0, "query": { "bool": { "filter": [ { "geo_shape": { "location": { "shape": { "type": "Multipolygon", "coordinates": country_variable }, "relation": "intersects" } } } ] } }, "aggs": { "unique_soilProperty": { "terms": { "field": "areaName", "size":15, } } } }, request_cache=False ) ``` * * * Next, I changed my mapping to this: ```auto { "mappings": { "location": { "type": "geo_shape" } "items": { "type": "nested", "properties": { "areaName": { "type": "keyword" }, } } } ``` My documents now look like this: ```auto { "_index": "catalog.b", "_id": "71c0fca6-45a0-4c15-942a-a31a674f5d0d", "_score": 0.0025355585, "_source": { "location": "POLYGON ((-76.4891423210711707 37.0441858681596372, -76.4891423210711707 37.0503207513172370))", "items":[{ "areaName": "Dataset A" }] }, "_index": "catalog.b", "_id": "71c0fca6-45a0-4c15-942a-a31a674f5d0d", "_score": 0.0015355585, "_source": { "location": "POLYGON ((-76.4891423210711707 37.0441858681596372, -76.4891423210711707 37.0503207513172370))", "items":[{ "areaName": "Dataset B" }] }} ``` This index is around `25GB` in size and has `1 primary` shard and has around 3 Million such docs. I'm testing using this query for performance: ```auto response = es.search(index = catalog.b, body = { "size":0, "query": { "bool": { "filter": [ { "geo_shape": { "location": { "shape": { "type": "Multipolygon", "coordinates": country_variable }, "relation": "intersects" } } } ] } }, "aggs": { "nested_items": { "nested": { "path": "items" }, "aggs": { "unique_dataSources": { "terms": { "field": "items.areaName", "size": 15 } } } } } }, request_cache=False ) ``` I get very similar query performance even with this setup. What kind of optimisations I could do more, to speed it up? ---
**Author:** ![Ignacio\_Vera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ignacio_vera/32/36674_2.png) [@Ignacio\_Vera](https://discuss.elastic.co/u/Ignacio_Vera)\ **Post date:** [January 30, 2024, 6:58am UTC](https://discuss.elastic.co/t/elastic-performance-for-spatial-queries-slows-down-when-geometries-are-in-millions/351959/2 "2024-01-30T06:58:36Z")
hello, Could you provide the following information? 1. Elasticsearch version 2. Index topology, e.g number of shards. 3. output of [hot threads](https://www.elastic.co/guide/en/elasticsearch/reference/current/cluster-nodes-hot-threads.html) when running the query. Thanks ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 27, 2024, 6:58am UTC](https://discuss.elastic.co/t/elastic-performance-for-spatial-queries-slows-down-when-geometries-are-in-millions/351959/3 "2024-02-27T06:58:57Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.