From: Andrey Lepikhov <a.lepikhov@postgrespro.ru>
To: Hywel Carver <hywel@skillerwhale.com>
To: pgsql-hackers@lists.postgresql.org
Cc: Konstantin Knizhnik <knizhnik@garret.ru>
Subject: Re: Removing unneeded self joins
Date: Wed, 30 Jun 2021 14:21:15 +0300
Message-ID: <2a6e540a-4c9f-9402-2e02-71e35fcd4dd1@postgrespro.ru> (raw)
In-Reply-To: <161553990603.28624.7702383984090736980.pgcf@coridan.postgresql.org>
References: <64486b0b-0404-e39e-322d-0801154901f3@postgrespro.ru>
<21e15759-dba5-2d2b-dd03-06943ad40378@postgrespro.ru>
<161553990603.28624.7702383984090736980.pgcf@coridan.postgresql.org>
On 12/3/21 12:05, Hywel Carver wrote:
> I've built and tested this, and it seems to function correctly to me. One question I have is whether the added "IS NOT NULL" filters can be omitted when they're unnecessary. Some of the resulting plans included an "IS NOT NULL" filter on a non-nullable column. To be clear, this is still an improvement (to me) without that.
I think, here we could ask more general question: do we want to remove a
'IS NOT NULL' clause from the clause list if the rest of the list
implicitly implies it?
Right now we don't analyze list of clauses at all:
CREATE TABLE a (x int);
EXPLAIN (COSTS OFF)
SELECT * FROM a WHERE (x < 1) AND (X
IS NOT NULL) AND
(x < 1) AND (X IS NOT NULL);
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on a
Filter: ((x IS NOT NULL) AND (x IS NOT NULL) AND (x < 1) AND (x < 1))
And even worse:
EXPLAIN (ANALYZE, VERBOSE)
SELECT *
FROM a WHERE (X IS NOT NULL) AND (X IS NULL);
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on public.a (cost=0.00..15.00 rows=87 width=4) (actual
time=0.136..0.136 rows=0 loops=1)
Output: x
Filter: ((a.x IS NOT NULL) AND (a.x IS NULL))
Rows Removed by Filter: 1000
It could reduce a number of selectivity mistakes, but increase CPU
consumption.
If we had such a clause analyzing machinery, we could trivially remove
this unneeded qual.
--
regards,
Andrey Lepikhov
Postgres Professional
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: a.lepikhov@postgrespro.ru, hywel@skillerwhale.com, pgsql-hackers@lists.postgresql.org, knizhnik@garret.ru
Subject: Re: Removing unneeded self joins
In-Reply-To: <2a6e540a-4c9f-9402-2e02-71e35fcd4dd1@postgrespro.ru>
* 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