Why dose select distinct primary key use full scan?

Question

I have a table created by the following schema on cockroachdb

CREATE TABLE demo (
  table_id UUID NOT NULL, 
  tenant INT8 NOT NULL,
  ingestion_time INT8 NOT NULL, 
  message_count INT8 NOT NULL DEFAULT 0, 
  create_time TIMESTAMPTZ NOT NULL, 
  update_time TIMESTAMPTZ NOT NULL, 
  PRIMARY KEY (
     table_id, tenant, ingestion_time DESC, 
  )
);

There are only 10 unique table_id, and each table_id has about 1000 tenants. Each tenant have about 260,000 unique ingestion_time.

Why does select distinct(table_id) from demo use full scan (which will scan about 26,000,000,000) rather than just scan primary key prefix (10 unique table_id prefix) and return 10 table ids.

This also happens to select distinct(tenant) from demo where table_id = '288c248b-7f53-4dbf-b396-b75441238565' which also doesn't use the primary prefix of table_id/tenant

user_19694431 · Answer

This is a known limitation inside the CockroachDB optimizer, where loose index scans are not supported. See the following issue, which is related to adding support for this optimization: https://github.com/cockroachdb/cockroach/issues/24584.

Why dose select distinct primary key use full scan?

1 Answers