AWS Elasticsearch test queries

aws_es_tests_20151019.txt

###############################################################################
# AWS compute optimized instance, 16vcpu, 30gb ram
###############################################################################

# how many trips indexed?
#
# 15M, 22ms
# 26M, 50ms
# 39M, 80ms
# 54M, 93ms
time curl -XGET 'http://ec2-52-24-133-209.us-west-2.compute.amazonaws.com:9200/taxi/pickup/_count'


# how many trips w/ 5 passengers?
#
# 15M, 17ms
# 26M, 19ms
# 26M, 20ms
# 54M, 31ms
time curl -XGET 'http://ec2-52-24-133-209.us-west-2.compute.amazonaws.com:9200/taxi/pickup/_count' -d '
{
    "query" : {
        "term" : { "passenger_count" : "5" }
    }
}'


#
# summarize full table by # of passengers
#
# 15M, 120ms
# 26M, 213ms
# 39M, 332ms
# 54M, 480ms
time curl -XPOST 'ec2-52-24-133-209.us-west-2.compute.amazonaws.com:9200/taxi/_search?pretty' -d '
{
  "size": 0,
  "aggs": {
    "group_by_passengers": {
      "terms": {
        "field": "passenger_count"
      }
    }
  }
}'

#
# zoomed out document count per geohash grid cell
#
# 15M, 10000ms cold, 88ms hot
# 26M, 12000ms cold, 174ms hot
# 39M, 13500ms cold, 254ms hot
# 54M, 13900ms cold, 517ms hot
#
time curl -XPOST 'ec2-52-24-133-209.us-west-2.compute.amazonaws.com:9200/taxi/_search?pretty' -d '
{
    "aggregations" : {
        "myLarge-GrainGeoHashGrid" : {
            "filter" : {
              "range": {
                 "trip_time_in_secs": {
                   "gte": 60,
                   "lte": 70
                 }
               }
            },
            "aggregations":{
                "zoom1":{
                  "geohash_grid" : {
                      "field" : "pickup_location",
                      "precision" : 6
                  }
                }
              }
        }
    }
}'


time curl -XPOST 'ec2-52-24-133-209.us-west-2.compute.amazonaws.com:9200/taxi/_search?pretty' -d '
{
    "aggregations" : {
        "myLarge-GrainGeoHashGrid" : {
            "filter" : {
            "bool" : {
              "must" : [
                { "range" : { "trip_time_in_secs" : { "gte": 60, "lte": 70 } } },
                { "geohash_cell": { "pickup_location": "dr5r" } }
                ]
              }
            },
            "aggregations":{
                "zoom1":{
                  "geohash_grid" : {
                      "field" : "pickup_location",
                      "precision" : 8
                  }
                }
              }
        }
    }
}'

#
# histogram of all values in index on the trip_time_in_secs
#
# 15M, 326ms
# 26M, 571ms
# 39M, 554ms
# 54M, 1200ms
time curl -XPOST 'ec2-52-24-133-209.us-west-2.compute.amazonaws.com:9200/taxi/_search?pretty' -d '
{
    "aggregations" : {
        "trip_time_in_secs" : {
            "histogram" : {
              "field": "trip_time_in_secs",
              "interval": 5
          }
        }
    }
}'


#
# zoomed in document count per geohash grid cell
#
# 15M, 226ms
# 26M, 431ms
# 39M, 631ms
# 54M, 870ms
time curl -XPOST 'ec2-52-24-133-209.us-west-2.compute.amazonaws.com:9200/taxi/_search?pretty' -d '
{
    "aggregations" : {
        "zoomedInView" : {
            "filter" : {
                "geo_bounding_box" : {
                    "pickup_location" : {
                        "top_left" : "40.7280960, -73.9850510",
                        "bottom_right" : "40.7280460, -73.9850010"
                    }
                }
            },
            "aggregations":{
                "zoom1":{
                    "geohash_grid" : {
                        "field":"pickup_location",
                        "precision":10
                    }
                }
            }
        }
    }
 }'
 

#
# histogram of all values in index on the trip_time_in_secs
# within a specific georectangle
#
# 15M, 220ms
# 26M, 421ms
# 39M, 431ms
# 54M, 700ms
#
time curl -XPOST 'ec2-52-24-133-209.us-west-2.compute.amazonaws.com:9200/taxi/_search?pretty' -d '
{
  "aggregations" : {
      "zoomedInView" : {
          "filter" : {
              "geo_bounding_box" : {
                  "pickup_location" : {
                      "top_left" : "40.7280960, -73.9850510",
                      "bottom_right" : "40.7280460, -73.9850010"
                  }
              }
          },
         "aggregations" : {
             "trip_time_in_secs" : {
                 "histogram" : {
                   "field": "trip_time_in_secs",
                   "interval": 1
               }
             }
         }
      }
    }
}'


## using geohash to filter, not bbox
#
# histogram of all values in index on the trip_time_in_secs
# within a specific georectangle
# using a GEOHASH_CELL filter instead of bbox
* AND a range filter

# 15M, 141ms
# 26M, 250ms
# 39M, 300ms
# 54M, 511ms
time curl -XPOST 'ec2-52-24-133-209.us-west-2.compute.amazonaws.com:9200/taxi/_search?pretty' -d '
{
  "aggregations" : {
      "zoomedInView" : {
          "filter" : {
            "bool" : {
              "must" : [
                { "range" : { "trip_time_in_secs" : { "gte": 10, "lte": 200 } } },
                { "geohash_cell": { "pickup_location": "dr5rsqmf0" } }
                ]
              }
            },
         "aggregations" : {
             "trip_time" : {
                 "histogram" : {
                   "field": "trip_time_in_secs",
                   "interval": 1
               }
             }
         }
      }
    }
}'   


#
# zoomed in document count per geohash grid cell
# hot	0m0.165
#
# 15M, 100ms
# 26M, 243ms
# 39M, 253ms
time curl -XPOST 'ec2-52-24-133-209.us-west-2.compute.amazonaws.com:9200/taxi/_search?pretty' -d '
{
   "aggregations" : {
       "zoomedInView" : {
          "filter" : {
          "geohash_cell": {
            "pickup_location": "dr5rsqmf0"
          }
        },
           "aggregations":{
               "zoom1":{
                   "geohash_grid" : {
                       "field":"pickup_location",
                       "precision":10
                   }
               }
           }
       }
   }
}'