Profiling and optimizing queries

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Profiling and optimizing queries

unlucio
Hello everybody,
In the attempt to optimize a query I followed the suggestions I found in this presentation:
https://speakerdeck.com/polyfractal/elasticsearch-query-optimization

transforming my query form:

{
  "index": "products_en",
  "from": 0,
  "size": 100,
  "q": "apple iphone AND visible:1",
  "_source": [
    "sku",
    "name",
    "brand",
    "link",
    "maxImages",
    "imageKey",
    "sizes",
    "price"
  ],
  "body": {
    "aggs": {
      "sim_type": {
        "terms": {
          "field": "attributesFacets.sim_type"
        }
      },
      "screen_type": {
        "terms": {
          "field": "attributesFacets.screen_type"
        }
      },
      "color": {
        "terms": {
          "field": "attributesFacets.color_family",
          "size": 0
        }
      },
      "price": {
        "range": {
          "field": "price",
          "ranges": [
            {
              "to": 99
            },
            {
              "from": 100,
              "to": 149
            },
            {
              "from": 150,
              "to": 199
            },
            {
              "from": 200,
              "to": 299
            },
            {
              "from": 300
            }
          ]
        }
      },
      "size": {
        "terms": {
          "field": "sizes"
        }
      },
      "brand": {
        "terms": {
          "field": "brand.key"
        }
      },
      "category": {
        "terms": {
          "field": "category"
        }
      }
    }
  }
}

to

{
  "index": "products_en",
  "from": 0,
  "size": 100,
  "body": {
    "query": {
      "filtered": {
        "query": {
          "match_all": {}
        },
        "filter": {
          "bool": {
            "must": [
              {
                "term": {
                  "search": "apple",
                  "_cached": true
                }
              },
              {
                "term": {
                  "search": "iphone",
                  "_cached": true
                }
              }
            ]
          }
        }
      }
    },
    "_source": [
      "sku",
      "name",
      "brand",
      "link",
      "maxImages",
      "imageKey",
      "sizes",
      "price"
    ],
    "aggs": {
      "sim_type": {
        "terms": {
          "field": "attributesFacets.sim_type"
        }
      },
      "screen_type": {
        "terms": {
          "field": "attributesFacets.screen_type"
        }
      },
      "color": {
        "terms": {
          "field": "attributesFacets.color_family",
          "size": 0
        }
      },
      "price": {
        "range": {
          "field": "price",
          "ranges": [
            {
              "to": 99
            },
            {
              "from": 100,
              "to": 149
            },
            {
              "from": 150,
              "to": 199
            },
            {
              "from": 200,
              "to": 299
            },
            {
              "from": 300
            }
          ]
        }
      },
      "size": {
        "terms": {
          "field": "sizes"
        }
      },
      "brand": {
        "terms": {
          "field": "brand.key"
        }
      },
      "category": {
        "terms": {
          "field": "category"
        }
      }
    }
  }
}

without any apparent gain in performances.
How can I profile my query so to understand where I'm spending the most time and then been able to optimize my data, query and eventually my mapping?

Goggling around I just found "sys admins" side insight on optimization (such as java memory configuration, using sdd etc), but what about optimizing data and queries?
How do I know if my data mappings and queries aren't "screwed up"?

As ES noob from my reported optimization attempt just gave me an more complicated query but no performance benefit and now I'm not sure how to find out/understand what I'm doing wrong.

thanks for help

best
lucio


--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/a1459f7c-2c39-491e-a368-af893856aff9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Profiling and optimizing queries

