postgres_fdw优化案例--GUC控制join pushdown

postgres=> explain (analyze,verbose) select distinct a.sid,a.ip,a.port,a.type from tbl a join inf b on a.sid::text=b.id::text where a.ext=1;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=452.77..452.86 rows=1 width=328) (actual time=203472.078..203472.100 rows=28 loops=1)
   Output: a.sid, a.ip, a.port, a.type
   ->  Sort  (cost=452.77..452.79 rows=7 width=328) (actual time=203472.076..203472.080 rows=28 loops=1)
         Output: a.sid, a.ip, a.port, a.type
         Sort Key: a.sid, a.ip, a.port, a.type
         Sort Method: quicksort  Memory: 26kB
         ->  Foreign Scan  (cost=100.00..452.68 rows=7 width=328) (actual time=25657.993..203471.879 rows=28 loops=1)
               Output: a.sid, a.ip, a.port, a.type
               Filter: ((a.sid)::text = (b.id)::text)
               Rows Removed by Filter: 50474621
               Relations: (public.tbl a) INNER JOIN (public.inf b)
               Remote SQL: SELECT r1.sid, r1.ip, r1.port, r1.type, r2.id FROM (public.tbl r1 INNERJOIN public.inf r2 ON (((r1.ext = 1))))
 Planning Time: 0.256 ms
 Execution Time: 203489.909 ms
(14rows)

postgres=> 

testdb=> explain analyze select distinct a.sid,a.ip,a.port,a.type from tbl a join inf b on a.sid::text=b.id::text where a.ext=1;
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=24036.30..24198.05 rows=1294 width=34) (actual time=16.465..17.169 rows=28 loops=1)
   ->  Gather Merge  (cost=24036.30..24185.11rows=1294 width=34) (actual time=16.464..17.152rows=28 loops=1)
         Workers Planned: 1
         Workers Launched: 1
         ->  Sort  (cost=23036.29..23039.52rows=1294 width=34) (actual time=13.913..13.917rows=14 loops=2)
               SortKey: a.sid, a.ip, a.port, a.type
               Sort Method: quicksort  Memory: 25kB
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               ->  HashAggregate  (cost=22956.46..22969.40rows=1294 width=34) (actual time=13.835..13.853rows=14 loops=2)
                     GroupKey: a.sid, a.ip, a.port, a.type
                     Batches: 1MemoryUsage: 73kB
                     Worker 0:  Batches: 1MemoryUsage: 73kB
                     ->  HashJoin  (cost=94.34..21471.91rows=148455 width=34) (actual time=5.451..13.809rows=14 loops=2)
                           Hash Cond: ((b.id)::text = (a.sid)::text)
                           ->  Parallel Seq Scanon inf b  (cost=0.00..1260.92rows=17292 width=4) (actual time=0.007..2.763rows=14698 loops=2)
                           ->  Hash  (cost=72.88..72.88rows=1717 width=34) (actual time=2.448..2.448rows=1717 loops=2)
                                 Buckets: 2048  Batches: 1MemoryUsage: 133kB
                                 ->  Seq Scanon tbl a  (cost=0.00..72.88rows=1717 width=34) (actual time=0.016..1.169rows=1717 loops=2)
                                       Filter: (ext = 1)
                                       Rows Removed by Filter: 1633
 Planning Time: 0.242 ms
 Execution Time: 17.311 ms
(22rows)

