是否使用索引:
gp=# explain select cjbh, jjbh, sfxq, sfsjsx, bccljg, cljgnr, spsj, zbld, ldclsj from jcj_cjxx_r where jjbh='20010015';
QUERY PLAN
--------------------------------------------------------------------------------------------
Gather Motion 1:1 (slice1; segments: 1) (cost=0.00..285.60 rows=1 width=122)
-> Index Scan using jcj_cjxx_r_pkey on jcj_cjxx_r (cost=0.00..285.60 rows=1 width=122)
Index Cond: jjbh::text = '20010015'::text
Optimizer status: legacy query optimizer
(4 rows)
Time: 0.981 ms
gp=# explain select cjbh, jjbh, sfxq, sfsjsx, bccljg, cljgnr, spsj, zbld, ldclsj from jcj_cjxx_r where zbld='20010015';
QUERY PLAN
--------------------------------------------------------------------------------------
Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..3918480.50 rows=197 width=122)
-> Seq Scan on jcj_cjxx_r (cost=0.00..3918480.50 rows=99 width=122)
Filter: zbld = '20010015'::bpchar
Optimizer status: legacy query optimizer
(4 rows)
Time: 0.641 ms
下面这两个查询计划,一个是PQO打开,使用了全表扫描,一个是PQO关闭,使用了seq scan
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..431.00 rows=1 width=274)
-> Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..431.00 rows=1 width=274)
-> Result (cost=0.00..431.00 rows=1 width=274)
-> Table Scan on dma_i3_czrk_jbxx_a (cost=0.00..431.00 rows=1 width=274)
Filter: gmsfzhm::text = '150424196710262754'::text OR gmsfzhm::text = '610623193712283251'::text
Settings: optimizer=on
Optimizer status: PQO version 1.625
(7 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1.03 rows=1 width=274)
-> Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..1.03 rows=1 width=274)
-> Limit (cost=0.00..1.01 rows=1 width=274)
-> Seq Scan on dma_i3_czrk_jbxx_a (cost=0.00..1.01 rows=1 width=274)
Filter: gmsfzhm::text = '150424196710262754'::text OR gmsfzhm::text = '610623193712283251'::text
Settings: optimizer=off
Optimizer status: legacy query optimizer
(7 rows)
下面是用用了index scan
QUERY PLAN
------------------------------------------------------------------------------------------------------
Limit (cost=0.00..3.00 rows=1 width=274)
-> Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..3.00 rows=1 width=274)
-> Index Scan using index_gmsfzhm on dma_i3_czrk_jbxx_a (cost=0.00..3.00 rows=1 width=274)
Index Cond: gmsfzhm::text = '150424196710262754'::text
Settings: optimizer=on
Optimizer status: PQO version 1.625
(6 rows)