sql: index usage stats doesn't capture pk scan during index join

[Azhng]

Suppose we have a plan as follow:

root@:26257/defaultdb> explain SELECT c1,c2,c3
    FROM scandirection
    WHERE big_id BETWEEN 10 and 20;
                                         info
--------------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • index join
  │ estimated row count: 0
  │ table: scandirection@primary
  │
  └── • scan
        estimated row count: 0 (<0.01% of the table; stats collected 23 minutes ago)
        table: scandirection@idx_rev
        spans: [/20 - /10]

The index usage virtual table doesn't reflect the scan on the pk

root@:26257/defaultdb> SELECT ti.descriptor_name AS table_name, ti.index_name, total_reads, last_read
FROM crdb_internal.index_usage_statistics AS us
JOIN crdb_internal.table_indexes ti ON us.index_id = ti.index_id AND us.table_id = ti.descriptor_id
ORDER BY total_reads ASC;
   table_name   | index_name | total_reads |           last_read
----------------+------------+-------------+--------------------------------
  scandirection | primary    |           0 | NULL
  b2            | idx_big_id |           0 | NULL
  scandirection | idx_fw     |           0 | NULL
  contend       | primary    |           0 | NULL
  u             | primary    |           0 | NULL
  b1            | primary    |           0 | NULL
  b2            | primary    |           0 | NULL
  o             | primary    |           0 | NULL
  order         | primary    |           0 | NULL
  a             | primary    |           0 | NULL
  scandirection | idx_rev    |        2001 | 2022-02-07 17:01:01.901473+00

Jira issue: CRDB-13010

[Azhng]

Seems like during ConstructIndexJoin, the Scan operation on the Pk is planned directly without going through the ConstructScan method, (where the index usage is captured).

This means that we are definitely missing the usage stats during index join.

[glennfawcett]

Additionally, the total_reads column is useful but what I need even more is the total_read_rows to show how many rows were retrieved by for each index and pk.

[kevin-v-ngo]

Additionally, the total_reads column is useful but what I need even more is the total_read_rows to show how many rows were retrieved by for each index and pk.

Thanks for the feedback @glennfawcett! This is an improvement we're tracking. To help understand the scenario a bit more, what would you do/action you'd take knowing the number of rows retrieved for a particular index?