static bool
foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
    RelOptInfo *outerrel, RelOptInfo *innerrel,
    JoinPathExtraData *extra)
{
 PgFdwRelationInfo *fpinfo;
 PgFdwRelationInfo *fpinfo_o;
 PgFdwRelationInfo *fpinfo_i;
 ListCell   *lc;
 List    *joinclauses;

/* 1）
  * We support pushing down INNER, LEFT, RIGHT, FULL OUTER and SEMI joins.
  * Constructing queries representing ANTI joins is hard, hence not
  * considered right now.
  */
if (jointype != JOIN_INNER && jointype != JOIN_LEFT &&
  jointype != JOIN_RIGHT && jointype != JOIN_FULL &&
  jointype != JOIN_SEMI)
return false;

/* 2）
  * We can't push down semi-join if its reltarget is not safe
  */
if ((jointype == JOIN_SEMI) && !semijoin_target_ok(root, joinrel, outerrel, innerrel))
return false;

/* 3）
  * If either of the joining relations is marked as unsafe to pushdown, the
  * join can not be pushed down.
  */
 fpinfo = (PgFdwRelationInfo *) joinrel->fdw_private;
 fpinfo_o = (PgFdwRelationInfo *) outerrel->fdw_private;
 fpinfo_i = (PgFdwRelationInfo *) innerrel->fdw_private;
if (!fpinfo_o || !fpinfo_o->pushdown_safe ||
  !fpinfo_i || !fpinfo_i->pushdown_safe)
return false;

/* 4）
  * If joining relations have local conditions, those conditions are
  * required to be applied before joining the relations. Hence the join can
  * not be pushed down.
  */
if (fpinfo_o->local_conds || fpinfo_i->local_conds)
return false;

/*省略*/
/* 5) */
 joinclauses = NIL;
 foreach(lc, extra->restrictlist)
 {  
  RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
bool  is_remote_clause = is_foreign_expr(root, joinrel,
                rinfo->clause);

if (IS_OUTER_JOIN(jointype) &&
   !RINFO_IS_PUSHED_DOWN(rinfo, joinrel->relids))
  {
   if (!is_remote_clause)
    return false;
   joinclauses = lappend(joinclauses, rinfo);
  }
else
  {
   if (is_remote_clause)
    fpinfo->remote_conds = lappend(fpinfo->remote_conds, rinfo);
   else
    fpinfo->local_conds = lappend(fpinfo->local_conds, rinfo);
  }
 }
 /* 6) */
 /*
  * deparseExplicitTargetList() isn't smart enough to handle anything other
  * than a Var.  In particular, if there's some PlaceHolderVar that would
  * need to be evaluated within this join tree (because there's an upper
  * reference to a quantity that may go to NULL as a result of an outer
  * join), then we can't try to push the join down because we'll fail when
  * we get to deparseExplicitTargetList().  However, a PlaceHolderVar that
  * needs to be evaluated *at the top* of this join tree is OK, because we
  * can do that locally after fetching the results from the remote side.
  */
 foreach(lc, root->placeholder_list)
 {
  PlaceHolderInfo *phinfo = lfirst(lc);
  Relids  relids;

/* PlaceHolderInfo refers to parent relids, not child relids. */
  relids = IS_OTHER_REL(joinrel) ?
   joinrel->top_parent_relids : joinrel->relids;

if (bms_is_subset(phinfo->ph_eval_at, relids) &&
   bms_nonempty_difference(relids, phinfo->ph_eval_at))
   return false;
 }

/* 省略 */
}

