Обсуждение: teaching Var about NOT NULL
Hi,
While hacking away at implementing join removal support for ANTI-JOINs I realised that I couldn't just replace the join with a WHERE false condition... Let me explain...
With a query such as:
SELECT * FROM a WHERE NOT EXISTS(SELECT 1 FROM b WHERE a.b_id = b.id);
Where a.b_id has a foreign key on b(id)
I'm working on a join removal patch which will turn this into:
SELECT * FROM a WHERE b_id IS NULL;
This seemed like a bit of a shame since with my test tables b_id is defined NOT NULL, but there seemed to be no way to tell if I needed to add a WHERE FALSE or a IS NULL check to the WHERE clause.
I quickly put together the attached patch which adds a "knownnotnull" bool field to Var which we can set to true when we're completely sure that the Var cannot contain any NULL values. I'm populating this from pg_attribute.attnotnull where I can and setting it to false where I can't see another way to tell for sure that nulls cannot exist.
The only use of knownnotnull that I've added to the patch is to turn a query such as:
SELECT * FROM a WHERE b_id IS NULL;
To not scan the table, since id is defined as NOT NULL.
postgres=# alter table a alter column b_id drop not null;
ALTER TABLE
postgres=# explain select id from a where b_id is null;
QUERY PLAN
---------------------------------------------------
Seq Scan on a (cost=0.00..31.40 rows=11 width=4)
Filter: (b_id IS NULL)
Planning time: 0.340 ms
(3 rows)
postgres=# alter table a alter column b_id set not null;
ALTER TABLE
postgres=# explain select id from a where b_id is null;
QUERY PLAN
--------------------------------------------------------
Result (cost=0.00..31.40 rows=1 width=4)
One-Time Filter: false
-> Seq Scan on a (cost=0.00..31.40 rows=1 width=4)
Planning time: 0.402 ms
(4 rows)
Having this extra flag could likely help optimise NOT IN(SELECT notnullcol FROM table) to allow this to become an ANTI-JOIN. It will also help join optimise join removals a little more.
The patch is just a few minutes old and there's no regression tests yet. I'd rather have some feedback before I proceed with it.
Regards
David Rowley
Вложения
David Rowley <dgrowleyml@gmail.com> writes:
> I quickly put together the attached patch which adds a "knownnotnull" bool
> field to Var which we can set to true when we're completely sure that the
> Var cannot contain any NULL values.
This is utterly the wrong way to go about it. How will you update views
containing such Vars, when someone does an ALTER TABLE SET/DROP NOT NULL?
The right thing is for the planner to look *at plan time* to see if the
column is marked NOT NULL. (This is safe against the ALTER TABLE problem
because an ALTER will result in a relcache invalidation signal, forcing
any plans referencing the table to be regenerated before next use.)
One way to go about it would be to add a bool array to RelOptInfo and
teach plancat.c to populate the array. However, that would only be a win
if we could expect the information to get used (preferably multiple times)
in most planning attempts. That does not seem to me to be likely for this
use case, so I'd go with just doing a pg_attribute catcache lookup
on-the-fly when necessary. I'd suggest
(1) new lsyscache.c utility function taking a relation OID and an attnum;
(2) new function somewhere in the planner that decides whether an
expression is known not-null. For a Var, it'd fetch the matching RTE,
see if it's RTE_RELATION, and if so call the lsyscache.c function.
There are a lot of other potential cases that such a function could be
taught about later, if it proves useful.
BTW, you'd need to be pretty careful about semantics here. Even if
the Var is known not-null at the point of scanning the relation,
what if that relation is nullable by some upper outer join? Perhaps
the function (2) would need to take an argument describing the
join level at which we're making the test.
regards, tom lane