GreenPlum关联查询执行计划异常：小范围ID查询变慢求助

环境与表结构 #

GreenPlum版本：PostgreSQL 9.4.20 (Greenplum Database 6.0.0-beta.3)
两张各约1亿条数据的表：

1. cookie_session表 #

CREATE TABLE "ods_overall_cookie"."cookie_session" (
  "site_cookie" varchar(80) COLLATE "pg_catalog"."default",
  "createtime" timestamp(6),
  "analyse_domain_cookie" varchar(30) COLLATE "pg_catalog"."default",
  "id" int4 NOT NULL,
  .... other fields....
) 
DISTRIBUTED by(analyse_domain_cookie)
;

CREATE INDEX "index_cookie_session_id" ON "ods_overall_cookie"."cookie_session" USING btree (
  "id" "pg_catalog"."int4_ops" ASC NULLS LAST
);

CREATE INDEX "index_analysis_domain_cookie_btree" ON "ods_overall_cookie"."cookie_session" USING btree (
  "analyse_domain_cookie" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

2. ta202202表 #

CREATE TABLE "ods_log"."ta202202" (
  "id" serial8,
  "uvcookie" varchar(50) COLLATE "pg_catalog"."default",
  .... other fields ...
) distributed by (uvcookie)
;

CREATE INDEX "index_ta202202_id" ON "ods_log"."ta202202" USING btree (
  "id" "pg_catalog"."int8_ops" ASC NULLS LAST
);

CREATE INDEX "indev_ta202202_uvcookie" ON "ods_log"."ta202202" USING btree (
  "uvcookie" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

问题现象 #

执行关联查询时，仅扩大1条ID范围，执行计划就从高效的Nested Loop切换为低效的Hash Join，耗时从0.14秒骤增至25秒：

快查询（Nested Loop，耗时~0.14s） #

select o.id,g.site_cookie 
from ods_log.ta202201  o 
join ods_overall_cookie.cookie_session  as g 
        on g.analyse_domain_cookie  = o.uvcookie 
WHERE o.ID BETWEEN 20000000 and 20000077;

执行计划：

Gather Motion 24:1  (slice1; segments: 24)  (cost=0.00..434.40 rows=1 width=41) (actual time=1.785..4.098 rows=552 loops=1)
  ->  Nested Loop  (cost=0.00..434.40 rows=1 width=41) (actual time=0.225..1.948 rows=276 loops=1)
        Join Filter: true
        ->  Index Scan using index_ta202201_id on ta202201  (cost=0.00..6.02 rows=3 width=25) (actual time=0.100..0.142 rows=8 loops=1)
              Index Cond: ((id >= 20000000) AND (id <= 20000077))
        ->  Index Scan using index_analysis_domain_cookie_btree on cookie_session  (cost=0.00..428.38 rows=1 width=33) (actual time=0.013..0.213 rows=34 loops=8)
              Index Cond: ((analyse_domain_cookie)::text = (ta202201.uvcookie)::text)
Planning time: 59.930 ms
  (slice0)    Executor memory: 216K bytes.
  (slice1)    Executor memory: 156K bytes avg x 24 workers, 156K bytes max (seg0).
  (slice2)    
Memory used:  128000kB
Optimizer: Pivotal Optimizer (GPORCA) version 3.39.0
Execution time: 26.725 ms

慢查询（Hash Join，耗时~25s） #

将ID范围改为o.ID BETWEEN 20000000 and 20000078，执行计划变为：

Gather Motion 24:1  (slice1; segments: 24)  (cost=0.00..437.02 rows=1 width=41) (actual time=10266.694..23884.316 rows=557 loops=1)
  ->  Hash Join  (cost=0.00..437.02 rows=1 width=41) (actual time=12256.944..23881.566 rows=276 loops=1)
        Hash Cond: ((ta202201.uvcookie)::text = (cookie_session.analyse_domain_cookie)::text)
        Extra Text: (seg0)   Initial batch 0:
(seg0)     Wrote 874907K bytes to inner workfile.
(seg0)     Wrote 1K bytes to outer workfile.
(seg0)   Overflow batches 1..7:
(seg0)     Read 1200209K bytes from inner workfile: 171459K avg x 7 nonempty batches, 335761K max.
(seg0)     Wrote 766456K bytes to inner workfile: 127743K avg x 6 overflowing batches, 304587K max.
(seg0)     Read 1K bytes from outer workfile: 1K avg x 4 nonempty batches, 1K max.
(seg0)     Wrote 1K bytes to outer workfile.
(seg0)   Secondary Overflow batches 8..32767:
(seg0)     Read 2014970K bytes from inner workfile: 9201K avg x 219 nonempty batches, 258871K max.
(seg0)     Wrote 1573816K bytes to inner workfile: 12107K avg x 130 overflowing batches, 247277K max.
(seg0)     Read 1K bytes from outer workfile.
(seg0)   Hash chain length 4.2 avg, 4645100 max, using 3735148 of 59506688 buckets.  Skipped 32541 empty batches.
        ->  Index Scan using index_ta202201_id on ta202201  (cost=0.00..6.02 rows=4 width=25) (actual time=0.380..0.428 rows=8 loops=1)
              Index Cond: ((id >= 20000000) AND (id <= 20000078))
        ->  Hash  (cost=431.00..431.00 rows=1 width=51) (actual time=12253.540..12253.540 rows=15647864 loops=1)
              ->  Seq Scan on cookie_session  (cost=0.00..431.00 rows=1 width=51) (actual time=0.058..5175.550 rows=15647865 loops=1)
Planning time: 62.416 ms
  (slice0)    Executor memory: 184K bytes.
* (slice1)    Executor memory: 245659K bytes avg x 24 workers, 375566K bytes max (seg0).  Work_mem: 290371K bytes max, 1149907K bytes wanted.
Memory used:  128000kB
Memory wanted:  1150306kB
Optimizer: Pivotal Optimizer (GPORCA) version 3.39.0
Execution time: 23927.425 ms

测试规律 #

调整ID范围后发现，特定边界会触发执行计划切换：

已尝试无效方法 #

• 修改优化器参数：

  set enable_nestloop= on;
  set enable_hashjoin = off;
  set enable_mergejoin = off;
  

• 改写为隐式关联SQL：

  select xxx from a,b where a.id between xxx and xxx and a.uvcookie = b.analyse_domain_cookie
  

• 更换关联类型（left join / inner join / full join）

解决方案 #

1. 禁用GPORCA并强制Nested Loop #

GPORCA在边界估算时出现偏差，切换到Postgres原生优化器并强制走Nested Loop：

set optimizer=off; -- 禁用GPORCA，使用Postgres原生规划器
set enable_hashjoin=off;
set enable_mergejoin=off;

执行查询前先运行上述参数，确保优化器选择Nested Loop。

2. 更新表统计信息 #

统计信息不准确导致优化器行数估算错误，更新统计信息后优化器能更准确判断执行计划：

ANALYZE ods_overall_cookie.cookie_session;
ANALYZE ods_log.ta202201;

3. 用子查询强制驱动表顺序 #

通过子查询先获取小范围的ta202201数据，再关联cookie_session，强制优化器以小结果集为驱动表：

select o.id, g.site_cookie
from (
    select id, uvcookie 
    from ods_log.ta202201 
    where id between 20000000 and 20000078
) o
join ods_overall_cookie.cookie_session g 
    on g.analyse_domain_cookie = o.uvcookie;

4. 调整join_collapse_limit参数 #

限制优化器合并子查询的数量，保留子查询的执行顺序：

set join_collapse_limit=1;

配合子查询写法，避免优化器重新调整表关联顺序。

内容的提问来源于stack exchange，提问作者linxiao yu