CREATE TABLE Account (
  ID STRING(100) NOT NULL,
  BSB STRING(20) NOT NULL,
  AccNum STRING(100) NOT NULL,
) PRIMARY KEY(ID);

In this Account table, ID is the only primary key. There’s no index on BSB and AccNum, and there’s no unique constrain on these 2 columns.
Apparently, BSB repeats more and AccNum is more like unique.
Assume, there are 10 million records in the table.
When I do the following query, I can see the query plan visualizer doesn’t show a full table scan. It says,

table scan on Account, rows returned:1″

SELECT * FROM account WHERE AccNum = "001"

But when I do the following query, the query does a full table scan. The query plan visualizer says

table scan on Account Full scan, row returned: 3,000,000″

SELECT * FROM account WHERE BSB = "100"

My questions:

1. Why the second SQL triggers full table scan but the first one doesn’t?
2. I think if the field in WHERE clause should incur a full table scan if the field doesn’t have an index on it. Is it right?
3. The first SQL takes 8ms, but the second SQL takes 30s. I can under stand the second one takes 30 seconds because of the full table scan. I want to know how Spanner actually looks for the records for the first SQL without a full table scan?