greenplum 单表 数据扫描

单表查询

1）seq scan 顺序扫描 数据文件从头读到尾，greenplum中压缩表尤为突出

2）index scan:索引扫描，对于查询小数据量而言，速度很快

3）bitmap heap scan:位图堆表扫描，数据在整表占较大的时候

tutorial=> create table pg_class_tmp as select * from pg_class distributed by (relname);

SELECT 468

tutorial=> create index pg_class_tmp_relkind_idx on pg_class_tmp(relkind);

CREATE INDEX

通过参数 enable_seqscan禁止顺序扫描，确保执行计划通过pg_class_tmp_relkind_idx查询数据

tutorial=> set enable_seqscan = off;

SET

tutorial=> explain select * from pg_class_tmp where relkind='c';

                                          QUERY PLAN                                         

----------------------------------------------------------------------------------------------

-

Gather Motion 2:1  (slice1; segments: 2)  (cost=100.28..143.12 rows=3 width=234)

   ->  Bitmap Heap Scan on pg_class_tmp  (cost=100.28..143.12 rows=3 width=234)

         Recheck Cond: relkind = 'c'::"char"

         ->  Bitmap Index Scan on pg_class_tmp_relkind_idx  (cost=0.00..100.28 rows=3 width=0)

               Index Cond: relkind = 'c'::"char"

Settings:  enable_seqscan=off

(6 rows)

/*创建索引时创建的为普通索引，为什么会变成位图索引*/

4）tid scan:隐藏字段ctid扫描(oracle rowid)

ctid是postgresql标记数据位置位置的字段，每个子节点都是一个postgresql数据库，每一个子节点都单独维护自己的一套ctid字段

tutorial=> select ctid from pg_class_tmp limit 1;

ctid 

-------

(0,1)

(1 row)

tutorial=> select relkind from pg_class_tmp where ctid = '(0,1)';

NOTICE:  SELECT uses system-defined column "pg_class_tmp.ctid" without the necessary companion

column "pg_class_tmp.gp_segment_id"HINT:  To uniquely identify a row within a distributed table, use the "gp_segment_id" column t

ogether with the "ctid" column. relkind

---------

t

r

(2 rows)

tutorial=> select relkind from pg_class_tmp where ctid = '(0,1)' and gp_segment_id=1;

relkind

---------

r

(1 row)

tutorial=> select relkind from pg_class_tmp where ctid = '(0,1)' and gp_segment_id=0;

relkind

---------

t

(1 row)

tutorial=>

5） 子查询

只要sql中有子查询，需要对子查询的结果做顺序扫描，就会进行子查询扫描

6） 函数扫描

tutorial=> explain select * from generate_series(1,20);

                               QUERY PLAN                              

------------------------------------------------------------------------

Function Scan on generate_series  (cost=0.00..12.50 rows=2000 width=4)

Settings:  enable_seqscan=off

(2 rows)     

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29162273/viewspace-2129553/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29162273/viewspace-2129553/