Tomas Vondra <tv@xxxxxxxx> writes: > Results from (A) and (B) seem strange to me because AFAIK there are no > multi-column statistics available, and accoring to this thread > http://archives.postgresql.org/pgsql-hackers/2009-03/msg00052.php > the single-column estimates are not multiplied (which would be OK only > in case of statistically independent columns). You're misreading that thread: it's discussing row inequality comparisons, as in your example (D). Row equality comparisons are the same as a bunch of per-column equality comparisons, which is why (A) and (B) behave the same, and for that you *will* get a multiplication of the assumed-independent clause selectivities. > I'm not quite sure why (C) has an estimate of 1. It's smart enough to see that each of the clauses is a range constraint on the variable, so you get fairly tight estimates on the number of matches ... and then those two small selectivities are multiplied together. It does not however notice that the range bounds are actually equal, which would allow it to convert the estimate to a simple equality estimate, which in many cases (including this one) would be better. I think we've discussed special-casing that, but it doesn't look like anybody got around to it yet. It's a little bit tricky to do because the range estimator doesn't really distinguish < from <= --- which normally doesn't matter a lot, but it does when you're considering "x >= 33 and x <= 33" versus "x > 33 and x < 33". > And I do have exactly the same problem with the estimate in (D). Where > the heck did 227232 come from? It doesn't recognize that this case is a range comparison (which was a point made in the thread you cited). So you get a dumb multiplication of the selectivities for col_a >= 33 and col_a <= 33. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
