Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2900503
  • 博文数量: 199
  • 博客积分: 1400
  • 博客等级: 上尉
  • 技术积分: 4126
  • 用 户 组: 普通用户
  • 注册时间: 2008-07-06 19:06
个人简介

半个PostgreSQL DBA,热衷于数据库相关的技术。我的ppt分享https://pan.baidu.com/s/1eRQsdAa https://github.com/chenhuajun https://chenhuajun.github.io

文章分类

全部博文(199)

文章存档

2020年(5)

2019年(1)

2018年(12)

2017年(23)

2016年(43)

2015年(51)

2014年(27)

2013年(21)

2011年(1)

2010年(4)

2009年(5)

2008年(6)

分类: Mysql/postgreSQL

2017-12-22 14:25:20

唯一索引的行估算实验

唯一索引除了有业务上约束作用,还可以使行估算更准确。 对唯一索引列的等值条件查询,即使统计信息缺失,也能得到准确的行估算值即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)
阅读(1300) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~