AI row count estimates arrive with tremendous confidence. Cardinal said four hundred. The query returned nine million.
Cardinal Rules. A Saturday cartoon about AI and databases.
Two things in your day estimate row counts, and only one of them has ever looked at your data.
The optimizer builds its guess from statistics. It keeps a small summary of what lives in each column, how many rows there are and how the values spread out. That guess still goes wrong, usually because the summary is old and the table has doubled since. Known problem, known fix.
Cardinal has none of that. Cardinal has read a great many sentences about databases. When you ask how many rows come back, the answer gets assembled out of how such answers usually sound. Four hundred sounds like a reasonable number of rows. It sounds reasonable for every table on earth, and that is the tell.
Try this the next time a model hands you a confident number. Ask how it knows. A real estimate can point at something, a statistics object, a sample, a count it actually ran. A guess starts explaining its reasoning in a lovely paragraph and never points at anything.
If you want the true number, the database is sitting right there. It will count for you. Counting is the one thing it has always been extremely good at.
Cardinal Rule
The machine never says maybe. That part is still your job.
Ask how it knows, then watch whether anything gets pointed at.
Cardinal says the estimate was directionally correct. On the desk this season: AI: Nobody’s in There. But we’re still in here. (on Amazon) and 100 AI Interview Questions and Answers for Data Professionals (Kindle, also on Amazon.in, paperback and audiobook).
Next Saturday, Cardinal reads the cover of one of my books and takes it personally.
A confident number is not a measurement, it is a suggestion in a nice font.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
4 Comments. Leave new
im guessing AI would come up with the cardinality estimates when you feed it the query and all statistics
However there is only one 8k page reserved for statistics and its buckets on which cardinality is estimated. The bigger the table gets the more the data gets spread out over each bucket and the less accurate the estimates will be is what im suspecting, just like computing statistics with a smaller sample size. It will leave more room for uncaptured variance in the data and therefore more chance on inaccurate estimates.
However SQL Servers cardinality estimates gave me numbers of rows that exceeded the amount i would get with a Cartesian product at certain times, even with full sampled statistics. No SQL Server, joining a table of 2 rows with one holding 3 rows doesnt produce 25 rows and never can be, not even when you remove the WHERE clause.
Thank you, Martien. You make a good point: stale statistics are only one reason estimates go wrong. Even fresh FULLSCAN statistics do not guarantee accurate join estimates.
The key histogram limit is 200 steps per statistics object, on its leading column. FULLSCAN removes sampling uncertainty, but it does not remove histogram compression or the estimator’s assumptions about data relationships. More distinct values, skew, and correlation can make estimation harder; a larger row count alone does not necessarily mean worse estimates.
You are right about the arithmetic: a simple inner join between two rows and three rows can return at most six rows. An estimate of 25 would need the query and execution plan to explain. I would check the estimated input rows and whether the displayed figure is per execution or across repeated executions before attributing it to a particular cause.
Giving AI the query, schema, and statistics certainly gives it something concrete to work with. My concern in this cartoon is a confident number supplied without that evidence. Even with evidence, an estimate is still an estimate, and the actual result is the check.
The problem i mentioned about with the estmates larger than a cartesian product happend on a DWH query probbaly generated by a tool. I was called in to investigate as the DWH jobs regularly broke during the night. I did a FULLSCAN statistics gathering on all tables (indexes get automatically new statistics with FULLSCAN sampling of the tables only) on my test environment. I found out the actual memory consumption was 10% of the allocated memory and as this was a large generated DWH query with 30 joins it took 25% off all memory. More big queries were started and all available memeory got consumed and the buffer cache for the pages got wiped out. Maybe due to this the queries couldnt finish in time and other big ones exceeding their maximum 20 minute wait time just got aborted resulting in the entire job failing.
I found the way over estimate in rows at that time too and as a test i changed all the left outer joins into inner joins and the estimates were much better. Memory allocation was also much better. As a shortfix i advised the DWH Team not to start multiple jobs at the same time.
We had Microsoft support on this but i couldnt convince the egineer from Microsoft this was truely a bug.He was aiming for adaptable queries. The fact that the query was generated with hardcoded DATE values didnt help that much. I did see that forced parametrisation was used in another database but this still genetated multiple different plans for what should have been one. I later learned that that was probably diue to parameter value length but i didnt research tnat. For generated dates that shouldnt be a problem as their length should be the same. However in the DWH issue my opinion was that this overestimating still was a bug and would have rather seen that recognized by Microsoft instead of going for the workaround immediately
I havent tried to reproduce the issue later on though
Martien, thank you for sharing such a detailed real-world example.
Your case is a great illustration of how cardinality estimates, memory grants, and concurrency can interact in a large DWH workload. Even with FULLSCAN statistics, estimation can become difficult when a generated query has many joins and complex intermediate result sets.
The improvement you saw after changing LEFT JOINs to INNER JOINs is particularly interesting. Of course, that change is only appropriate when the query logic allows it, but it can simplify estimation and lead to very different memory grants.
I also agree that with this type of issue, it is useful to trace the execution plan and identify where the estimated and actual row counts first start to diverge significantly.
Thank you again for sharing the experience. It is a very useful example.