From: | Richard Guo <guofenglinux(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Check each of base restriction clauses for constant-FALSE-or-NULL |
Date: | 2023-10-07 09:39:48 |
Message-ID: | CAMbWs4_x3-CnVVrCboS1LkEhB5V+W7sLSCabsRiG+n7+5_kqbg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
In relation_excluded_by_constraints() when we're trying to figure out
whether the relation need not be scanned, one of the checks we do is to
detect constant-FALSE-or-NULL restriction clauses. Currently we perform
this check only when there is exactly one baserestrictinfo entry, and
the comment explains this as below.
* Regardless of the setting of constraint_exclusion, detect
* constant-FALSE-or-NULL restriction clauses. Because const-folding will
* reduce "anything AND FALSE" to just "FALSE", any such case should
* result in exactly one baserestrictinfo entry.
This doesn't seem entirely correct, because equivclass.c may generate
constant-FALSE baserestrictinfo entry on the fly. In addition, other
quals could get pushed down to the baserel. All these cases would
result in that the baserestrictinfo list might possibly have other
members besides the FALSE constant.
So I'm wondering if we should check each of base restriction clauses for
constant-FALSE-or-NULL quals, like attached.
Here are some examples.
-- #1 constant-FALSE generated by ECs
-- unpatched (in all branches)
explain (costs off) select * from t t1 where a = 1 and a = 2;
QUERY PLAN
--------------------------
Result
One-Time Filter: false
-> Seq Scan on t t1
Filter: (a = 1)
(4 rows)
-- patched
explain (costs off) select * from t t1 where a = 1 and a = 2;
QUERY PLAN
--------------------------
Result
One-Time Filter: false
(2 rows)
-- #2 other quals get pushed down to the baserel
-- unpatched (in 15 and earlier)
explain (costs off)
select * from t t1 left join (select * from t t2 where false) s on s.a = 1;
QUERY PLAN
--------------------------------------
Nested Loop Left Join
-> Seq Scan on t t1
-> Materialize
-> Result
One-Time Filter: false
-> Seq Scan on t t2
Filter: (a = 1)
(7 rows)
-- patched
explain (costs off)
select * from t t1 left join (select * from t t2 where false) s on s.a = 1;
QUERY PLAN
--------------------------------
Nested Loop Left Join
-> Seq Scan on t t1
-> Result
One-Time Filter: false
(4 rows)
I'm a little concerned that it will bring some overhead to loop through
the baserestrictinfo list. But considering that other codes in the same
function also loops through the list, maybe I'm worrying over nothing.
Any thoughts?
Thanks
Richard
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Check-each-of-base-restriction-clauses-for-constant-FALSE.patch | application/octet-stream | 3.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Michał Kłeczek | 2023-10-07 10:01:17 | Draft LIMIT pushdown to Append and MergeAppend patch |
Previous Message | Thomas Munro | 2023-10-07 05:51:45 | CREATE DATABASE with filesystem cloning |