Example of query execution plan where the cardinality estimation makes a difference

Question

I am trying to create an example of a query execution plan which shows that if I have the cardinality estimation for some column of a table I can decide which query execution plan has the least cost.

So I have these two posts (1 and 2) which I can understand estimated cardinality as the number of distinct values on a column. And then I calculate the selectivity.

SELECT max(price) FROM tickets WHERE country = "CANADA";

If my table has 180 items and only 10 are CANADA. And there are only 4 different countries (CANADA, BRAZIL, USA, GERMANY). So....

the cardinality of column country é 4, because it is the number of distinct items.

The selectivity of the column country = CANADA is the number of items accessed divided by the items on the table. 10/180 = 0.0555.

But which are the different query execution plans that the optimizer could choose based on the cardinality?

If this is not a good example, could someone point one query that the cardinality would be a treasure to the optimizer in order to decide to chose one plan versus another?

Thanks, Felipe

Answer 1

At 180 rows, you're not likely to see any difference whatsoever.

In order to see different plans get chosen at different predicate distributions, you'd need quite bit more data, and likely competing data sources (indexes) to aid them.

Let's say you had 1.8mm rows in the table. If you've got

• A clustered index on a column unrelated to price or country (Like Id or something)
• A nonclustered index on price and country
• A single column nonclustered index on country

You may start to see a difference in the method of aggregation, serial vs. parallel plans chosen, and index choice, depending on which country you search for and its selectivity.

For an example of a recent Q&A:

• Large variances between two similar queries when doing WHERE EXISTS on related table

For a more complex reasons:

• Why is my query suddenly slower than it was yesterday?

Answer 2

So this isn't exactly what you are looking for, but it may help. I provided an answer to a separate question asked last week.

Very similar queries, vastly different performance

In this question the addition of a single value to a IN() statement changed the query plan and execution time. There is an awesome answer to that question provided by a different person so hopefully it give you the details you are looking for.