Statistics for deleted tables in system.table_statistics do not get removed

[jonstjohn]

Describe the problem

The problem was first noticed when performance of SHOW TABLES on a specific cluster slowed to > 60 seconds. A statement trace showed that most time was spent on the following query to the system.table_statistics table:

SELECT
  s."tableID", max(s."rowCount")
FROM
  system.table_statistics AS s
  JOIN (
      SELECT "tableID", max("createdAt") AS last_dt FROM system.table_statistics GROUP BY "tableID"
    )
      AS l ON l."tableID" = s."tableID" AND l.last_dt = s."createdAt"
    AS OF SYSTEM TIME '-10s'
GROUP BY
  s."tableID"

The system.table_statistics table had grown quite large (> 5 million rows) and appeared to retain statistics on deleted tables.

To Reproduce

Start a single node.

cockroach start-single-node --insecure

Query for the count of rows in system.table_statistics by database name. A watch provides repeated output of the command.

./cockroach sql --execute 'select t.database_name, count(ts."tableID") from system.table_statistics ts left join crdb_internal.tables t on ts."tableID" = t.table_id group by t.database_name;' --watch 10s --insecure

(it may take a couple of minutes for the statistics to show for system tables)

Set the gc.ttlseconds on the default range to 500 seconds to force garbage collection of the table:

ALTER RANGE DEFAULT CONFIGURE ZONE USING gc.ttlseconds = 300;

Create a database and table:

CREATE DATABASE d;
CREATE TABLE d.t (k uuid, v string);

Observe that rows in system.table_statistics are created for the table.
(it may take a couple of minutes for the statistics to show for the newly created table)

Drop the database:

SET sql_safe_updates=false;
DROP DATABASE d;

Observe that the rows for the table are not deleted, even after waiting until garbage collection completes (5 minutes).

Expected behavior
When a table is dropped, either directly or via dropping a database, the related rows in system.table_statistics are deleted. This prevents accumulation of orphaned rows that can slow down the performance of SHOW TABLES.

Additional data / screenshots
None

Environment:

• CockroachDB version: reproduced in 21.2.12 and 22.1.1
• Server OS: reproduced on MacOS but also observed on Linux
• Client app: cockroach sql

Additional context
May impact other queries or cluster operations.

Jira issue: CRDB-22733

[mgartner]

This is a known limitation - we don't delete old stats. Moving to backlog.

[jhaynie]

As a workaround, we're now using this in a periodic cleanup script:

DELETE FROM system.table_statistics WHERE "tableID" NOT IN (SELECT table_id FROM crdb_internal.tables);