# 56.1. 行预期的例子

```EXPLAIN SELECT * FROM tenk1;

QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)```

```SELECT relpages，reltuples FROM pg_class WHERE relname = 'tenk1';

relpages | reltuples
----------+-----------
358 |     10000```

```EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;

QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1  (cost=24.06..394.64 rows=1007 width=244)
Recheck Cond: (unique1 < 1000)
->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..23.80 rows=1007 width=0)
Index Cond: (unique1 < 1000)```

```SELECT histogram_bounds FROM pg_stats
WHERE tablename='tenk1' AND attname='unique1';

histogram_bounds
------------------------------------------------------
{0，993，1997，3050，4040，5036，5957，7057，8029，9016，9995}```

```selectivity = (1 + (1000 - bucket[2].min)/(bucket[2].max - bucket[2].min))/num_buckets
= (1 + (1000 - 993)/(1997 - 993))/10
= 0.100697```

```rows = rel_cardinality * selectivity
= 10000 * 0.100697
= 1007  (rounding off)```

```EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'CRAAAA';

QUERY PLAN
----------------------------------------------------------
Seq Scan on tenk1  (cost=0.00..483.00 rows=30 width=244)
Filter: (stringu1 = 'CRAAAA'::name)```

```SELECT null_frac，n_distinct，most_common_vals，most_common_freqs FROM pg_stats
WHERE tablename='tenk1' AND attname='stringu1';

null_frac         | 0
n_distinct        | 676
most_common_vals  | {EJAAAA，BBAAAA，CRAAAA，FCAAAA，FEAAAA，GSAAAA，JOAAAA，MCAAAA，NAAAAA，WGAAAA}
most_common_freqs | {0.00333333，0.003，0.003，0.003，0.003，0.003，0.003，0.003，0.003，0.003}```

```selectivity = mcf[3]
= 0.003```

```rows = 10000 * 0.003
= 30```

```EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'xxx';

QUERY PLAN
----------------------------------------------------------
Seq Scan on tenk1  (cost=0.00..483.00 rows=15 width=244)
Filter: (stringu1 = 'xxx'::name)```

```selectivity = (1 - sum(mvf))/(num_distinct - num_mcv)
= (1 - (0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 +
0.003 + 0.003 + 0.003 + 0.003))/(676 - 10)
= 0.0014559```

```rows = 10000 * 0.0014559
= 15  (rounding off)```

```EXPLAIN SELECT * FROM tenk1 WHERE stringu1 < 'IAAAAA';

QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1  (cost=0.00..483.00 rows=3077 width=244)
Filter: (stringu1 < 'IAAAAA'::name)```

```SELECT histogram_bounds FROM pg_stats
WHERE tablename='tenk1' AND attname='stringu1';

histogram_bounds
--------------------------------------------------------------------------------
{AAAAAA，CQAAAA，FRAAAA，IBAAAA，KRAAAA，NFAAAA，PSAAAA，SGAAAA，VAAAAA，XLAAAA，ZZAAAA}```

```selectivity = sum(relevant mvfs)
= 0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003
= 0.01833333```

```selectivity = mcv_selectivity + histogram_selectivity * histogram_fraction
= 0.01833333 + 0.298387 * 0.96966667
= 0.307669

rows        = 10000 * 0.307669
= 3077  (rounding off)```

```EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000 AND stringu1 = 'xxx';

QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1  (cost=23.80..396.91 rows=1 width=244)
Recheck Cond: (unique1 < 1000)
Filter: (stringu1 = 'xxx'::name)
->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..23.80 rows=1007 width=0)
Index Cond: (unique1 < 1000)```

```selectivity = selectivity(unique1 < 1000) * selectivity(stringu1 = 'xxx')
= 0.100697 * 0.0014559
= 0.0001466

rows        = 10000 * 0.0001466
= 1  (rounding off)```

```EXPLAIN SELECT * FROM tenk1 t1，tenk2 t2
WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;

QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop  (cost=4.64..456.23 rows=50 width=488)
->  Bitmap Heap Scan on tenk1 t1  (cost=4.64..142.17 rows=50 width=244)
Recheck Cond: (unique1 < 50)
->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.63 rows=50 width=0)
Index Cond: (unique1 < 50)
->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..6.27 rows=1 width=244)
Index Cond: (t2.unique2 = t1.unique2)```

tenk1上的unique1 < 50限制在嵌套循环连接之前计算。这个条件是用类似上面的那个范围例子的方法处理的。 但是这次数值50落在unique1的直方图表的第一个段内：

```selectivity = (0 + (50 - bucket[1].min)/(bucket[1].max - bucket[1].min))/num_buckets
= (0 + (50 - 0)/(993 - 0))/10
= 0.005035

rows        = 10000 * 0.005035
= 50  (rounding off)```

```SELECT tablename，null_frac，n_distinct，most_common_vals FROM pg_stats
WHERE tablename IN ('tenk1'，'tenk2') AND attname='unique2';

tablename  | null_frac | n_distinct | most_common_vals
-----------+-----------+------------+------------------
tenk1     |         0 |         -1 |
tenk2     |         0 |         -1 |```

```selectivity = (1 - null_frac1) * (1 - null_frac2) * min(1/num_distinct1，1/num_distinct2)
= (1 - 0) * (1 - 0) / max(10000，10000)
= 0.0001```

```rows = (outer_cardinality * inner_cardinality) * selectivity
= (50 * 10000) * 0.0001
= 50```