testQueries.txt

//retrieve all trans the last month for one account
//swap acc no and date
//med settings XYZ får vi tider A-B-C. N docs tråla.
//Test 1
{
	"fields" : ["id","date", "accountNumber", "description", "transactionCode", "amount", "remoteAccountNumber"],
 	"size" : "50",
	"query" : {
		"filtered" : {
			"filter" : {
				"and" : [
					{
						"range" : {
							"date" : {
								"from" : "1259622000000"
							},
							"_cache" : false
						}
					},
					{
						"term" : {
    						"accountNumber" : 73678126984,
    						"_cache" : false
    					}
					}
				]
			}
		}
	}
}


//histogram test. size:0 gjør at vi ikke får base data, kun histogrammet
//må sette indices og acc no
//Test 2
{
	"size" : 0,
    "query" : {
        "filtered" : {
        	"filter" : {
				"term" : {
    				"accountNumber" : 73678126984,
    				"_cache" : false
    			}
			}
        }
    },
    "facets" : {
        "spending/month" : {
            "date_histogram" : {
                "key_field" : "date",
                "value_field" : "amount",
                "interval" : "month",
                "time_zone" : 2
            }
        }
    }
}

//Rerturnerer statistikk for alle typer transer for konto X, fordelt på forskjellige typer
//Test 3
{
	"fields" : ["accountNumber"],
    "query" : {
    	"filtered" : {
    		"filter" : {
    			"term" : {
    				"accountNumber" : 73678126984,
    				"_cache" : false
    			}
    		}
    	}
    },
    "facets" : {
        "type_amount_stats" : {
            "terms_stats" : {
                "key_field" : "transactionCode",
                "value_field" : "amount"
            }
        }
    }
}

//samme som over, men med flere konti. Husk multi routing.
{
  "fields": [
    "accountNumber"
  ],
  "query": {
    "filtered": {
      "filter": {
        "terms": {
          "accountNumber": [
            73678126984,
            73676966629,
            73673747844
          ],
          "execution": "bool_nocache",
          "_cache": false
        }
      }
    }
  },
  "facets": {
    "type_amount_stats": {
      "terms_stats": {
        "key_field": "transactionCode",
        "value_field": "amount"
      }
    }
  }
}

//1 shard per index per måned,  Konto med 835K docs. | 12 GB
//Test 1: 350, 30, 20. 35K docs.
//Test 2: 20K, 89, 73. 835K docs.
//Test 3: 7.2K, 123, 116. 835K docs.
//Test 4: 2.9K(cached?) 265, 269. 2M docs.

//1 shard per index per år, Konti som over | 16GB
//Test 1: 151, 23, 27.
//Test 2: 4.1K, 65, 66.
//Test 3: 1.1K, 108, 109.
//Test 4: 3.6K, 228, 226.

//1 shard per index per år, Konti som over | 12GB
//Test 1: 865, 42, 43.
//Test 2: 5.2K, 71, 70. !: 16K etter reboot. 
//Test 3: 1.5K, 99, 106.
//Test 4: 3.7K, 211, 224. 

//som over men med store på acc no, date.
//Test 1: 1371, 36, 32
//Test 2: 6.6k, 66, 75
//Test 3: 2.1K, 113, 104
//Test 4: 626, 231, 221 (3.9k etter flush/clear)

//Memindex med kun 4 felter
//Test 2: 3.2K, 67, 62

//SSD ingen store, 1 shard/index
//Test 1: 126, 29, 28
//Test 2: 3.2k, 66, 61
//Test 3: 988, 95, 93
//Test 4: 236, 193, 205
//SSD med warmers
//Test 1: 18, 20, 16
//Test 2: 62, 64, 64
//Test 3: 276, 99, 96
//Test 4: 208, 207, 204

//Bloom_pulse
//Test 1: 87, 34, 30
//Test 2: 3.6K, 62, 60
//Test 3: 1.0, 97, 98
//Test 4: 224, 222, 198

//Amount med postings memory
//Test 1: 29, 23, 24
//Test 2: 5.9k, 68, 67
//Test 3: 1.0, 103, 102
//Test 4: 262, 213, 215

//Faceting med 50 kontoer! (12M transer totalt)
//med routing og warmers: 750ms!
{
  "size": 0,
  "query": {
    "filtered": {
      "filter": {
        "terms": {
          "accountNumber": [
            56922945469,
            56926301941,
            13813418010,
            30725461708,
            54124696916,
            56924002561,
            54127525007,
            54127432744,
            65421843084,
            54128401427,
            30727984865,
            65422247591,
            56925903221,
            84987290939,
            54129861171,
            54122122675,
            72454273792,
            72458310899,
            64346386087,
            65427046619,
            30728184030,
            84988169016,
            63379475134,
            63377088174,
            15278713741,
            54126749924,
            56928400238,
            87327730680,
            54126485984,
            54131896600,
            75493596913,
            56921853763,
            54134718411,
            13813253696,
            54127636797,
            30726165925,
            56927861698,
            13816879481,
            87327415326,
            54124411093,
            54126944077,
            98718755892,
            98715932501,
            54131566279,
            54121329612,
            72457860420,
            98712781016,
            72451065841,
            70784809106,
            54138900318
          ]
        }
      }
    }
  },
  "facets": {
    "spending/month": {
      "date_histogram": {
        "key_field": "date",
        "value_field": "amount",
        "interval": "month",
        "time_zone": 2
      }
    }
  }
}

