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
Date: Wed, 06 Nov 2019 18:15:41 +0000
Message-ID: <87d0e4lw1a.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <5177016.MnsixQzrC5@peanuts2>
References: <5177016.MnsixQzrC5@peanuts2>
>>>>> "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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andrew@tao11.riddles.org.uk, p.psql@pinaraf.info, pgsql-hackers@lists.postgresql.org
Subject: Re: [Patch] optimizer - simplify $VAR1 IS NULL AND $VAR1 IS NOT NULL
In-Reply-To: <87d0e4lw1a.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox