On Mon, Jul 7, 2014 at 7:37 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Ashutosh Bapat <ashutosh.ba...@enterprisedb.com> writes:
> > Right now, constraint exclusion code looks only at the provided
> conditions.
> > If we want avoid table scan based on constraints in the above example, it
> > will need to look at the implied conditions as well. E.g. val2 < 30 AND
> val
> > = val2 => val < 30. Then the constraint exclusion can see that val < 30
> > val > 30 are contradictory and infer that the result is going to be
> empty.
> > We will need to add information about the transitive inferences between
> > operators. Can we do that in PostgreSQL? Will the benefits be worth the
> > code, that needs to be added?
> I doubt it.  The extra code isn't the problem so much, it's the extra
> planning cycles spent trying to make proofs that will usually fail.
> What you propose will create a combinatorial explosion in the number
> of proof paths to be considered.

I can understand that it will create combinatorial explosion in the number
of predicates that need to be examined by the constraint exclusion. I do
not understand where come the paths gets involved here. The constraint
exclusion kicks in before paths are created

 220 static void
 221 set_rel_size(PlannerInfo *root, RelOptInfo *rel,
 222              Index rti, RangeTblEntry *rte)
 223 {
 224     if (rel->reloptkind == RELOPT_BASEREL &&
 225         relation_excluded_by_constraints(root, rel, rte))
 226     {
 227         /*
 228          * We proved we don't need to scan the rel via constraint
 229          * so set up a single dummy path for it.  Here we only check
this for
 230          * regular baserels; if it's an otherrel, CE was already
checked in
 231          * set_append_rel_pathlist().
 232          *
 233          * In this case, we go ahead and set up the relation's path
right away
 234          * instead of leaving it for set_rel_pathlist to do.  This is
 235          * we don't have a convention for marking a rel as dummy
except by
 236          * assigning a dummy path to it.
 237          */
 238         set_dummy_rel_pathlist(rel);
 239     }

 and does not create paths for relations excluded by constraints

 295 /*
 296  * set_rel_pathlist
 297  *    Build access paths for a base relation
 298  */
 299 static void
 300 set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
 301                  Index rti, RangeTblEntry *rte)
 302 {
 303     if (IS_DUMMY_REL(rel))
 304     {
 305         /* We already proved the relation empty, so nothing more to do
 306     }

Same in the case of join in mark_join_rel()

 663      * JOIN_INNER.
 664      */
 665     switch (sjinfo->jointype)
 666     {
 667         case JOIN_INNER:
 668             if (is_dummy_rel(rel1) || is_dummy_rel(rel2) ||
 669                 restriction_is_constant_false(restrictlist, false))
 670             {
 671                 mark_dummy_rel(joinrel);
 672                 break;
 673             }
 674             add_paths_to_joinrel(root, joinrel, rel1, rel2,
 675                                  JOIN_INNER, sjinfo,
 676                                  restrictlist);
 677             add_paths_to_joinrel(root, joinrel, rel2, rel1,
 678                                  JOIN_INNER, sjinfo,
 679                                  restrictlist);
 680             break;

BTW, on a side note, I noticed, we use mark_dummy_rel() for joinrels and
set_dummy_rel_pathlist() for baserel. Shouldn't we be using the same
function everywhere for the same functionality (e.g. adding an append path
with no child-paths).

For larger relations, the time spent in constraint exclusion might be
lesser than the time taken by actual table/index scan and that to when such
a scan is not going to produce any rows. So, we might want to apply the
technique only when the estimated number of rows/pages are above a certain
threshold and may be when the GUC is ON (like we do it today).

> > I can see some more benefits. We can use it to eliminate joins where the
> > constraints on joining relations may cause the join to be empty e.g.
> ... and applying constraint exclusion to join relations would make that
> problem even worse.
The same case goes with joins, where potentially, the crossproduct of two
tables is huge.

>                         regards, tom lane

Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Reply via email to