[未完待续]explain sql解析器

是否使用索引：

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)