postgresql - PostgreSql 应用并行顺序扫描而不是索引扫描

Question

我在所有列 Juliet、Five、Juilet1 到 Juliet15 上都有索引，即使 PostgresSQL 正在执行并行 Seq 扫描而不是索引扫描。为什么会这样？

PostgresSql 查询是，

SELECT juliet FROM romeo 
WHERE (((romeo.juliet1 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet2 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet3 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet4 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet5 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet6 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet7 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet8 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet9 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet10 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet11 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet12 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet13 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet14 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.juliet15 IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732') 
   OR romeo.five IN ('S66.303S', 'S66303S', 'S52.009S', 'S52009S', 'S32.466G', 'S32466G', 'H44.139', 'H44139', 'H35.732', 'H35732'))));

这是explain analyze生成的查询计划。如您所见，正在对 Romeo 应用并行 Seq 扫描。

Gather  (cost=1000.000..2459531.510 rows=14964 width=9) (actual time=734.439..11268.059 rows=376 loops=1)
    Workers Planned: 2
    Workers Launched: 2
  ->  Parallel Seq Scan on romeo  (cost=0.000..2457035.110 rows=6235 width=9) (actual time=741.013..11256.264 rows=125 loops=3)
          Filter: (((juliet1)::text = ANY ('three'::text[])) OR ((juliet2)::text = ANY ('three'::text[])) OR ((juliet3)::text = ANY ('three'::text[])) OR ((juliet4)::text = ANY ('three'::text[])) OR ((juliet5)::text = ANY ('three'::text[])) OR ((juliet6)::text = ANY ('three'::text[])) OR ((juliet7)::text = ANY ('three'::text[])) OR ((juliet8)::text = ANY ('three'::text[])) OR ((juliet9)::text = ANY ('three'::text[])) OR ((juliet10)::text = ANY ('three'::text[])) OR ((juliet11)::text = ANY ('three'::text[])) OR ((juliet12)::text = ANY ('three'::text[])) OR ((juliet13)::text = ANY ('three'::text[])) OR ((juliet14)::text = ANY ('three'::text[])) OR ((juliet15)::text = ANY ('three'::text[])) OR ((five)::text = ANY ('three'::text[])))
          Rows Removed by Filter: 4774850
Planning time: 0.533 ms
Execution time: 60030.289 ms

表上所有索引的索引语句。

CREATE INDEX juliet1_idx_pd_activity ON pd_activity (juliet1);
CREATE INDEX juliet2_idx_pd_activity ON pd_activity (juliet2);
CREATE INDEX juliet3_idx_pd_activity ON pd_activity (juliet3);
CREATE INDEX juliet4_idx_pd_activity ON pd_activity (juliet4);
CREATE INDEX juliet5_idx_pd_activity ON pd_activity (juliet5);
CREATE INDEX juliet6_idx_pd_activity ON pd_activity (juliet6);
CREATE INDEX juliet7_idx_pd_activity ON pd_activity (juliet7);
CREATE INDEX juliet8_idx_pd_activity ON pd_activity (juliet8);
CREATE INDEX juliet9_idx_pd_activity ON pd_activity (juliet9);
CREATE INDEX juliet10_idx_pd_activity ON pd_activity (juliet10);
CREATE INDEX juliet11_idx_pd_activity ON pd_activity (juliet11);
CREATE INDEX juliet12_idx_pd_activity ON pd_activity (juliet12);
CREATE INDEX juliet13_idx_pd_activity ON pd_activity (juliet13);
CREATE INDEX juliet14_idx_pd_activity ON pd_activity (juliet14);
CREATE INDEX juliet15_idx_pd_activity ON pd_activity (juliet15);

标签： postgresqlindexingsql-execution-plan