Tomas Vondra <tv@xxxxxxxx> writes: > I've been fighting with some CTE queries recently, and in the end I've > ended up with two basic cases. In one case the CTEs work absolutely > great, making the estimates much more precise, while in the other the > results are pretty terrible. And I'm not sure why both of these behave > the way they do. You're assuming the case where the estimate is better is better for a reason ... but it's only better as a result of blind dumb luck. The outer-level query planner doesn't know anything about the CTE's output except the estimated number of rows --- in particular, it doesn't drill down to find any statistics about the join column. So what you're getting there is a default selectivity estimate that just happens to match reality in this case. (If you work through the math in eqjoinsel_inner for the case where the relation sizes are grossly different and we don't have MCV stats, you'll find that it comes out to be assuming that each row in the larger relation has one join partner in the smaller one, which indeed is your situation here.) In the other example, you're likewise getting a default selectivity estimate, only it doesn't match so well, because the default does not include assuming that the join keys are unique on both sides. Without the CTEs, the optimizer can see the keys are unique so it makes the right selectivity estimate. In principle we could make the optimizer try to drill down for stats, which would make these examples work the same with or without the CTE layers. I'm not sure it's worth the trouble though --- I'm dubious that people would use a CTE for cases that are simple enough for the stats estimates to be worth anything. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
