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

---------

(2 rows)

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

relkind

---------

(1 row)

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

relkind

---------

(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)