Amazon Redshift Tables with Missing Statistics

Some of your Amazon Redshift source’s tables may be missing statistics. Information on these are stored in the STL_EXPLAIN table which is where all of the EXPLAIN plan for each of the queries that is submitted to your source for execution are displayed.

In this tutorial we will show you a fairly simple query that can be run against your cluster’s STL table showing your pertinent information on the missing statistics. This query will have an output of two columns, and they are:

• PLANNODE
• Count

The Breakdown

1. PLANNODE
   • This column is a substring of the plan node where plannode contains the words “missing statistics as dictated by the WHERE clause.
2. Count
   • Using count (*) this column will show the number of occurrences of this specific statistic.

The Query

SELECT

	substring(trim(plannode),1,100) as plannode,

	count(*)

FROM

	stl_explain

WHERE

	plannode like '%missing statistics%'

GROUP BY plannode

ORDER BY 2 DESC

REFERENCES:

https://docs.aws.amazon.com/redshift/latest/dg/r_STL_EXPLAIN.html

https://docs.aws.amazon.com/redshift/latest/dg/diagnostic-queries-for-query-tuning.html#identify-queries-that-are-top-candidates-for-tuning

The above query was made available by Amazon Redshift’s support documentation and was sourced from that site.