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

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=# 

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=#