所有文章是按github的markdown格式书写的，如此页面显示不正常，请跳转到github版

唯一索引的行估算实验

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

实验

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

postgres=# create table tbc1(id int) with (autovacuum_enabled=off);
CREATE TABLE
postgres=# 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 INDEX
postgres=# 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 INDEX
postgres=# 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 <0;
                                                       QUERY PLAN                    
                                    
-------------------------------------------------------------------------------------
------------------------------------
 Bitmap Heap Scan on tbc1  (cost=41.12..127.78 rows=3333 width=4) (actual time=0.009.
.0.009 rows=0 loops=1)
   Recheck Cond: (id < 0)
   ->  Bitmap Index Scan on tbc1_id_idx1  (cost=0.00..40.28 rows=3333 width=0) (actua
l time=0.007..0.007 rows=0 loops=1)
         Index Cond: (id < 0)
 Planning time: 0.100 ms
 Execution time: 0.036 ms
(6 rows)

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_e
lems | most_common_elem_freqs | elem_count_histogram 
------------+-----------+---------+-----------+-----------+-----------+------------+-
-----------------+-------------------+------------------+-------------+--------------
-----+------------------------+----------------------
(0 rows)

December 22, 2017