1. 文章
  2. 基础运维

Greenplum数据库left join不走索引的问题

osdba · 2022-04-18 11:18:42发表 · 45427 次点击
   0    0   

1. 问题

中启乘数科技是一家专业的Greenplum数据库服务商，在一个客户的环境中发现一张小表 left join 分区大表，经常不走索引。经过仔细测试，发现情况是：

  1. 一张小表 left join的大表必须是分区表，而且join的字段必须是varchar类型的字段是才不走索引。
  2. 一张小表 left join的普通大表，可以走到索引
  3. 一张小表 left join的大表，即使是分区表，但如果join健是 text类型或int类型，也是可以走到索引的。

2. 问题重现过程

建测试表：

  1. create table test_big(
  2.   id varchar(32),
  3.   t varchar(32)
  4. ) distributed by (id)
  5. partition by range(t)
  6. (
  7.    partition p1 start ('0') inclusive end ('5') exclusive,
  8.    partition p2 start ('5') inclusive end ('9999999999999999999') inclusive
  9. );
  11. insert into test_big select seq, seq from generate_series(1, 10000000) as seq;
  13. create index idx_test_big_id on test_big(id);
  16. create table test_small(
  17.   id varchar(32),
  18.   t varchar(32)
  19. ) distributed by (id);
  21. insert into test_small select seq*10000, seq*10000 from generate_series(1, 100) as seq;

看执行计划：

  1. postgres=# set Optimizer to off;
  2. SET
  4. postgres=# set enable_nestloop to on;
  5. SET
  7. postgres=# analyze test_big_1_prt_p1;
  8. ANALYZE
  9. postgres=# analyze test_big_1_prt_p2;
  10. ANALYZE
  12. postgres=# explain analyze select a.* from test_small a left join test_big b on a.id=b.id;
  13.                                                                          QUERY PLAN
  15. ---------------------------------------------------------------------------------------------------------------------------------------------
  16. ---------------
  17.  Gather Motion 2:1  (slice2; segments: 2)  (cost=4.25..350948.25 rows=100 width=12) (actual time=5428.774..5430.486 rows=100 loops=1)
  18.    ->  Hash Right Join  (cost=4.25..350948.25 rows=50 width=12) (actual time=44.883..5427.152 rows=61 loops=1)
  19.          Hash Cond: ((b.id)::text = (a.id)::text)
  20.          Extra Text: (seg1)   Hash chain length 1.0 avg, 1 max, using 61 of 524288 buckets.
  21.          ->  Redistribute Motion 2:2  (slice1; segments: 2)  (cost=0.00..313443.00 rows=5000000 width=7) (actual time=2.339..4486.409 rows=50
  22. 00055 loops=1)
  23.                Hash Key: b.id
  24.                ->  Append  (cost=0.00..113443.00 rows=5000000 width=7) (actual time=0.499..2188.017 rows=5000055 loops=1)
  25.                      ->  Seq Scan on test_big_1_prt_p1 b  (cost=0.00..50419.45 rows=2222223 width=7) (actual time=0.498..828.350 rows=2223115
  26.  loops=1)
  27.                      ->  Seq Scan on test_big_1_prt_p2 b_1  (cost=0.00..63023.55 rows=2777778 width=7) (actual time=0.234..527.056 rows=27786
  28. 15 loops=1)
  29.          ->  Hash  (cost=3.00..3.00 rows=50 width=12) (actual time=0.119..0.119 rows=61 loops=1)
  30.                ->  Seq Scan on test_small a  (cost=0.00..3.00 rows=50 width=12) (actual time=0.069..0.079 rows=61 loops=1)
  31.  Planning time: 0.861 ms
  32.    (slice0)    Executor memory: 127K bytes.
  33.    (slice1)    Executor memory: 75K bytes avg x 2 workers, 75K bytes max (seg0).
  34.    (slice2)    Executor memory: 4232K bytes avg x 2 workers, 4232K bytes max (seg0).  Work_mem: 3K bytes max.
  35.  Memory used:  128000kB
  36.  Optimizer: Postgres query optimizer
  37.  Execution time: 5432.691 ms
  38. (18 rows)

发现不走索引。

但是如果是普通的 inner join是可以走索引的：

  1. postgres=# explain analyze select a.* from test_small a join test_big b on a.id=b.id;
  2.                                                                                 QUERY PLAN
  4. ---------------------------------------------------------------------------------------------------------------------------------------------
  5. ------------------------------
  6.  Gather Motion 2:1  (slice2; segments: 2)  (cost=0.18..66891.00 rows=100 width=12) (actual time=84.616..99.975 rows=100 loops=1)
  7.    ->  Nested Loop  (cost=0.18..66891.00 rows=50 width=12) (actual time=43.385..94.197 rows=61 loops=1)
  8.          ->  Broadcast Motion 2:2  (slice1; segments: 2)  (cost=0.00..6.00 rows=100 width=12) (actual time=1.052..1.165 rows=100 loops=1)
  9.                ->  Seq Scan on test_small a  (cost=0.00..3.00 rows=50 width=12) (actual time=0.055..0.065 rows=61 loops=1)
  10.          ->  Append  (cost=0.18..334.40 rows=1 width=7) (actual time=0.106..0.937 rows=1 loops=100)
  11.                ->  Index Only Scan using test_big_1_prt_p1_id_idx on test_big_1_prt_p1 b  (cost=0.18..167.20 rows=1 width=7) (actual time=0.0
  12. 49..0.587 rows=0 loops=100)
  13.                      Index Cond: (id = (a.id)::text)
  14.                      Heap Fetches: 0
  15.                ->  Index Only Scan using test_big_1_prt_p2_id_idx on test_big_1_prt_p2 b_1  (cost=0.18..167.20 rows=1 width=7) (actual time=0
  16. .056..0.345 rows=0 loops=100)
  17.                      Index Cond: (id = (a.id)::text)
  18.                      Heap Fetches: 0
  19.  Planning time: 1.046 ms
  20.    (slice0)    Executor memory: 200K bytes.
  21.    (slice1)    Executor memory: 58K bytes avg x 2 workers, 58K bytes max (seg0).
  22.    (slice2)    Executor memory: 280K bytes avg x 2 workers, 280K bytes max (seg0).
  23.  Memory used:  128000kB
  24.  Optimizer: Postgres query optimizer
  25.  Execution time: 102.337 ms
  26. (18 rows)