void
add_paths_to_joinrel(PlannerInfo *root,
      RelOptInfo *joinrel,
      RelOptInfo *outerrel,
      RelOptInfo *innerrel,
      JoinType jointype,
      SpecialJoinInfo *sjinfo,
      List *restrictlist)
{
 JoinPathExtraData extra;
bool  mergejoin_allowed = true;
 ListCell   *lc;
 Relids  joinrelids;
    
 /* 省略 */
 /*
  * Find potential mergejoin clauses.  We can skip this if we are not
  * interested in doing a mergejoin.  However, mergejoin may be our only
  * way of implementing a full outer join, so override enable_mergejoin if
  * it's a full join.
  */
if (enable_mergejoin || jointype == JOIN_FULL)
  extra.mergeclause_list = select_mergejoin_clauses(root,
                joinrel,
                outerrel,
                innerrel,
                restrictlist,
                jointype,
                &mergejoin_allowed);

 /*
  * If it's SEMI, ANTI, or inner_unique join, compute correction factors
  * for cost estimation.  These will be the same for all paths.
  */
if (jointype == JOIN_SEMI || jointype == JOIN_ANTI || extra.inner_unique)
  compute_semi_anti_join_factors(root, joinrel, outerrel, innerrel,
            jointype, sjinfo, restrictlist,
            &extra.semifactors);
/* 省略 */

 /*
  * 1. Consider mergejoin paths where both relations must be explicitly
  * sorted.  Skip this if we can't mergejoin.
  */
if (mergejoin_allowed)
  sort_inner_and_outer(root, joinrel, outerrel, innerrel,
        jointype, &extra);

 /*
  * 2. Consider paths where the outer relation need not be explicitly
  * sorted. This includes both nestloops and mergejoins where the outer
  * path is already ordered.  Again, skip this if we can't mergejoin.
  * (That's okay because we know that nestloop can't handle
  * right/right-anti/full joins at all, so it wouldn't work in the
  * prohibited cases either.)
  */
if (mergejoin_allowed)
  match_unsorted_outer(root, joinrel, outerrel, innerrel,
        jointype, &extra);
/* 省略 */
#ifdef NOT_USED

 /*
  * 3. Consider paths where the inner relation need not be explicitly
  * sorted.  This includes mergejoins only (nestloops were already built in
  * match_unsorted_outer).
  *
  * Diked out as redundant 2/13/2000 -- tgl.  There isn't any really
  * significant difference between the inner and outer side of a mergejoin,
  * so match_unsorted_inner creates no paths that aren't equivalent to
  * those made by match_unsorted_outer when add_paths_to_joinrel() is
  * invoked with the two rels given in the other order.
  */
if (mergejoin_allowed)
  match_unsorted_inner(root, joinrel, outerrel, innerrel,
        jointype, &extra);
#endif

 /*
  * 4. Consider paths where both outer and inner relations must be hashed
  * before being joined.  As above, disregard enable_hashjoin for full
  * joins, because there may be no other alternative.
  */
if (enable_hashjoin || jointype == JOIN_FULL)
  hash_inner_and_outer(root, joinrel, outerrel, innerrel,
        jointype, &extra);

 /*
  * 5. If inner and outer relations are foreign tables (or joins) belonging
  * to the same server and assigned to the same user to check access
  * permissions as, give the FDW a chance to push down joins.
  */
if (joinrel->fdwroutine &&
  joinrel->fdwroutine->GetForeignJoinPaths)
  joinrel->fdwroutine->GetForeignJoinPaths(root, joinrel,
             outerrel, innerrel,
             jointype, &extra);

 /*
  * 6. Finally, give extensions a chance to manipulate the path list.  They
  * could add new paths (such as CustomPaths) by calling add_path(), or
  * add_partial_path() if parallel aware.  They could also delete or modify
  * paths added by the core code.
  */
if (set_join_pathlist_hook)
  set_join_pathlist_hook(root, joinrel, outerrel, innerrel,
          jointype, &extra);
}

bool
is_foreign_expr(PlannerInfo *root,
    RelOptInfo *baserel,
    Expr *expr)
{
 foreign_glob_cxt glob_cxt;
 foreign_loc_cxt loc_cxt;
 PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) (baserel->fdw_private);

 /*
  * Check that the expression consists of nodes that are safe to execute
  * remotely.
  */
 glob_cxt.root = root;
 glob_cxt.foreignrel = baserel;

 /*
  * For an upper relation, use relids from its underneath scan relation,
  * because the upperrel's own relids currently aren't set to anything
  * meaningful by the core code.  For other relation, use their own relids.
  */
if (IS_UPPER_REL(baserel))
  glob_cxt.relids = fpinfo->outerrel->relids;
else
  glob_cxt.relids = baserel->relids;
 loc_cxt.collation = InvalidOid;
 loc_cxt.state = FDW_COLLATE_NONE;
/* 1) */
if (!foreign_expr_walker((Node *) expr, &glob_cxt, &loc_cxt, NULL))
return false;

 /*
  * If the expression has a valid collation that does not arise from a
  * foreign var, the expression can not be sent over.
  */
 /* 2) */
if (loc_cxt.state == FDW_COLLATE_UNSAFE)
return false;

 /*
  * An expression which includes any mutable functions can't be sent over
  * because its result is not stable.  For example, sending now() remote
  * side could cause confusion from clock offsets.  Future versions might
  * be able to make this choice with more granularity.  (We check this last
  * because it requires a lot of expensive catalog lookups.)
  */
/* 3) */
if (contain_mutable_functions((Node *) expr))
return false;

/* OK to evaluate on the remote server */
return true;
}

