add guidance on using correct stats when tuning queries with EXPLAIN

[rytaft]

Rebecca Taft (rytaft) commented:

Opening a new ticket as requested in https://github.com/cockroachdb/docs/pull/18338#issuecomment-2021291246 cc @taroface

There was a support issue where the customer was tuning their queries and was concerned that the EXPLAIN output showed some full table scans. However, this was expected since the customer was testing with empty tables, and therefore the stats did not represent the production environment.

We should consider updating the docs to mention that most of the time the optimizer makes the right decision, and if you see a full scan you should (a) make sure that it's actually causing a performance problem, and (b) confirm that the optimizer would actually choose a full scan in the production environment (with correct stats, cluster topology, etc)? Only then should a customer try to override the optimizer's decision.

Jira Issue: DOC-10000