4

I created a test table in cloud spanner and populated it with 120 million rows. i have created a composite primary key for the table.

when i run a simple "select count(*) from " query, it takes approximately a minute for cloud spanner web UI to return results.

Is anyone else facing similar problem?

Maxim
  • 4,075
  • 1
  • 14
  • 23
Shailu Mishra
  • 61
  • 6
  • I'm not a database expert but, in SQL Server I have learned and switched my syntax in this case to use COUNT(1) rather than COUNT(*) as I had read somewhere that the * will cause the engine to resolve all the columns just as SELECT * does, and this will slow down the query similarly. – iGanja Jul 26 '22 at 16:44

2 Answers2

6

Cloud Spanner does not materialize counts, so queries will like "select count(*) ...." will scan the entire table to return the count of rows, hence the higher time to execute. If you require faster counts, recommend keeping a sharded counter updated transactionally with changes to the table.

samiz
  • 106
  • 1
1

@samiz - you answer "recommend keeping a sharded counter updated transactionally with changes to the table"

how can we detect how many sharded counter need for the table? there is no retry n transaction...

thank you

Chipintoza
  • 295
  • 4
  • 16