Re: [HACKERS] Asymmetry between parent and child wrt "false" quals - Mailing list pgsql-hackers

On Tue, Mar 21, 2017 at 2:19 PM, Amit Langote
<Langote_Amit_f8@lab.ntt.co.jp> wrote:
> On 2017/03/21 14:59, Ashutosh Bapat wrote:
>> When I run a query like below on a child-less table, the plan comes out to be
>>
>> explain verbose SELECT * FROM uprt1_l WHERE a = 1 AND a = 2;
>>                               QUERY PLAN
>> ----------------------------------------------------------------------
>>  Result  (cost=0.00..11.50 rows=1 width=13)
>>    Output: a, b, c
>>    One-Time Filter: false
>>    ->  Seq Scan on public.uprt1_l  (cost=0.00..11.50 rows=1 width=13)
>>          Output: a, b, c
>>          Filter: (uprt1_l.a = 1)
>> (6 rows)
>>
>> where as the same query run on a parent with children, the plan is
>> postgres=# \d prt1_l
>>                     Table "public.prt1_l"
>>  Column |       Type        | Collation | Nullable | Default
>> --------+-------------------+-----------+----------+---------
>>  a      | integer           |           | not null |
>>  b      | integer           |           |          |
>>  c      | character varying |           |          |
>> Partition key: RANGE (a)
>> Number of partitions: 3 (Use \d+ to list them.)
>>
>> postgres=# explain verbose SELECT * FROM prt1_l WHERE a = 1 AND a = 2;
>>                 QUERY PLAN
>> -------------------------------------------
>>  Result  (cost=0.00..0.00 rows=0 width=40)
>>    Output: prt1_l.a, prt1_l.b, prt1_l.c
>>    One-Time Filter: false
>> (3 rows)
>>
>> For a parent table with children, set_append_rel_size() evaluates
>> restrictions in loop
>>  880     foreach(l, root->append_rel_list)
>>  881     {
>>  882         AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
>>
>> starting at 1021. If any of the restrictions are evaluated to false,
>> it set the child as dummy. If all children are dummy, the appendrel is
>> set to dummy.
>>
>> But for a child-less table, even if the "false" qual is available in
>> baserestrictinfo in set_rel_size(), we do not mark the relation as
>> dummy. Instead, paths are created for it and only at the time of
>> planning we add the gating plan when there is a pseudo constant quals.
>> Why do we have different behaviours in these two cases?
>
> I think the case where there is no child table would not be handled by
> set_append_rel_size(), because rte->inh would be false.  Instead, I
> thought the test at the beginning of relation_excluded_by_constraints()
> would have detected this somehow; the comment there says the following:
>
> /*
>  * 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.
>
> But the qual (a = 1 and a = 2) is *not* reduced to exactly one
> constant-false-or-null baserestrictinfo entry; instead I see that there
> are two RestrictInfos viz. a = 1 and const-FALSE at that point.  I think
> the const-folding mentioned in the above comment does not occur after
> equivalence class processing, which would be required to conclude that (a
> = 1 and a = 2) reduces to constant-false.  OTOH, (a = 1 and false) can be
> reduced to constant-false much earlier when performing
> preprocess_qual_conditions().

Right.

>
> That said, I am not sure if it's worthwhile to modify the test at the
> beginning of relation_excluded_by_constraints() to iterate over
> rel->baserestrictinfos to look for any const-FALSE quals, instead of doing
> it only when there *only* the const-FALSE qual.
>
I don't think we should do it in relation_excluded_by_constraints().
We should do it outside like what is being done in
set_append_rel_size(). Probably we should extract common code into a
function and call it for both kinds of relations.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company