3

it looks like the optimizer is doing table scan instead of index scan if I select columns that are not part of the index!.

I created the following table and index

CREATE TABLE customer_lastseen_products (
 customer_ref_value STRING(50) NOT NULL,
 customer_ref_type STRING(20) NOT NULL,
 sku_config STRING(40) NOT NULL,
 mp_code STRING(20) NOT NULL,
 is_added_to_cart BOOL NOT NULL,
 is_purchased BOOL NOT NULL,
l ast_visit_time TIMESTAMP OPTIONS( allow_commit_timestamp = true)
)PRIMARY KEY(customer_ref_value, sku_config),
ROW DELETION POLICY (OLDER_THAN(last_visit_time, INTERVAL 30 DAY))

and index

CREATE INDEX customercodeIndex3 ON customer_lastseen_products(customer_ref_value, customer_ref_type, mp_code, last_visit_time DESC);

But this query is doing full table scan

SELECT
sku_config , is_added_to_cart, is_purchased
FROM customer_lastseen_products
WHERE(customer_ref_value, customer_ref_type) in (('0f2e9ed9-2d5e-4c78-b03f-0c6dd3f65598', 'customer_code'), ('', 'visitor_id'))
AND mp_code = "mp"
AND last_visit_time between '2020-10-03T12:35:59' and '2022-10-03T12:35:59'
order by last_visit_time desc
Omar Ahmed
  • 53
  • 3

1 Answers1

2

According to internal documentation, it is encouraged to use the directive FORCE_INDEX in order to improve the consistency of queries by specifying the index you would like to use. To add on that, I was able to observe that the Cloud Spanner's query optimizer may take up to 3 days to start using an index after it's creation, as it requires time to collect the database's statistics.

Please find the documentation article here.

Andrés
  • 487
  • 1
  • 12