Re: Row estimation for "var <> const" and for "NOT (...)" queries

"Nikolay Samokhvalov" <samokhvalov(at)gmail(dot)com> 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