unlucio
Anyone have any idea? Or is just not possible? :((

On Tuesday, March 10, 2015 at 10:45:23 AM UTC+4, unlucio wrote:
Hello everybody,
In the attempt to optimize a query I followed the suggestions I found in this presentation:
<a href="https://speakerdeck.com/polyfractal/elasticsearch-query-optimization" target="_blank" rel="nofollow" onmousedown="this.href='https://www.google.com/url?q\75https%3A%2F%2Fspeakerdeck.com%2Fpolyfractal%2Felasticsearch-query-optimization\46sa\75D\46sntz\0751\46usg\75AFQjCNFCV0QK-JjCytW4kYlskQjFYp-xlQ';return true;" onclick="this.href='https://www.google.com/url?q\75https%3A%2F%2Fspeakerdeck.com%2Fpolyfractal%2Felasticsearch-query-optimization\46sa\75D\46sntz\0751\46usg\75AFQjCNFCV0QK-JjCytW4kYlskQjFYp-xlQ';return true;">https://speakerdeck.com/polyfractal/elasticsearch-query-optimization

transforming my query form:

{
  "index": "products_en",
  "from": 0,
  "size": 100,
  "q": "apple iphone AND visible:1",
  "_source": [
    "sku",
    "name",
    "brand",
    "link",
    "maxImages",
    "imageKey",
    "sizes",
    "price"
  ],
  "body": {
    "aggs": {
      "sim_type": {
        "terms": {
          "field": "attributesFacets.sim_type"
        }
      },
      "screen_type": {
        "terms": {
          "field": "attributesFacets.screen_type"
        }
      },
      "color": {
        "terms": {
          "field": "attributesFacets.color_family",
          "size": 0
        }
      },
      "price": {
        "range": {
          "field": "price",
          "ranges": [
            {
              "to": 99
            },
            {
              "from": 100,
              "to": 149
            },
            {
              "from": 150,
              "to": 199
            },
            {
              "from": 200,
              "to": 299
            },
            {
              "from": 300
            }
          ]
        }
      },
      "size": {
        "terms": {
          "field": "sizes"
        }
      },
      "brand": {
        "terms": {
          "field": "brand.key"
        }
      },
      "category": {
        "terms": {
          "field": "category"
        }
      }
    }
  }
}

to

{
  "index": "products_en",
  "from": 0,
  "size": 100,
  "body": {
    "query": {
      "filtered": {
        "query": {
          "match_all": {}
        },
        "filter": {
          "bool": {
            "must": [
              {
                "term": {
                  "search": "apple",
                  "_cached": true
                }
              },
              {
                "term": {
                  "search": "iphone",
                  "_cached": true
                }
              }
            ]
          }
        }
      }
    },
    "_source": [
      "sku",
      "name",
      "brand",
      "link",
      "maxImages",
      "imageKey",
      "sizes",
      "price"
    ],
    "aggs": {
      "sim_type": {
        "terms": {
          "field": "attributesFacets.sim_type"
        }
      },
      "screen_type": {
        "terms": {
          "field": "attributesFacets.screen_type"
        }
      },
      "color": {
        "terms": {
          "field": "attributesFacets.color_family",
          "size": 0
        }
      },
      "price": {
        "range": {
          "field": "price",
          "ranges": [
            {
              "to": 99
            },
            {
              "from": 100,
              "to": 149
            },
            {
              "from": 150,
              "to": 199
            },
            {
              "from": 200,
              "to": 299
            },
            {
              "from": 300
            }
          ]
        }
      },
      "size": {
        "terms": {
          "field": "sizes"
        }
      },
      "brand": {
        "terms": {
          "field": "brand.key"
        }
      },
      "category": {
        "terms": {
          "field": "category"
        }
      }
    }
  }
}

without any apparent gain in performances.
How can I profile my query so to understand where I'm spending the most time and then been able to optimize my data, query and eventually my mapping?

Goggling around I just found "sys admins" side insight on optimization (such as java memory configuration, using sdd etc), but what about optimizing data and queries?
How do I know if my data mappings and queries aren't "screwed up"?

As ES noob from my reported optimization attempt just gave me an more complicated query but no performance benefit and now I'm not sure how to find out/understand what I'm doing wrong.

thanks for help

best
lucio


--
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 [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/5773cccd-6ef4-4fbe-9e9b-00e20edb33c4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.