Accuracy testing of query optimizers

摘要

Query optimizer accuracy is tested by measuring the actual execution times on a database of each of a plurality of alternative query plans for each of a plurality of queries, first ranking the plans by the actual execution times, then ranking the plans by estimated execution times, and correlating the two rankings by computing a rank correlation metric. The rank correlation metric is normalized so that different optimizers can be objectively compared.

主权项

1. A computer-implemented method comprising:
receiving a query; receiving different query plans that a first query optimizer has generated as alternatives for executing the query; determining, for each query plan of two or more of the query plans that the first query optimizer has generated as an alternative for executing the query, (i) an estimated cost that the first query optimizer associates with executing the query plan, and (ii) an actual cost that is measured during execution of the query plan; generating, by one or more computers, (i) a first ranking of the two or more query plans, ordered according to measured, actual cost, and (ii) a second ranking of the two or more query plans, ordered according to estimated cost; and determining, based at least on the first ranking of the two or more query plans and the second ranking of the two or more query plans, a first rank correlation score that reflects an accuracy of the first query optimizer in estimating costs associated with executing query plans; determining a second rank correlation score associated with a second query optimizer different from the first query optimizer; and selecting, as a more accurate query optimizer, one of (i) the first query optimizer, or (ii) the second query optimizer, based at least on comparing the first rank correlation score and the second rank correlation score.