From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Pierre Ducroquet <p.psql@pinaraf.info>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: [Patch] optimizer - simplify $VAR1 IS NULL AND $VAR1 IS NOT NULL
In-Reply-To: <5177016.MnsixQzrC5@peanuts2> (Pierre Ducroquet's message of
	"Wed, 06 Nov 2019 18:41:23 +0100")
Message-ID: <87d0e4lw1a.fsf@news-spur.riddles.org.uk>
References: <5177016.MnsixQzrC5@peanuts2>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/26.3 (berkeley-unix)
Date: Wed, 06 Nov 2019 18:15:41 +0000
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

>>>>> "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)