Understanding spanner explanation

Question

I've a table of 860M rows in Google Cloud Spanner and I'm trying to understand how explanation works.

The table has a string column geoid and there is an index at this column.

When I run the following query it takes only 36ms:

SELECT count(*)
FROM usbg_2015
WHERE geoid= '340170175001'

Table structure is:

CREATE TABLE usbg_2015 (
    geoid STRING(12),
    quadkey STRING(24),
) PRIMARY KEY (geoid, quadkey)

However, I don't understand why the explanation says it uses a Table Scan instead of an Index Scan. I understood a Table scan as a full scan of the table, in this case reading 860M rows and it should take more time than 36ms. What I'm missing?

Answer 1

In the explanation, Table Scan merely means that it reads the data from a table and does not necessarily mean a full table scan. Same goes for index scan. It means that it is reading from an index. In both cases, if there is a seekable predicate (e.g., constant prefixes on primary key or indexed column), they will do the seek.

The plan used the base table, and seek-and-scanned 11 rows, otherwise you would see 860M rows returned as a result out of the Table Scan.

Is geoid the leading primary key column of the table usbg_2015? That is the only explanation that I can think of given the plan.

Answer 2

table scan have two meanings:

1- when you search by a primary key (or in your case the first part of it)

2- When you perform am index scan and have in the select list a column that isn't neither in the index nor in the storing clause, than you have to join the index with the table itself. This operation is called table scan.