[未完待续]explain sql解析器

来源：互联网发布：淘宝怎样申请退换货编辑：程序博客网时间：2024/05/16 12:49

是否使用索引：

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 msgp=# 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)

0 0