# 9.1第56章

## 56.1. 行预期的例子

``` EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)
```

```SELECT reltuples, relpages FROM pg_class WHERE relname = 'tenk1';
```
```relpages | reltuples
----------+-----------
345 |     10000
```

```rows = 10000
```

```EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;
```
```                        QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1  (cost=0.00..470.00 rows=1031 width=244)
Filter: (unique1 < 1000)
```

```unique1 < 1000
```

```SELECT histogram_bounds FROM pg_stats
WHERE tablename='tenk1' AND attname='unique1';
histogram_bounds
------------------------------------------------------
{1,970,1943,2958,3971,5069,6028,7007,7919,8982,9995}
```

```selectivity = (1 + (1000 - bckt[2].min)/(bckt[2].max - bckt[2].min))/num_bckts
= (1 + (1000 - 970)/(1943 - 970))/10
= 0.1031
```

```rows = rel_cardinality * selectivity
= 10000 * 0.1031
= 1031
```

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

QUERY PLAN
----------------------------------------------------------
Seq Scan on tenk1  (cost=0.00..470.00 rows=31 width=244)
Filter: (stringu1 = 'ATAAAA'::name)
```

```stringu1 = 'ATAAAA'
```

```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        | 672
most_common_vals  | {FDAAAA,NHAAAA,ATAAAA,BGAAAA,EBAAAA,MOAAAA,NDAAAA,OWAAAA,BHAAAA,BJAAAA}
most_common_freqs | {0.00333333,0.00333333,0.003,0.003,0.003,0.003,0.003,0.003,0.00266667,0.00266667}
```

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

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

EXPLAIN 现实的数值比这个大一，因为一些事后的估计检查。

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

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

```selectivity = (1 - sum(mvf))/(num_distinct - num_mcv)
= (1 - (0.00333333 + 0.00333333 + 0.003 + 0.003 + 0.003
+ 0.003 + 0.003 + 0.003 + 0.00266667 + 0.00266667))/(672 - 10)
= 0.001465
```

```rows = 10000 * 0.001465
= 15
```

```EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000 AND stringu1 = 'xxx';
```
```                      QUERY PLAN
-----------------------------------------------------------
Seq Scan on tenk1  (cost=0.00..495.00 rows=2 width=244)
Filter: ((unique1 < 1000) AND (stringu1 = 'xxx'::name))
```

```selectivity = selectivity(unique1 < 1000) * selectivity(stringu1 = 'xxx')
= 0.1031 * 0.001465
= 0.00015104
```

```rows = 10000 * 0.00015104
= 2
```

```EXPLAIN SELECT *  FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
```
```                                     QUERY PLAN
-----------------------------------------------------------------------------------------
Nested Loop  (cost=0.00..346.90 rows=51 width=488)
->  Index Scan using tenk1_unique1 on tenk1 t1  (cost=0.00..192.57 rows=51 width=244)
Index Cond: (unique1 < 50)
->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.00..3.01 rows=1 width=244)
Index Cond: ("outer".unique2 = t2.unique2)
```

```selectivity = (0 + (50 - bckt[1].min)/(bckt[1].max - bckt[1].min))/num_bckts
= (0 + (50 - 1)/(970 - 1))/10
= 0.005057
```
```rows        = 10000 * 0.005057
= 51
```

```t2.unique2 = t1.unique2
```

```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) * min(1/10000, 1/1000)
= 0.0001
```

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