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?

Lienlienhard

FYI, full table scans are called out in the Explanation explicitly: "Table Scan: mytable (full scan: true)" – Bevon

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.

Thar

Yeah, that's make sense. I have edited the question to include the table definition. it uses a composed primary key. – Lienlienhard

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.

Demarcusdemaria