Example of query execution plan where the cardinality estimation makes a difference 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
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&amp;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?