Re: [HACKERS] Asymmetry between parent and child wrt "false" quals
On Tue, Mar 21, 2017 at 2:19 PM, Amit Langote 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
Re: [HACKERS] 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
[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 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? 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers