Estimated Rows is 1

Abdel 2015-03-27 10:32:52

This query is giving me an ESTIMATED NUMBER OF ROWS = 1 and due to this it performs realy slow. Can someone point out for me the missing index that will make it stop doing that wrong estimate calculation?

Thanks in advance

-A

SQLkiwi 2015-03-27 12:36:14
>Can someone point out for me the missing index that will make it stop doing that wrong estimate calculation?

I doubt this is possible. Cardinality estimation errors are all but inevitable with deep join trees. Even small-ish errors will tend to propagate and increase in severity as the number of joins and other estimate-affecting operations accumulate. You can see this effect for yourself by following the estimate versus actual variations from the deepest point of the tree upward.

The query in the question is a moderately complex series of joins, predicates, and aggregations, which are a tough enough proposition for the cardinality estimation logic before we take into account the fact that half of the objects referenced are views containing yet more joins and predicates.

The sudden drop in actual row counts at the final aggregate strongly suggests that the preceding logic could be improved to compute at least partial aggregates earlier, or otherwise significantly reduce the row counts through de-duplication. Given an infinite amount of time and resources, the query optimizer might be able to find a plan that pushed down the aggregations, but it knows you probably don't want to wait for that, so it times out.

In case that seems unreasonable, consider there are 14 joins alone in the view-expanded query. If the optimizer considered just the possible ways to perform the joins, it would have to look at 64,764,752,532,480,000 (6.4 quadrillion) combinations. That's without considering the placement of aggregates and the possible introduction of partial aggregates.

In summary, there is no easy answer here. The fastest path to improvement is probably through refactoring the query: expressing it in terms that explicitly introduce partial aggregation or other optimizations that would reduce row counts earlier. That sort of task would require an expert with direct access to the system, design, and business requirement behind the query.

Abdel 2015-03-27 15:00:26
"That sort of task would require an expert with direct access to the system, design, and business requirement behind the query."
-That expert is me 🙂 but this one is simply driving me mad trying to figure out…
SQLkiwi 2015-03-31 13:18:15
Well good luck. I have given you as much of an answer as I can from here.