How can I update the statistics of a table in cockroachdb

Question

In cockroachdb when I explain my query i see this

root@:26257/management_console_production> explain select * from clusters;
                                    info
-----------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • scan
    estimated row count: 40 (100% of the table; stats collected 8 days ago)
    table: clusters@clusters_pkey
    spans: FULL SCAN
(7 rows)


Time: 2ms total (execution 2ms / network 0ms)

It shows above that stats were collected 8 days ago, how do i refresh the table statistics?

Answer 1

In CRDB the cost based optimizer uses these statistics to make better query plans. Table statistics are refreshed automatically based on usage patterns and some cluster settings

sql.stats.automatic_collection.fraction_stale_rows  (Target fraction of stale rows per table that will trigger a statistics refresh)

sql.stats.automatic_collection.min_stale_rows   (Target minimum number of stale rows per table that will trigger a statistics refresh)

You can manually update table statistics using CREATE STATISTICS or ANALYZE

root@:26257/management_console_production> analyze clusters;
ANALYZE


Time: 1.012s total (execution 1.012s / network 0.000s)

root@:26257/management_console_production> explain select * from clusters;
                                      info
--------------------------------------------------------------------------------
  distribution: local
  vectorized: true

  • scan
    estimated row count: 41 (100% of the table; stats collected 4 seconds ago)
    table: clusters@clusters_pkey
    spans: FULL SCAN
(7 rows)

See more about how table statistics works here