2

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

  • From the links in your question, I'm assuming that this is SQL Server! Could could please also put in the version that you are using? – Vérace Jun 29 at 23:18
  • Hi @Vérace. No, it is not. I am sorry. I am looking for a use case example and scenario where my optimizer will have more than 1 query to execute. However, I am using Stream Processing Engines (e.g.: Flink, Spark, Storm) which have their models based on RDBMS but distributed to work over distributed file systems like HDFS. – Felipe Jul 1 at 8:24
5

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:

For a more complex reasons:

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.