We executed the same parameterized query on two different spanner instances both instances utilizing version 2 of the optimizer. On one instance the optimizer picked the right instance causing only 30 rows to be read and on the other instance the optimizer picked no index and hence a table scan of 3 million rows was performed on this high volume executed parameterized query. The datasets are different between the two instances. However, with 3 million rows available for data distribution statistics, we think the optimizer should have the right information for picking the right index.
The query was very simple like below:
Select col1, col2 col3 from table_a where col4 = @parameter1 and col5 = @parameter2
The index was on col4, col5 storing col3. It has to read the base table for the col1, col2
Is there anyway to reset the execution plan of a parameterized query on Spanner?
Is there anyway to inspect the data distribution information for a table/index?
Any other suggestions on how to determine why the optimizer did not choose the index would be appreciated. We really do not want to have to force the index on Spanner queries if possible.
As per google support, you cannot reset a parameterized execution plan and you are not able to see the data distribution information for a table/index.
Still waiting on a reply from google support why two environments pick different execution plans. I would think it is some how related to the data in each table in each environment which in my case is not identical.