Tables with missing stats
Advertisement
RStoolKit Results: Tables with missing stats
RedShift is maintaining the statistics for all the tables to generate a better query plan. If the stats are or out of date, then your queries may perform slower then what you expected.
If table statistics are missing or out of date, you might see the following:
- A warning message in EXPLAIN command results.
- A missing statistics alert event in STL_ALERT_EVENT_LOG. For more information, see Reviewing query alerts.
Tables with stale stats (> 5 percent)
This issue is a slight different from the missing stats. Here the table has statistics. But it is out of date. After the stats gathered, if you perform any more deletes and updates then the table’s stats need to be updates. Stale statistics can lead to suboptimal query execution plans and long execution times.
Find the tables with missing stats:
with cte as (
SELECT
query,
trim(replace(replace(plannode, '-', ''), 'Tables missing statistics:', '')) as table_name
FROM
stl_explain
WHERE
plannode LIKE '%missing statistics%'
AND plannode NOT LIKE '%redshift_auto_health_check_%'
GROUP BY
query,
plannode
ORDER BY
2 DESC)
select
c."schema",
c."table",
count(a.table_name)
from
cte a
join stl_scan b on
a.query = b.query
and a.table_name = b.perm_table_name
join pg_catalog.svv_table_info c on
b.tbl = c.table_id
group by
"schema",
"table";
Find the tables with stale stats:
select
"schema",
"table"
FROM
svv_table_info
WHERE
stats_off > 5;
How to fix this problem:
To fix this issue, run the ANALYZE query on those tables.
ANALYZE [ VERBOSE ]
[ [ table_name [ ( column_name [, ...] ) ] ]
[ PREDICATE COLUMNS | ALL COLUMNS ]
Note: To reduce processing time and improve overall system performance, Amazon Redshift skips ANALYZE for a table if the percentage of rows that have changed since the last ANALYZE command run is lower than the analyze threshold specified by the analyze_threshold_percent parameter. By default, analyze_threshold_percent is 10. To change analyze_threshold_percent for the current session, execute the SET command. The following example changes analyze_threshold_percent to 20 percent.
set analyze_threshold_percent to 0.01;
External Links:
- Vacuum options in RedShift
- Analyze in RedShift
- RedShift vacuum Utility - Python Based
- Automate Vacuum Analyze Utility - Shell based with more control
Loading Comments