Mfcttrf

What happens if a caster is surprised while casting a spell with a long casting time?

How useful would a hydroelectric power plant be in the post-apocalypse world?

How do I present a future free of gender stereotypes without being jarring or overpowering the narrative?

A* pathfinding algorithm too slow

Could all three Gorgons turn people to stone, or just Medusa?

What specifically counts as "anything" in spell text?

How to track mail undetectably?

Why doesn't SpaceX land boosters in Africa?

Is it advisable to inform the CEO about his brother accessing his office?

Delete all files from a folder using a bat that match a certain pattern in Windows 10

What does 'in attendance' mean on a death certificate - England?

German idiomatic equivalents of 能骗就骗 (if you can trick, then trick)

What verb for taking advantage fits in "I don't want to ________ on the friendship"?

The Lucas argument vs the theorem-provers -- who wins and why?

Is my guitar action too high or is the bridge too high?

Why should I allow multiple IPs on a website for a single session?

Calculus, water poured into a cone: Why is the derivative non-linear?

Can dual citizens open crypto exchange accounts where U.S. citizens are prohibited?

What does 5d4 x 10 gp mean?

Do electrons really perform instantaneous quantum leaps?

How would one prevent political gerrymandering?

Is it theoretically possible to hack printer using scanner tray?

Why do movie directors use brown tint on Mexico cities?

Dynamic Sql Query - how to add an int to the code?

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

Why is my query suddenly slower than it was yesterday?SQL Server cardinality hintVery similar queries, vastly different performanceLarge variances between two similar queries when doing WHERE EXISTS on related tablewhat is more important on a query execution plan, cardinality or selectivity?Different execution plan for the same query if I change a value in the predicateoptimize the query with big and low cardinalityHow does SQL Server's optimizer estimate the number of rows in a joined table?Understanding below execution planHow to hint many-to-many join in SQL Server?Estimation failure in Stored Procedure execution planIndex Seek Operator CostImproving statistics on a large “WHERE IN” SQL Server queryNeed to understand the drastic difference in execution plan between two querieswhat is more important on a query execution plan, cardinality or selectivity?

1

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

optimization execution-plan cardinality-estimates

share|improve this question

asked 8 hours ago

FelipeFelipe

11933 bronze badges

add a comment | 

1

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

optimization execution-plan cardinality-estimates

share|improve this question

asked 8 hours ago

FelipeFelipe

11933 bronze badges

add a comment | 

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

optimization execution-plan cardinality-estimates

share|improve this question

asked 8 hours ago

FelipeFelipe

11933 bronze badges

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

share|improve this question

asked 8 hours ago

FelipeFelipe

11933 bronze badges

share|improve this question

asked 8 hours ago

FelipeFelipe

11933 bronze badges

asked 8 hours ago

FelipeFelipe

11933 bronze badges

asked 8 hours ago

FelipeFelipe

11933 bronze badges

2 Answers
2

active

oldest

votes

3

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?

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Erik DarlingErik Darling

25.2k1313 gold badges7777 silver badges127127 bronze badges

add a comment | 

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.

share|improve this answer

answered 8 hours ago

Kirk SaundersKirk Saunders

45722 silver badges1010 bronze badges

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f241624%2fexample-of-query-execution-plan-where-the-cardinality-estimation-makes-a-differe%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

3

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?

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Erik DarlingErik Darling

25.2k1313 gold badges7777 silver badges127127 bronze badges

add a comment | 

3

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?

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Erik DarlingErik Darling

25.2k1313 gold badges7777 silver badges127127 bronze badges

add a comment | 

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?

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Erik DarlingErik Darling

25.2k1313 gold badges7777 silver badges127127 bronze badges

share|improve this answer

edited 7 hours ago

answered 8 hours ago

Erik DarlingErik Darling

25.2k1313 gold badges7777 silver badges127127 bronze badges

answered 8 hours ago

Erik DarlingErik Darling

25.2k1313 gold badges7777 silver badges127127 bronze badges

answered 8 hours ago

Erik DarlingErik Darling

25.2k1313 gold badges7777 silver badges127127 bronze badges

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.

share|improve this answer

answered 8 hours ago

Kirk SaundersKirk Saunders

45722 silver badges1010 bronze badges

add a comment | 

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.

share|improve this answer

answered 8 hours ago

Kirk SaundersKirk Saunders

45722 silver badges1010 bronze badges

add a comment | 

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.

share|improve this answer

answered 8 hours ago

Kirk SaundersKirk Saunders

45722 silver badges1010 bronze badges

share|improve this answer

answered 8 hours ago

Kirk SaundersKirk Saunders

45722 silver badges1010 bronze badges

answered 8 hours ago

Kirk SaundersKirk Saunders

45722 silver badges1010 bronze badges

answered 8 hours ago

Kirk SaundersKirk Saunders

45722 silver badges1010 bronze badges