//Settings før update
curl -XPUT el1:9200/_settings -d
{
  "index": {
    "refresh_interval": -1,
    "translog.disable_flush": true
  }
}
//Update by query!
{
    "query" : {
        "filtered":{
        	"filter" : {
            "terms" : { "transactionCode" : ["_151", "_718", "_719", "_922", "_938", "R_074", "R_079", "R_086", "V_151", "U_922", "U_938", "V_074", "V_075", "V_079", "V_086", "V_093","V_094", "V_095", "V_096", "V_097", "V_099", "U_151", "R_718", "R_719", "R_718", "R_719"]}
        	}
    	} 
    },
    "script" : "ctx._source.category = 1"
}
//v2
{
    "query" : {
        "filtered":{
          "filter" : {
            "query" : { "match" : {"description":"anew"}}
          }
      } 
    },
    "script" : "ctx._source.category = 1"
}
//Setup 3: 
//took 241 sec på 59K docs, throttling 20mb
//U_b_q took 75min to insert a new field in all 20M trans.
//Throttling har stor innvirkning. 100mb -> 10 sec på 59K docs
//SSD throtling 200mb-> ca 75min på new field 20M
//19sec på 59K docs. 8.6sec på run 2.


//Warmers!
curl -XPUT el5:9200/_all/_warmer/warmer_1 -d
{
  "query" : {
      "match_all" : {}
  },
  "facets" : {
    "type_amount_stats": {
      "terms_stats": {
        "key_field": "accountNumber",
        "value_field": "amount"
      }
    }
  }
}

{
  "query": {
    "match_all": {}
  },
  "facets": {
    "type_amount_stats": {
      "terms_stats": {
        "key_field": "category",
        "value_field": "amount"
      }
    }
  }
}

{
    "query" : {
        "match_all":{}
    },
    "facets" : {
        "spending/month" : {
            "date_histogram" : {
                "key_field" : "date",
                "value_field" : "amount",
                "interval" : "month",
                "time_zone" : 2
            }
        }
    }
}


{
    "query" : {
        "match_all" : {}
    },
    "facets": {
    "type_amount_stats": {
      "terms_stats": {
        "key_field": "transactionCode",
        "value_field": "amount"
      }
    }
  }
}

//Mapping v1
{
  "trans": {
    "properties": {
      "accountNumber": {
        "type": "long",
        "postings_format": "memory"
      },
      "amount": {
        "type": "double",
        "postings_format": "memory"
      },
      "archiveReference": {
        "type": "integer"
      },
      "batchNumber": {
        "type": "integer"
      },
      "bokforingDate": {
        "type": "long",
        "precision_step": 2
      },
      "category": {
        "type": "short"
      },
      "currencyAmount": {
        "type": "double"
      },
      "currencyCode": {
        "type": "string"
      },
      "date": {
        "type": "long",
        "postings_format": "memory",
        "precision_step": 2
      },
      "description": {
        "type": "string"
      },
      "id": {
        "type": "long"
      },
      "isConfidential": {
        "type": "boolean"
      },
      "numbericalReference": {
        "type": "integer",
        "index": "no"
      },
      "posteringDate": {
        "type": "long",
        "index": "no"
      },
      "remoteAccountNumber": {
        "type": "long"
      },
      "transactionCode": {
        "type": "string",
        "index": "not_analyzed"
      },
      "valuteringDate": {
        "type": "long",
        "index": "no"
      }
    }
  }
}

//Mapping v2
{
  "trans": {
    "properties": {
      "accountNumber": {
        "type": "long",
        "postings_format": "bloom_pulsing"
      },
      "amount": {
        "type": "double",
        "postings_format": "bloom_pulsing"
      },
      "archiveReference": {
        "type": "integer"
      },
      "batchNumber": {
        "type": "integer"
      },
      "bokforingDate": {
        "type": "long"
      },
      "category": {
        "type": "short"
      },
      "currencyAmount": {
        "type": "double"
      },
      "currencyCode": {
        "type": "string"
      },
      "date": {
        "type": "long",
        "postings_format": "bloom_pulsing"
      },
      "description": {
        "type": "string"
      },
      "id": {
        "type": "integer"
      },
      "isConfidential": {
        "type": "boolean"
      },
      "numbericalReference": {
        "type": "integer",
        "index": "no"
      },
      "posteringDate": {
        "type": "long",
        "index": "no"
      },
      "remoteAccountNumber": {
        "type": "long"
      },
      "transactionCode": {
        "type": "string",
        "index": "not_analyzed"
      },
      "valuteringDate": {
        "type": "long",
        "index": "no"
      }
    }
  }
}

{
  "fields":["category"],
  "size": 30,
  "query" : {
    "filtered":{
      "query": {
        "match" : {
          "description":"ana"
        }
      },
      "filter" : {
        "not" : {
          "term" : {"category" : 5}
        }
      } 
    }
  }
}   
//Stats for field cache
curl -XGET el2:9200/_stats/fielddata/category,amount,date,accountNumber?pretty