Consider adding section on guardrails to avoid full scan

[rytaft]

Rebecca Taft (rytaft) commented:

In a recent support ticket, I provided information about ways to prevent the optimizer from planning a full scan. Currently this information is scattered in different places in the docs. It would be helpful to have a single page that covers all the different types of guardrails available to avoid expensive queries. I would suggest including at least the following:

• The NO_FULL_SCAN hint can be used on any DML statement to avoid a full scan. This guarantees the query will not perform a full scan on the table with the hint (the query will error if this is not possible).
• Use the disallow_full_table_scans and optionally large_full_scan_rows settings to disallow full scans or large full scans.
• Use transaction_rows_read_err and/or transaction_rows_written_err to disallow transactions reading and/or writing a certain number of rows. transaction_rows_read_log and transaction_rows_written_log just log transactions that read or write the specified number of rows and can be used to assess the impact of enabling the "err" versions of the settings.

cc @taroface

Jira Issue: DOC-9629

[exalate-issue-sync]

Shannon Bradshaw (shannonbradshaw) commented:
Rupert Harwood can you link this issue and https://cockroachdb.zendesk.com/agent/tickets/20162 ?

[exalate-issue-sync]

Rupert Harwood (rupertharwood-crl) commented:
Done.

[exalate-issue-sync]

Richard Loveland (rmloveland) commented:
Ryan Kuo FYI I recently added some content around avoiding full scans in this PR, which updates the SQL Performance Best Practices page (info below the line). Would probably be good to cross-link with whatever you add / update (PS I'm not intentionally peeping on your issues, I saw this when scanning the support DOC issues dashboard and it rang a bell with that other thing 🙂 )

---

h3. Disallow full table scans with the {{disallow_full_table_scans}} setting

To prevent overloading production clusters with full table scans, you have several options:

At the cluster level, configure the {{disallow_full_table_scans}} session setting for some or all users/roles using the {{ALTER ROLE}} statement.

{noformat}ALTER ROLE ALL SET disallow_full_table_scans = true;
{noformat}

At the application level, add the {{disallow_full_table_scans}} session setting to the connection string using the {{options}} parameter.

[exalate-issue-sync]

Ryan Kuo (taroface) commented:
Thanks Richard Loveland! I’ve already worked that section into the PR:

#18338