PostgreSQL子查询统计信息“陷阱”

create table test1(a int);
create table test2(a int);


insert into test1 select generate_series(1,100000);
insert into test2 select generate_series(1,201);


analyze test1;
analyze test2;

postgres=# explain with t as (select * from test2) select * from test1 where a in (select * from t);
                             QUERY PLAN
---------------------------------------------------------------------
 Hash Join  (cost=12.03..2273.78 rows=50000 width=4)
   Hash Cond: (test1.a = t.a)
   CTE t
     ->  Seq Scan on test2  (cost=0.00..3.01 rows=201 width=4)
   ->  Seq Scan on test1  (cost=0.00..1443.00 rows=100000 width=4)
   ->  Hash  (cost=6.52..6.52 rows=200 width=4)
         ->  HashAggregate  (cost=4.52..6.52 rows=200 width=4)
               Group Key: t.a
               ->  CTE Scan on t  (cost=0.00..4.02 rows=201 width=4)
(9 rows)


postgres=# explain with t as (select * from test2 where a <= 200) select * from test1 where a in (select * from t);
                            QUERY PLAN
-------------------------------------------------------------------
 Hash Semi Join  (cost=10.01..1722.49 rows=200 width=4)
   Hash Cond: (test1.a = t.a)
   CTE t
     ->  Seq Scan on test2  (cost=0.00..3.51 rows=200 width=4)
           Filter: (a <= 200)
   ->  Seq Scan on test1  (cost=0.00..1443.00 rows=100000 width=4)
   ->  Hash  (cost=4.00..4.00 rows=200 width=4)
         ->  CTE Scan on t  (cost=0.00..4.00 rows=200 width=4)
(8 rows)

src/include/utils/selfuncs.h


/* default number of distinct values in a table */
#define DEFAULT_NUM_DISTINCT  200

 /*
  * Without MCV lists for both sides, we can only use the heuristic
  * about nd1 vs nd2.
  */
   double    nullfrac1 = stats1 ? stats1->stanullfrac : 0.0;
   // 是否采用默认值，在get_variable_numdistinct中指定，由于WITH没有任何统计信息，采用默认
   // 值，同时在该函数前面也会进行检查判断是否是默认采样值（主要依据是获取的结果是否大于等于实际
   // 值，这里实际值201,因此这里采用默认值，true
   if (!isdefault1 && !isdefault2)
   {
      if (nd1 <= nd2 || nd2 < 0)
        selec = 1.0 - nullfrac1;
      else
        selec = (nd2  nd1) * (1.0 - nullfrac1);
    }
    else
     // 计算最终选择率，这里nullfrac是0，因此最终结果是0.5
     selec = 0.5 * (1.0 - nullfrac1);

postgres=# explain with t as (select * from test2) select * from test1 where a in (select * from t);
                            QUERY PLAN
-------------------------------------------------------------------
 Hash Semi Join  (cost=5.52..1713.26 rows=201 width=4)
   Hash Cond: (test1.a = test2.a)
   ->  Seq Scan on test1  (cost=0.00..1443.00 rows=100000 width=4)
   ->  Hash  (cost=3.01..3.01 rows=201 width=4)
         ->  Seq Scan on test2  (cost=0.00..3.01 rows=201 width=4)
(5 rows)


postgres=# explain with t as (select * from test2 where a <= 200) select * from test1 where a in (select * from t);
                            QUERY PLAN
-------------------------------------------------------------------
 Hash Semi Join  (cost=6.01..1713.74 rows=200 width=4)
   Hash Cond: (test1.a = test2.a)
   ->  Seq Scan on test1  (cost=0.00..1443.00 rows=100000 width=4)
   ->  Hash  (cost=3.51..3.51 rows=200 width=4)
         ->  Seq Scan on test2  (cost=0.00..3.51 rows=200 width=4)
               Filter: (a <= 200)
(6 rows)

/*    
 * Consider inlining the CTE (creating RTE_SUBQUERY RTE(s)) instead of
 * implementing it as a separately-planned CTE.
 *
 * We cannot inline if any of these conditions hold:
 *
 * 1. The user said not to (the CTEMaterializeAlways option).
 *
 * 2. The CTE is recursive.
 *
 * 3. The CTE has side-effects; this includes either not being a plain
 * SELECT, or containing volatile functions.  Inlining might change
 * the side-effects, which would be bad.
 *
 * 4. The CTE is multiply-referenced and contains a self-reference to
 * a recursive CTE outside itself.  Inlining would result in multiple
 * recursive self-references, which we don't support.
 */


  if ((cte->ctematerialized == CTEMaterializeNever ||
    (cte->ctematerialized == CTEMaterializeDefault &&
     cte->cterefcount == 1)) &&
   !cte->cterecursive &&
   cmdType == CMD_SELECT &&
   !contain_dml(cte->ctequery) &&
   (cte->cterefcount <= 1 ||
    !contain_outer_selfref(cte->ctequery)) &&
   !contain_volatile_functions(cte->ctequery))
 {
   inline_cte(root, cte);
   /* Make a dummy entry in cte_plan_ids */
   root->cte_plan_ids = lappend_int(root->cte_plan_ids, -1);
   continue;
 }