Re: [Patch] optimizer - simplify $VAR1 IS NULL AND $VAR1 IS NOT NULL

>>>>> "Pierre" == Pierre Ducroquet <p(dot)psql(at)pinaraf(dot)info> writes:

Pierre> Hello

Pierre> In several queries relying on views, I noticed that the
Pierre> optimizer miss a quite simple to implement optimization. My
Pierre> views contain several branches, with different paths that are
Pierre> simplified by the caller of the view. This simplification is
Pierre> based on columns to be null or not.

Pierre> Today, even with a single table, the following (silly) query is
Pierre> not optimized away:

Pierre> SELECT * FROM test WHERE a IS NULL AND a IS NOT NULL;

Actually it can be, but only if you set constraint_exclusion=on (rather
than the default, 'partition').

postgres=# explain select * from foo where id is null and id is not null;
QUERY PLAN
-----------------------------------------------------
Seq Scan on foo (cost=0.00..35.50 rows=13 width=4)
Filter: ((id IS NULL) AND (id IS NOT NULL))
(2 rows)

postgres=# set constraint_exclusion=on;
SET

postgres=# explain select * from foo where id is null and id is not null;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)

In fact when constraint_exclusion=on, the planner should detect any case
where some condition in the query refutes another condition. There is
some downside, though, which is why it's not enabled by default:
planning may take longer.

Pierre> The attached patch handles both situations. When flattening and
Pierre> simplifying the AND clauses, a list of the NullChecks is built,
Pierre> and subsequent NullChecks are compared to the list. If opposite
Pierre> NullChecks on the same variable are found, the whole AND is
Pierre> optimized away.

That's all very well but it's very specific to a single use-case. The
existing code, when you enable it, can detect a whole range of possible
refutations (e.g. foo > 1 AND foo < 1).

--
Andrew (irc:RhodiumToad)