Asymmetry between parent and child wrt "false" quals

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().

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers