Search code examples
google-cloud-spanner

Understanding spanner explanation

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?

enter image description here

Solution

  • 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.