Check each of base restriction clauses for constant-FALSE-or-NULL

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

Responses

Browse pgsql-hackers by date

  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