def test_index_benefit__lt__(self): index_0 = Index([self.column_0]) index_0.estimated_size = 1 index_1 = Index([self.column_1]) index_1.estimated_size = 2 # Due to its size, index_0 has the better ratio index_benefit_0 = IndexBenefit(index_0, 10) index_benefit_1 = IndexBenefit(index_1, 10) self.assertTrue(index_benefit_1 < index_benefit_0) # The ratios are equal, the columns are taken into consideration index_benefit_1 = IndexBenefit(index_1, 20) self.assertTrue(index_benefit_0 < index_benefit_1)
def test_calculate_index_benefits(self): index_0 = Index([self.column_0]) index_0.estimated_size = 5 index_1 = Index([self.column_1]) index_1.estimated_size = 1 index_2 = Index([self.column_2]) index_2.estimated_size = 3 query_result_0 = { "cost_without_indexes": 100, "cost_with_recommended_indexes": 50, "recommended_indexes": [index_0, index_1], } # Yes, negative benefit is possible query_result_1 = { "cost_without_indexes": 50, "cost_with_recommended_indexes": 60, "recommended_indexes": [index_1], } query_result_2 = { "cost_without_indexes": 60, "cost_with_recommended_indexes": 57, "recommended_indexes": [index_2], } query_result_3 = { "cost_without_indexes": 60, "cost_with_recommended_indexes": 60, "recommended_indexes": [], } query_results = { "q0": query_result_0, "q1": query_result_1, "q2": query_result_2, "q3": query_result_3, } index_benefits = self.algo._calculate_index_benefits( [index_0, index_1, index_2], query_results) expected_index_benefits = [ IndexBenefit(index_1, 40), IndexBenefit(index_0, 50), IndexBenefit(index_2, 3), ] self.assertEqual(index_benefits, expected_index_benefits)
def test_try_variations_time_limit(self): index_0 = Index([self.column_0]) index_0.estimated_size = 1 index_1 = Index([self.column_1]) index_1.estimated_size = 1 index_2 = Index([self.column_2]) index_2.estimated_size = 1 index_3 = Index([self.column_3]) index_3.estimated_size = 1 index_4 = Index([self.column_4]) index_4.estimated_size = 1 index_5 = Index([self.column_5]) index_5.estimated_size = 1 index_6 = Index([self.column_6]) index_6.estimated_size = 1 index_7 = Index([self.column_7]) index_7.estimated_size = 5 self.algo.cost_evaluation.calculate_cost = MagicMock(return_value=17) self.algo.seconds_limit = 0.2 time_before = time.time() self.algo._try_variations( selected_index_benefits=frozenset([IndexBenefit(index_0, 1)]), index_benefits=frozenset([IndexBenefit(index_1, 1)]), workload=[], ) self.assertGreaterEqual(time.time(), time_before + self.algo.seconds_limit) def fake(selected, workload): cost = 10 if IndexBenefit(index_3, 1.5) in selected: cost -= 0.5 if IndexBenefit(index_4, 0.5) in selected: cost += 0.5 if IndexBenefit(index_1, 0.5) in selected: cost += 0.5 if IndexBenefit(index_1, 0.5) in selected: cost += 0.5 return cost # In this scenario a good index has not been selected (index_3). # We test three things: # (i) That index_3 gets chosen by variation. # (ii) That the weakest index from the original selection gets # removed (index_1). # (iii) That index_4 does not get chosen even though it is better than index_1. self.algo._evaluate_workload = fake self.algo.maximum_remove = 1 self.algo.disk_constraint = 3 new = self.algo._try_variations( selected_index_benefits=frozenset([ IndexBenefit(index_0, 1), IndexBenefit(index_1, 0.5), IndexBenefit(index_2, 1), ]), index_benefits=frozenset([ IndexBenefit(index_0, 1), IndexBenefit(index_1, 0.5), IndexBenefit(index_2, 1), IndexBenefit(index_3, 1.5), IndexBenefit(index_4, 0.6), ]), workload=[], ) self.assertIn(IndexBenefit(index_3, 1.5), new) self.assertNotIn(IndexBenefit(index_4, 0.5), new) self.assertNotIn(IndexBenefit(index_1, 0.5), new) # Test that good index is not chosen because of storage restrictions new = self.algo._try_variations( selected_index_benefits=frozenset([IndexBenefit(index_0, 1)]), index_benefits=frozenset( [IndexBenefit(index_0, 1), IndexBenefit(index_7, 5)]), workload=[], ) self.assertEqual(new, set([IndexBenefit(index_0, 1)]))
def test_combine_subsumed(self): index_0_1 = Index([self.column_0, self.column_1]) index_0_1.estimated_size = 2 index_0 = Index([self.column_0]) index_0.estimated_size = 1 index_1 = Index([self.column_1]) index_1.estimated_size = 1 # Scenario 1. Index subsumed because better ratio for larger index index_benefits = [ IndexBenefit(index_0_1, 21), IndexBenefit(index_0, 10) ] subsumed = self.algo._combine_subsumed(index_benefits) expected = [IndexBenefit(index_0_1, 31)] self.assertEqual(subsumed, expected) # Scenario 2. Index not subsumed because better index has fewer attributes index_benefits = [ IndexBenefit(index_0, 11), IndexBenefit(index_0_1, 20) ] subsumed = self.algo._combine_subsumed(index_benefits) expected = [IndexBenefit(index_0, 11), IndexBenefit(index_0_1, 20)] self.assertEqual(subsumed, expected) # Scenario 3. Index not subsumed because last element does not match # attribute even though better ratio index_0_1_2 = Index([self.column_0, self.column_1, self.column_2]) index_0_1_2.estimated_size = 3 index_0_2 = Index([self.column_0, self.column_2]) index_0_2.estimated_size = 2 index_benefits = [ IndexBenefit(index_0_1_2, 31), IndexBenefit(index_0_2, 20) ] subsumed = self.algo._combine_subsumed(index_benefits) expected = [IndexBenefit(index_0_1_2, 31), IndexBenefit(index_0_2, 20)] self.assertEqual(subsumed, expected) # Scenario 4. Multi Index subsumed index_benefits = [ IndexBenefit(index_0_1_2, 31), IndexBenefit(index_0_1, 20) ] subsumed = self.algo._combine_subsumed(index_benefits) expected = [IndexBenefit(index_0_1_2, 51)] self.assertEqual(subsumed, expected) # Scenario 5. Multiple Indexes subsumed index_benefits = [ IndexBenefit(index_0_1_2, 31), IndexBenefit(index_0_1, 20), IndexBenefit(index_0, 10), ] subsumed = self.algo._combine_subsumed(index_benefits) expected = [IndexBenefit(index_0_1_2, 61)] self.assertEqual(subsumed, expected) # Scenario 6. Input returned if len(input) < 2 subsumed = self.algo._combine_subsumed([IndexBenefit(index_0_1, 21)]) expected = [IndexBenefit(index_0_1, 21)] self.assertEqual(subsumed, expected) # Scenario 7. Input not sorted by ratio throws with self.assertRaises(AssertionError): subsumed = self.algo._combine_subsumed( [IndexBenefit(index_0, 10), IndexBenefit(index_0_1, 21)])