单表查询
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)
