ORCA mixes up different indexes in different partitions.

[hlinnaka]

create table index_confusion (
col1 int,
col2 int,
col3 int,
col4 int,
col5 int,
col6 int,
col7 int,
col8 int,
col9 int,
col10 int,
col11 int,
col12 int) PARTITION BY RANGE (col1) (START (1) END (100000) EVERY (50000));

insert into index_confusion select g, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, g from generate_series(1, 10000) g;
analyze index_confusion;

CREATE INDEX i_a on index_confusion_1_prt_1 (col12);
CREATE INDEX i_b on index_confusion_1_prt_2 (col1, col2);

explain select * from index_confusion where col12 = '1';
select * from index_confusion where col12 = '1';

This happens:

                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..6.00 rows=1 width=48)
   ->  Sequence  (cost=0.00..6.00 rows=1 width=48)
         ->  Partition Selector for index_confusion (dynamic scan id: 1)  (cost=10.00..100.00 rows=34 width=4)
               Partitions selected: 2 (out of 2)
         ->  Dynamic Index Scan on index_confusion (dynamic scan id: 1)  (cost=0.00..6.00 rows=1 width=48)
               Index Cond: (col12 = 1)
 Planning time: 24.759 ms
 Optimizer: PQO version 3.11.0
(8 rows)

psql:a.sql:24: ERROR:  failed to find index for partition "index_confusion_1_prt_2" in dynamic index scan (nodeDynamicIndexscan.c:169)  (seg0 slice1 127.0.0.1:40000 pid=29337) (nodeDynamicIndexscan.c:169)

The culprit is the code in cdbpartindex.c, which tries to detect if all partitions in a partitioned table have compatible indexes. constructIndexHashKey() builds a "signature" string for each index. The string includes the column numbers in each index, and it confuses columns "1" and "2" with "12". Because of that, when one partition has an index on columns 1 and 2, and another partition has an index on column 12, it thinks that they are the same.

[lij55]

it is fixed on master/7 but still exits on 6.

test=# select * from index_confusion where col12 = '1';
 col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col9 | col10 | col11 | col12 
------+------+------+------+------+------+------+------+------+-------+-------+-------
    1 |    2 |    3 |    4 |    5 |    6 |    7 |    8 |    9 |    10 |    11 |     1

it works well if turn ORCA off on 6.

[ashwinstar]

@vraghavan78 please assess if wish to fix this for 6X_STABLE if not please feel free to close this issue as doesn't exist for gpdb7 and forward

[chrishajas]

I took a VERY quick look here, it might be as simple as adding a delimiter to the hash function. This issue only exists on 6X

[chrishajas]

Fixed in https://github.com/greenplum-db/gpdb/pull/15891