唯一索引的行估算实验

唯一索引除了有业务上约束作用，还可以使行估算更准确。 对唯一索引列的等值条件查询，即使统计信息缺失，也能得到准确的行估算值即1。

实验

创建不收集统计信息的测试表

postgres=# create table tbc1(id int) with (autovacuum_enabled=off);CREATE TABLEpostgres=# insert into tbc1 select * from generate_series(1,10000);INSERT 0 10000

查询某唯一值，但行估算为57。

postgres=# explain select * from tbc1 where id =10;                      QUERY PLAN                       ------------------------------------------------------- Seq Scan on tbc1  (cost=0.00..188.44 rows=57 width=4)   Filter: (id = 10)(2 rows)

创建普通索引，行估算仍为50。

postgres=# create index on tbc1(id);CREATE INDEXpostgres=# explain select * from tbc1 where id =10;                                QUERY PLAN                                 --------------------------------------------------------------------------- Bitmap Heap Scan on tbc1  (cost=2.17..38.17 rows=50 width=4)   Recheck Cond: (id = 10)   ->  Bitmap Index Scan on tbc1_id_idx  (cost=0.00..2.16 rows=50 width=0)         Index Cond: (id = 10)(4 rows)

创建唯一索引，行估算变为1和实际吻合。

postgres=# create unique index on tbc1(id);CREATE INDEXpostgres=# explain select * from tbc1 where id =10;                                  QUERY PLAN                                  ------------------------------------------------------------------------------ Index Only Scan using tbc1_id_idx1 on tbc1  (cost=0.29..3.30 rows=1 width=4)   Index Cond: (id = 10)(2 rows)

唯一索引对行估算的作用不适用于非等值条件，比如范围条件

postgres=# explain analyze select * from tbc1 where id   Bitmap Index Scan on tbc1_id_idx1  (cost=0.00..40.28 rows=3333 width=0) (actual time=0.007..0.007 rows=0 loops=1)         Index Cond: (id

SQL中也不要在条件字段上附加计算或类型转换，否则即使有唯一索引估算也不会准。

postgres=# explain select * from tbc1 where id::text ='10';                      QUERY PLAN                       ------------------------------------------------------- Seq Scan on tbc1  (cost=0.00..220.00 rows=50 width=4)   Filter: ((id)::text = '10'::text)(2 rows)

由于关闭auto_autovacuum，测试过程中全程测试表统计信息都为空

postgres=# select * from pg_stats where tablename='tbc1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram ------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------(0 rows)