>>>>> "Pierre" == Pierre Ducroquet <p.psql@pinaraf.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)
