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?