static bool
foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
    RelOptInfo *outerrel, RelOptInfo *innerrel,
    JoinPathExtraData *extra)
{
/* 省略 */
/*
  * Pull the other remote conditions from the joining relations into join
  * clauses or other remote clauses (remote_conds) of this relation
  * wherever possible. This avoids building subqueries at every join step.
  *
  * For an inner join, clauses from both the relations are added to the
  * other remote clauses. For LEFT and RIGHT OUTER join, the clauses from
  * the outer side are added to remote_conds since those can be evaluated
  * after the join is evaluated. The clauses from inner side are added to
  * the joinclauses, since they need to be evaluated while constructing the
  * join.
  *
  * For SEMI-JOIN clauses from inner relation can not be added to
  * remote_conds, but should be treated as join clauses (as they are
  * deparsed to EXISTS subquery, where inner relation can be referred). A
  * list of relation ids, which can't be referred to from higher levels, is
  * preserved as a hidden_subquery_rels list.
  *
  * For a FULL OUTER JOIN, the other clauses from either relation can not
  * be added to the joinclauses or remote_conds, since each relation acts
  * as an outer relation for the other.
  *
  * The joining sides can not have local conditions, thus no need to test
  * shippability of the clauses being pulled up.
  */
      switch (jointype)
      {
          case JOIN_INNER:
              fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
                                                 fpinfo_i->remote_conds);
              fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
                                                 fpinfo_o->remote_conds);
              break;

          case JOIN_LEFT:

              /*
               * When semi-join is involved in the inner or outer part of the
               * left join, it's deparsed as a subquery, and we can't refer to
               * its vars on the upper level.
               */
              if (bms_is_empty(fpinfo_i->hidden_subquery_rels))
                  fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
                                                    fpinfo_i->remote_conds);
              if (bms_is_empty(fpinfo_o->hidden_subquery_rels))
                  fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
                                                     fpinfo_o->remote_conds);
              break;

          case JOIN_RIGHT:

              /*
               * When semi-join is involved in the inner or outer part of the
               * right join, it's deparsed as a subquery, and we can't refer to
               * its vars on the upper level.
               */
              if (bms_is_empty(fpinfo_o->hidden_subquery_rels))
                  fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
                                                    fpinfo_o->remote_conds);
              if (bms_is_empty(fpinfo_i->hidden_subquery_rels))
                  fpinfo->remote_conds = list_concat(fpinfo->remote_conds,
                                                     fpinfo_i->remote_conds);
              break;

          case JOIN_SEMI:
              fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
                                                fpinfo_i->remote_conds);
              fpinfo->joinclauses = list_concat(fpinfo->joinclauses,
                                                fpinfo->remote_conds);
              fpinfo->remote_conds = list_copy(fpinfo_o->remote_conds);
              fpinfo->hidden_subquery_rels = bms_union(fpinfo->hidden_subquery_rels,
                                                       innerrel->relids);
              break;

          case JOIN_FULL:

              /*
               * In this case, if any of the input relations has conditions, we
               * need to deparse that relation as a subquery so that the
               * conditions can be evaluated before the join.  Remember it in
               * the fpinfo of this relation so that the deparser can take
               * appropriate action.  Also, save the relids of base relations
               * covered by that relation for later use by the deparser.
               */
              if (fpinfo_o->remote_conds)
              {
                  fpinfo->make_outerrel_subquery = true;
                  fpinfo->lower_subquery_rels =
                      bms_add_members(fpinfo->lower_subquery_rels,
                                      outerrel->relids);
              }
              if (fpinfo_i->remote_conds)
              {
                  fpinfo->make_innerrel_subquery = true;
                  fpinfo->lower_subquery_rels =
                      bms_add_members(fpinfo->lower_subquery_rels,
                                      innerrel->relids);
              }
              break;

          default:
              /* Should not happen, we have just checked this above */
              elog(ERROR, "unsupported join type %d", jointype);
      }

      /*
       * For an inner join, all restrictions can be treated alike. Treating the
       * pushed down conditions as join conditions allows a top level full outer
       * join to be deparsed without requiring subqueries.
       */
      if (jointype == JOIN_INNER)
      {
          Assert(!fpinfo->joinclauses);
          fpinfo->joinclauses = fpinfo->remote_conds;
          fpinfo->remote_conds = NIL;
      }
   /* 省略 */
   /*
  * Set the string describing this join relation to be used in EXPLAIN
  * output of corresponding ForeignScan.  Note that the decoration we add
  * to the base relation names mustn't include any digits, or it'll confuse
  * postgresExplainForeignScan.
  */
 fpinfo->relation_name = psprintf("(%s) %s JOIN (%s)",
          fpinfo_o->relation_name,
          get_jointype_name(fpinfo->jointype),
          fpinfo_i->relation_name);

 /*
  * Set the relation index.  This is defined as the position of this
  * joinrel in the join_rel_list list plus the length of the rtable list.
  * Note that since this joinrel is at the end of the join_rel_list list
  * when we are called, we can get the position by list_length.
  */
 Assert(fpinfo->relation_index == 0); /* shouldn't be set yet */
 fpinfo->relation_index =
  list_length(root->parse->rtable) + list_length(root->join_rel_list);

