>> I am wondering, why the historical data (real result of the query) does
not affect the execution plan. For example, If I ran the query 10 times I
always get around 6500 instead of 5.5 million in the top most execution plan
node.
The likely accurate, if cheeky, answer is simply that no one has taken the
time to implement what at face value would seem to be an extremely complex
behavior.
As to the rest you should probably gather up some additional details and
post this over on performance. There is likely a middle ground between your
idea and the status quo but that ground cannot be evaluated unless examples
of possibly misbehaving queries are provided.
David J.