对PostgreSQL的 seq scan , bitmap index scan 和 index scan 的进一步理解

开始

参考momjian 的文章：

http://momjian.us/main/writings/pgsql/optimizer.pdf

首先，构造一个数据分布明显倾斜的表（有的值占据了70%以上的分布）

postgres=# CREATE TEMPORARY TABLE sample (letter, junk) AS
postgres-# SELECT substring(relname, 1, 1), repeat('x', 250)
postgres-# FROM pg_class
postgres-# ORDER BY random();
SELECT 291
postgres=# 


postgres=# CREATE INDEX i_sample on sample (letter);
CREATE INDEX
postgres=# 

postgres=# CREATE OR REPLACE FUNCTION lookup_letter(text) RETURNS SETOF text AS $$
postgres$# BEGIN
postgres$# RETURN QUERY EXECUTE'
postgres$# EXPLAIN SELECT letter
postgres$# FROM sample
postgres$# WHERE letter ='''||$1||'''';
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# 

postgres=# WITH letters (letter, count) AS (
postgres(# SELECT letter, COUNT(*)
postgres(# FROM sample
postgres(# GROUP BY 1
postgres(# )
postgres-# SELECT letter, count, (count * 100.0 / (SUM(count) OVER ()))::numeric(4,1) AS "%"
postgres-# FROM letters
postgres-# ORDER BY 2 DESC;
 letter | count |  %   
--------+-------+------
 p      |   223 | 76.6
 c      |    12 |  4.1
 s      |     9 |  3.1
 r      |     8 |  2.7
 f      |     6 |  2.1
 d      |     5 |  1.7
 u      |     5 |  1.7
 _      |     5 |  1.7
 t      |     5 |  1.7
 v      |     4 |  1.4
 e      |     3 |  1.0
 a      |     3 |  1.0
 i      |     2 |  0.7
 k      |     1 |  0.3
(14 rows)

postgres=# 

此时，如果不通过 analyze 来更新统计信息，得到的 执行计划是不准确的。所以先要更新统计信息：

postgres=#analyze sampel;

postgres=#

然后，再来看执行的情况：

[作者：技术者高健@博客园  mail: luckyjackgao@gmail.com ]

postgres=# EXPLAIN SELECT *     FROM sample
WHERE letter ='p';
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on sample  (cost=0.00..14.64 rows=223 width=256)
   Filter: (letter = 'p'::text)
(2 rows)

postgres=# 

postgres=# EXPLAIN SELECT *
FROM sample
WHERE letter = 'd';
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Bitmap Heap Scan on sample  (cost=4.29..14.24 rows=5 width=256)
   Recheck Cond: (letter = 'd'::text)
   ->  Bitmap Index Scan on i_sample  (cost=0.00..4.29 rows=5 width=0)
         Index Cond: (letter = 'd'::text)
(4 rows)
postgres=# 

postgres=# EXPLAIN SELECT *
FROM sample
WHERE letter = 'k';
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Index Scan using i_sample on sample  (cost=0.00..8.27 rows=1 width=256)
   Index Cond: (letter = 'k'::text)
(2 rows)
postgres=# 

数据分布很大(比如70%以上)，用index scan 已经没有意义了，因为数据太多了。所以就不如用 全表扫描了。

数据分布较小(比如 1.7%)，则用 bitmap index scan。数据更少的时候，用的是 index scan。

需要引起注意的是， bitmap index 也可以用在where 条件单一的时候。

结束