Major Description
When we transform a scan into a nested join of an index and its base table, this join is on the clustering key columns, which are present and unique in both the index and the base table.
Therefore, this is a 1:1 join with unique matches on each side. There is no use in a probe cache, since we will never probe for the same base table key twice.
Right now we see a probe cache, though. There are a few reasons and I'm not sure whether this is a regression or an old bug.
The reason: When we create the index join, in IndexJoinRule1::makeSubstituteFromIndexInfo() in file core/sql/optimizer/TransRule.cpp, we don't synthesize logical properties for the join. This is correct, in principle, because its logical properties are the same as those of the scan. However, the synthLogProp() method also sets some local variables in the join that are used to determine whether a probe cache is useful: leftHasUniqueMatches_ and rightHasUniqueMatches_.
Here is a test case:
create table tdi(service_id int not null,
user_number int not null,
start_time timestamp not null,
b int,
primary key(service_id, user_number, start_time))
division by (date_trunc('day', start_time))
salt using 4 partitions on (user_number);
create index tdix on tdi(user_number, start_time) salt like table;
control query shape join(cut,cut);
prepare s from
select * from tdi where user_number = 1234 and start_time between timestamp '2017-01-01 00:00:00' and timestamp '2017-01-15 00:00:00';
explain options 'f' s;
The explain shows a PROBE_CACHE, which is unnecessary.