Add index create_date to crdb_internal.table_indexes

[kevin-v-ngo]

Knowing when an index was created will help developers determine whether they should drop the index if there has been no reads on that index. If the index was created 'far' in the past with no reads, it's a definite signal to drop. If it was recently created, the developer may want to hold off until they run their workload to see if the index will be used. This would complement crdb_internal.index_usage_statistics.

Epic: CRDB-9075

[ajwerner]

I suspect that there's more to fulfiling this user story than just tracking in the low-level SQL schema APIs. We've been bitten a bit in the past by not thinking through how we ultimately want to surface this in terms of the crdb_internal sql tables and the /admin APIs. What API endpoint is ultimately trying to have this information added to it?

[kevin-v-ngo]

Thanks - I thought all information surfaced in crdb_internal is accessible by the console - @lindseyjin and @maryliag, mind chiming in?

This information would be surfaced on the following page: https://www.figma.com/file/xdmwvnFQd6KkO9RJ0XLDH0/22.1_SQL-obsrv_query-performance?node-id=3498%3A33376

[maryliag]

We don't have an API for the index usage yet, @lindseyjin is working on this and the API will return the content of crdb_internal.index_usage_statistics so it can be displayed on the console. We use the values on the table indexes to generate the list of indexes for the index_usage_statistics, so the new column for creation date can be used the same way

[lindseyjin]

I suspect that there's more to fulfiling this user story than just tracking in the low-level SQL schema APIs. We've been bitten a bit in the past by not thinking through how we ultimately want to surface this in terms of the crdb_internal sql tables and the /admin APIs. What API endpoint is ultimately trying to have this information added to it?

Hi! I'm planning on creating a new API endpoint /_status/databases/{database}/tables/{table}/indexstats to surface index usage stats on the frontend.

Although another option I do have is piggybacking off the current Table Details endpoint at /_admin/v1/databases/{database}/tables/{table} since it already surfaces some index info. I think I currently prefer the first approach for reusability (in case we want to use the index stats request anywhere else), but let me know if any of you have other suggestions!