如果直接join分区，是可以直接走索引的：

  1. postgres=# explain analyze select a.* from test_small a left join test_big_1_prt_p1 b on a.id=b.id;
  2.                                                                             QUERY PLAN
  4. ---------------------------------------------------------------------------------------------------------------------------------------------
  5. ---------------------
  6.  Gather Motion 2:1  (slice1; segments: 2)  (cost=0.18..16724.00 rows=100 width=12) (actual time=4.751..5.025 rows=100 loops=1)
  7.    ->  Nested Loop Left Join  (cost=0.18..16724.00 rows=50 width=12) (actual time=0.250..2.501 rows=61 loops=1)
  8.          ->  Seq Scan on test_small a  (cost=0.00..3.00 rows=50 width=12) (actual time=0.056..0.074 rows=61 loops=1)
  9.          ->  Index Only Scan using test_big_1_prt_p1_id_idx on test_big_1_prt_p1 b  (cost=0.18..167.20 rows=1 width=7) (actual time=0.002..0.
  10. 038 rows=0 loops=61)
  11.                Index Cond: (id = (a.id)::text)
  12.                Heap Fetches: 0
  13.  Planning time: 0.740 ms
  14.    (slice0)    Executor memory: 96K bytes.
  15.    (slice1)    Executor memory: 164K bytes avg x 2 workers, 164K bytes max (seg0).
  16.  Memory used:  128000kB
  17.  Optimizer: Postgres query optimizer
  18.  Execution time: 7.444 ms
  19. (12 rows)

如果join健是text类型则没有这个问题

建测试表：

  1. create table test2_big(
  2.   id text,
  3.   t text
  4. ) distributed by (id)
  5. partition by range(t)
  6. (
  7.    partition p1 start ('0') inclusive end ('5') exclusive,
  8.    partition p2 start ('5') inclusive end ('9999999999999999999') inclusive
  9. );
  11. insert into test2_big select seq, seq from generate_series(1, 10000000) as seq;
  13. create index idx_test2_big_id on test2_big(id);
  16. create table test2_small(
  17.   id text,
  18.   t text
  19. ) distributed by (id);
  21. insert into test2_small select seq*10000, seq*10000 from generate_series(1, 100) as seq;
  23. postgres=# analyze test2_big_1_prt_p1;
  24. ANALYZE
  25. postgres=# analyze test2_big_1_prt_p2;
  26. ANALYZE

看执行计划：

  1. postgres=# set Optimizer to off;
  2. SET
  4. postgres=# set enable_nestloop to on;
  5. SET
  7. postgres=# explain analyze select a.* from test2_small a left join test2_big b on a.id=b.id;
  8.                                                                                  QUERY PLAN
  10. ---------------------------------------------------------------------------------------------------------------------------------------------
  11. -------------------------------
  12.  Gather Motion 2:1  (slice1; segments: 2)  (cost=0.18..33445.00 rows=100 width=12) (actual time=2.988..4.136 rows=100 loops=1)
  13.    ->  Nested Loop Left Join  (cost=0.18..33445.00 rows=50 width=12) (actual time=0.113..1.983 rows=61 loops=1)
  14.          ->  Seq Scan on test2_small a  (cost=0.00..3.00 rows=50 width=12) (actual time=0.011..0.030 rows=61 loops=1)
  15.          ->  Append  (cost=0.18..334.40 rows=1 width=7) (actual time=0.002..0.031 rows=1 loops=61)
  16.                ->  Index Only Scan using test2_big_1_prt_p1_id_idx on test2_big_1_prt_p1 b  (cost=0.18..167.20 rows=1 width=7) (actual time=0
  17. .000..0.013 rows=0 loops=61)
  18.                      Index Cond: (id = a.id)
  19.                      Heap Fetches: 0
  20.                ->  Index Only Scan using test2_big_1_prt_p2_id_idx on test2_big_1_prt_p2 b_1  (cost=0.18..167.20 rows=1 width=7) (actual time
  21. =0.001..0.015 rows=1 loops=61)
  22.                      Index Cond: (id = a.id)
  23.                      Heap Fetches: 0
  24.  Planning time: 0.810 ms
  25.    (slice0)    Executor memory: 128K bytes.
  26.    (slice1)    Executor memory: 268K bytes avg x 2 workers, 268K bytes max (seg0).
  27.  Memory used:  128000kB
  28.  Optimizer: Postgres query optimizer
  29.  Execution time: 6.018 ms
  30. (16 rows)

3. 问题解决

问题已经定位，是bug，需要修改代码解决，如有兴趣，可以邮件customer@csudata.com

本站文章，未经作者同意，请勿转载，如需转载，请邮件customer@csudata.com.
0 评论  
添加一条新评论

用户登录

个人首页
立即登录