Thread: [HACKERS] Asymmetry between parent and child wrt "false" quals
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 loop880 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? Following
comment in set_append_rel_size() doesn't explain why it's done for
child but not for parent.
1000 * The child rel's targetlist might contain non-Var
expressions, which
1001 * means that substitution into the quals could produce
opportunities
1002 * for const-simplification, and perhaps even pseudoconstant quals.
1003 * Therefore, transform each RestrictInfo separately to see if it
1004 * reduces to a constant or pseudoconstant. (We must process them
1005 * separately to keep track of the security level of each qual.)
1006 */
Why do we want to create paths for the relation which we know is not
going to produce any result?
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
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-foldingwill* reduce "anything AND FALSE" to just "FALSE", any such case should* result in exactly one
baserestrictinfoentry.
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().
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.
Thanks,
Amit
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