return true;
/* 省略 */
}

postgres=> SELECT oprname, oprcode,oprleft::regtype, oprright::regtype FROM pg_operator WHERE oid =98;
 oprname | oprcode | oprleft | oprright 
---------+---------+---------+----------
 =       | texteq  | text    | text
(1 row)

postgres=> 

postgres=> SELECT oprname, oprcode,oprleft::regtype, oprright::regtype FROM pg_operator WHERE oid = 96;
 oprname | oprcode | oprleft | oprright 
---------+---------+---------+----------
 =       | int4eq  | integer | integer
(1 row)

postgres=>

postgres=> show postgres_fdw.is_join_pushdown;
 postgres_fdw.is_join_pushdown 
-------------------------------
 on
(1 row)

postgres=> explain (verbose,analyze) select distinct a.sid,a.ip,a.port,a.type from tbl a join inf b on a.sid::text = b.id::text where a.ext=1;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=452.77..452.86 rows=1 width=328) (actual time=222742.200..222742.228 rows=28.00 loops=1)
   Output: a.sid, a.ip, a.port, a.type
   Buffers: shared hit=9
   ->  Sort  (cost=452.77..452.79 rows=7 width=328) (actual time=222742.197..222742.202 rows=28.00 loops=1)
         Output: a.sid, a.ip, a.port, a.type
         Sort Key: a.sid, a.ip, a.port, a.type
         Sort Method: quicksort  Memory: 26kB
         Buffers: shared hit=9
         ->  Foreign Scan  (cost=100.00..452.68 rows=7 width=328) (actual time=27949.156..222741.929 rows=28.00 loops=1)
               Output: a.sid, a.ip, a.port, a.type
               Filter: ((a.sid)::text = (b.id)::text)
               Rows Removed by Filter: 50474621
               Relations: (public.tbl a) INNER JOIN (public.inf b)
               Remote SQL: SELECT r1.sid, r1.ip, r1.port, r1.type, r2.id FROM (public.tbl r1 INNERJOIN public.inf r2 ON (((r1.ext = 1))))
 Planning Time: 0.241 ms
 Execution Time: 222760.342 ms
(16rows)

postgres=>

postgres=> set postgres_fdw.is_join_pushdown to off;
SET
postgres=> explain (verbose,analyze) select distinct a.sid,a.ip,a.port,a.type from tbl a join inf b on a.sid::text = b.id::text where a.ext=1;
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=557.72..557.81 rows=1 width=328) (actual time=79.539..79.566 rows=28.00 loops=1)
   Output: a.sid, a.ip, a.port, a.type
   ->  Sort  (cost=557.72..557.74 rows=7 width=328) (actual time=79.537..79.544 rows=28.00 loops=1)
         Output: a.sid, a.ip, a.port, a.type
         Sort Key: a.sid, a.ip, a.port, a.type
         Sort Method: quicksort  Memory: 26kB
         ->  Hash Join  (cost=213.12..557.62 rows=7 width=328) (actual time=17.859..79.490 rows=28.00 loops=1)
               Output: a.sid, a.ip, a.port, a.type
               Hash Cond: ((b.id)::text = (a.sid)::text)
               ->  Foreign Scan on public.inf b  (cost=100.00..431.64 rows=1462 width=32) (actual time=0.465..54.815 rows=29397.00 loops=1)
                     Output: b.id
                     Remote SQL: SELECTidFROM public.inf
               ->  Hash  (cost=113.11..113.11rows=1 width=328) (actual time=8.201..8.202rows=1717.00 loops=1)
                     Output: a.sid, a.ip, a.port, a.type
                     Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  MemoryUsage: 133kB
                     ->  ForeignScanon public.tbl a  (cost=100.00..113.11rows=1 width=328) (actual time=0.575..6.676rows=1717.00 loops=1)
                           Output: a.sid, a.ip, a.port, a.type
                           Remote SQL: SELECTsid, ip, port, typeFROM public.tbl WHERE ((ext = 1))
 Planning Time: 0.236 ms
 Execution Time: 79.839 ms
(20rows)

postgres=>