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

Поиск
Список
Период
Сортировка
От Richard Guo
Тема Check each of base restriction clauses for constant-FALSE-or-NULL
Дата
Msg-id CAMbWs4_x3-CnVVrCboS1LkEhB5V+W7sLSCabsRiG+n7+5_kqbg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Check each of base restriction clauses for constant-FALSE-or-NULL  (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>)
Re: Check each of base restriction clauses for constant-FALSE-or-NULL  (David Rowley <dgrowleyml@gmail.com>)
Список 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
Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Thomas Munro
Дата:
Сообщение: CREATE DATABASE with filesystem cloning
Следующее
От: Michał Kłeczek
Дата:
Сообщение: Draft LIMIT pushdown to Append and MergeAppend patch