I have a Cloud Spanner table whose primary key ordering is unspecified, so it's ASC (ascending) by default. I have a query where I want to order the results by primary key in DESC (descending) order. Based on the Cloud Spanner documentation it seems that ORDER BY DESC is not the most efficient way to scan the table in reverse order. As I understand the correct way to perform reverse scans is to create a reverse index on the table. Is that the best approach, or is there something better I can do?
1 Answers
1
votes
That's correct, ORDER BY DESC is not currently as efficient in Cloud Spanner as scanning in key order if the key is in ASC order. You could create a secondary index with DESC ordering or you could use DESC ordering in the primary key of the the table itself (unfortunately, this would require you to re-create your table).
In general, any ORDER BY clause that doesn't match the table or index's ordering will require a sorting step and will thus impact performance. So an ORDER BY DESC scan will be performant if the table or index is ordered the same way. This gets particularly complex if some of the key components are ordered ASC and some are DESC, so it's best to ensure that the ORDER BY clause exactly matches the table or index's order. The important thing to understand is that Spanner stores data in primary key order (see https://cloud.google.com/spanner/docs/schema-and-data-model#primary_keys), and the storage format supports forward-scanning efficiently. For indexes, the primary key is the set of columns that you can use to look up items in the index.
