"Nikolay Samokhvalov" <[EMAIL PROTECTED]> writes: > I have a table "table1" with ~100k rows, the table having "flag1" > column. The value of "flag1" is NULL in 85k+ rows, and it's TRUE in > 7k+ rows, and FALSE in 6k rows.
Yeah, you're going to have some problems with so many NULLs, I'm sure. > -- so, there is a wrong assumption that for "var <> const" expressions > we may just use estimation for "var = const" and subtract it from 1. > In fact, NULLs are ignored here. According to ternary logic, in this > case we must subtract the number of NULLs also. This will improve row > estimation for "var <> const" queries (but not in case when we deal > with boolean datatype, look at (2)!). If there are no objections, I'll > send the patch, which is straightforward. It doesn't seem all that straightforward to me, unless your intent is to copy-and-paste all of eqsel(), which I wouldn't regard as a very acceptable solution. Otherwise you're going to need some refactoring. > 2). In case of "WHERE flag1 = FALSE" or "WHERE flag1 <> TRUE" the > planner rewrites the query to "WHERE NOT flag1" and then uses the > logic defined in backend/optimizer/path/clausesel.c, where, again, we > see the wrong approach which ignores NULLs: I think the only case where we could hope to improve that is where the argument is a simple bool variable --- but of course that's also the only case where we could've done much with the "flag1 = FALSE" form, so the rewriting isn't really hurting here. I'd suggest pushing the work into selfuncs.c and seeing if examine_variable can do anything with the argument. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers