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

在一个生产环境中发现一张小表 left join 分区大表，经常不走索引。经过仔细测试，发现情况是：

1.一张小表 left join的大表必须是分区表，而且join的字段必须是varchar类型的字段是才不走索引。

2.一张小表 left join的普通大表，可以走到索引。

3.一张小表 left join的大表，即使是分区表，但如果join健是 text类型或int类型，也是可以走到索引的。

建测试表：

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. );
   

10. 
    

11. insert into test_big select seq, seq from generate_series(1,10000000)as seq;
    

12. 
    

13. create index idx_test_big_id on test_big(id);
    

14. 
    

15. 
    

16. create table test_small(
    

17. id varchar(32),
    

18. t varchar(32)
    

19. ) distributed by(id);
    

20. 
    

21. insert into test_small select seq*10000, seq*10000from generate_series(1,100)as seq;
    

看执行计划：

1. postgres=#setOptimizer to off;
   

2. SET
   

3. 
   

4. postgres=#set enable_nestloop to on;
   

5. SET
   

6. 
   

7. postgres=# analyze test_big_1_prt_p1;
   

8. ANALYZE
   

9. postgres=# analyze test_big_1_prt_p2;
   

10. ANALYZE
    

11. 
    

12. postgres=# explain analyze select a.*from test_small a left join test_big b on a.id=b.id;
    

13. QUERY PLAN
    

14. 
    

15. ---------------------------------------------------------------------------------------------------------------------------------------------
    

16. ---------------
    

17. GatherMotion2:1(slice2; segments:2)(cost=4.25..350948.25 rows=100 width=12)(actual time=5428.774..5430.486 rows=100 loops=1)
    

18. ->HashRightJoin(cost=4.25..350948.25 rows=50 width=12)(actual time=44.883..5427.152 rows=61 loops=1)
    

19. HashCond:((b.id)::text =(a.id)::text)
    

20. ExtraText:(seg1)Hash chain length 1.0 avg,1 max,using61 of 524288 buckets.
    

21. ->RedistributeMotion2: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. HashKey: b.id
    

24. ->Append(cost=0.00..113443.00 rows=5000000 width=7)(actual time=0.499..2188.017 rows=5000055 loops=1)
    

25. ->SeqScan 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. ->SeqScan 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. ->SeqScan 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
   

3. 
   

4. ---------------------------------------------------------------------------------------------------------------------------------------------
   

5. ------------------------------
   

6. GatherMotion2:1(slice2; segments:2)(cost=0.18..66891.00 rows=100 width=12)(actual time=84.616..99.975 rows=100 loops=1)
   

7. ->NestedLoop(cost=0.18..66891.00 rows=50 width=12)(actual time=43.385..94.197 rows=61 loops=1)
   

8. ->BroadcastMotion2:2(slice1; segments:2)(cost=0.00..6.00 rows=100 width=12)(actual time=1.052..1.165 rows=100 loops=1)
   

9. ->SeqScan 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. ->IndexOnlyScanusing 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. IndexCond:(id =(a.id)::text)
    

14. HeapFetches:0
    

15. ->IndexOnlyScanusing 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. IndexCond:(id =(a.id)::text)
    

18. HeapFetches: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
   

3. 
   

4. ---------------------------------------------------------------------------------------------------------------------------------------------
   

5. ---------------------
   

6. GatherMotion2:1(slice1; segments:2)(cost=0.18..16724.00 rows=100 width=12)(actual time=4.751..5.025 rows=100 loops=1)
   

7. ->NestedLoopLeftJoin(cost=0.18..16724.00 rows=50 width=12)(actual time=0.250..2.501 rows=61 loops=1)
   

8. ->SeqScan on test_small a (cost=0.00..3.00 rows=50 width=12)(actual time=0.056..0.074 rows=61 loops=1)
   

9. ->IndexOnlyScanusing 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. IndexCond:(id =(a.id)::text)
    

12. HeapFetches: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. );
   

10. 
    

11. insert into test2_big select seq, seq from generate_series(1,10000000)as seq;
    

12. 
    

13. create index idx_test2_big_id on test2_big(id);
    

14. 
    

15. 
    

16. create table test2_small(
    

17. id text,
    

18. t text
    

19. ) distributed by(id);
    

20. 
    

21. insert into test2_small select seq*10000, seq*10000from generate_series(1,100)as seq;
    

22. 
    

23. postgres=# analyze test2_big_1_prt_p1;
    

24. ANALYZE
    

25. postgres=# analyze test2_big_1_prt_p2;
    

26. ANALYZE
    

看执行计划：

1. postgres=#setOptimizer to off;
   

2. SET
   

3. 
   

4. postgres=#set enable_nestloop to on;
   

5. SET
   

6. 
   

7. postgres=# explain analyze select a.*from test2_small a left join test2_big b on a.id=b.id;
   

8. QUERY PLAN
   

9. 
   

10. ---------------------------------------------------------------------------------------------------------------------------------------------
    

11. -------------------------------
    

12. GatherMotion2:1(slice1; segments:2)(cost=0.18..33445.00 rows=100 width=12)(actual time=2.988..4.136 rows=100 loops=1)
    

13. ->NestedLoopLeftJoin(cost=0.18..33445.00 rows=50 width=12)(actual time=0.113..1.983 rows=61 loops=1)
    

14. ->SeqScan 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. ->IndexOnlyScanusing 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. IndexCond:(id = a.id)
    

19. HeapFetches:0
    

20. ->IndexOnlyScanusing 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. IndexCond:(id = a.id)
    

23. HeapFetches: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)
    

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

未经作者同意，请勿转载，如需转载，请邮件customer@csudata.com.