Learn how database statistics and query planners work to create efficient execution plans, and how to optimize them for better performance.
Learn how database statistics and query planners work to create efficient execution plans, and how to optimize them for better performance.
An estimated 30-40% of database performance issues can be traced back to suboptimal query plans caused by outdated or inaccurate statistics. While indexes provide the pathways for faster access, statistics tell the database which pathways are worth taking.
This lesson will demystify database statistics and the query planning process, empowering you to:
Understand how statistics are collected, stored, and used
See the planner's logic in choosing between different access paths
Learn to influence plan selection through statistics management
Troubleshoot and fix common statistics-related performance issues
Design schemas and write queries with statistics in mind
Let's unveil the "black box" of database planning and learn to work with it, rather than against it.
Database statistics are essentially a set of metadata that describe the characteristics of tables, columns, and indexes. They help the query optimizer estimate the cost of different execution plans and choose the most efficient one.
Databases collect and maintain several types of statistics:
Table statistics
: Total row count, total pages/blocks
Column statistics
: Number of distinct values, most common values, data distribution (histograms)
Index statistics
: Index height, clustering factor, distinct values per index
Correlation statistics
: Relationship between columns (multi-column statistics)
Two fundamental concepts in database statistics are cardinality and selectivity:
Cardinality
The number of distinct values in a column or index. High cardinality means many distinct values (like a unique ID column), while low cardinality means few distinct values (like a boolean or status column with 3-4 possible values).
customer_id
(1,000,000 distinct values in a table with 1,000,000 rows) → High cardinality
(3 distinct values in a table with 1,000,000 rows) → Low cardinality
Selectivity
The ratio of distinct values to the total number of rows. Higher selectivity (closer to 1.0) means a predicate will filter out more rows, making it more "selective".
customer_id
(1,000,000 distinct values in a table with 1,000,000 rows) → Selectivity: 1.0
(3 distinct values in a table with 1,000,000 rows) → Selectivity: 0.000003
The selectivity of a column has a major impact on how the optimizer treats it:
High selectivity columns
(like unique keys) are excellent candidates for indexes and are typically good for equality conditions.
Low selectivity columns
(like status flags) are generally poor candidates for standalone indexes, as using them would still retrieve a large portion of the table.
Beyond simple metrics like cardinality, modern databases maintain information about the actual distribution of values in a column using histograms.
Types of Histograms
Equi-width Histograms
Divide the value range into equal intervals, counting how many values fall in each bucket.
Equi-height Histograms
Divide the data into buckets with approximately the same number of values in each.
Top-N Frequency Histograms
Track the N most common values separately, with their exact frequencies.
Hybrid Approaches
Modern databases often use combinations of these methods for better accuracy.
Histograms help the optimizer handle data skew, where some values appear much more frequently than others. Without this information, the optimizer might make poor decisions for queries that target popular or rare values.
Different database systems collect statistics in different ways:
PostgreSQL
Automatic collection by
Manual collection via
Statistics stored in
pg_statistic
Sampling-based approach (default: 300 * sampling_multiplier rows)
MySQL/MariaDB
Persistent statistics in InnoDB (stored in internal system tables)
Manual update using
ANALYZE TABLE
Index dives for cardinality estimation
Histogram statistics available in newer versions
SQL Server
Automatic collection through
AUTO_CREATE_STATISTICS
Manual update using
UPDATE STATISTICS
Statistics stored in internal tables
Sampling or full scans based on table size
Automatic collection by
GATHER_STATS_JOB
Manual collection using
Statistics stored in data dictionary
Supports method_opt for histogram creation
When Statistics Need Updating
While most databases automatically update statistics, you may need to manually update them after:
Bulk data loads or deletions
Significant schema changes
Creating new indexes
When distribution of data changes significantly
When you observe unexplained performance degradation
Each database provides ways to view the statistics it collects:
PostgreSQL
Understanding statistics is the first step to mastering query planning—in the next section, we'll explore how these statistics are used to generate execution plans.
The query planner is the brain that transforms SQL queries into execution plans. Let's explore how it uses statistics to make decisions and how you can influence those decisions.
1-- View column statistics (user-friendly format)
2SELECT * FROM pg_stats
3WHERE tablename = 'orders' AND attname = 'order_date';
4
5-- View table statistics
6SELECT * FROM pg_class WHERE relname = 'orders';
7
8-- View index statistics
9SELECT * FROM pg_stats
10WHERE tablename = 'orders_idx' AND attname = '0';1-- View table and index statistics
2SHOW TABLE STATUS LIKE 'orders';
3
4-- View index statistics
5SHOW INDEX FROM orders;
6
7-- View histogram statistics (MySQL 8.0+)
8SELECT * FROM information_schema.COLUMN_STATISTICS
9WHERE table_name = 'orders' AND column_name = 'order_date';At a high level, query planning follows these steps:
Parse the query
into an internal representation (parse tree)
Rewrite the query
by applying transformation rules (view expansion, predicate pushdown, etc.)
Generate candidate plans
by exploring different access methods, join orders, and join algorithms
Estimate the cost
of each plan using statistics and cost models
Select the lowest-cost plan
for execution
Simplified Query Planning Process
phase is where statistics are heavily used to estimate costs.
The planner estimates the cost of each operation using statistics and a cost model. Key factors include:
: Reading from disk or memory
: Processing rows, applying filters, etc.
Network costs
: Transferring data between nodes (in distributed systems)
Memory usage
: Sorting, hashing, etc.
Row Estimation Examples
Let's explore how the planner uses statistics to estimate the number of rows returned by different operations:
Estimating Simple Predicates
has 4 distinct values and the table has 1,000,000 rows, the planner estimates approximately 250,000 rows (assuming even distribution). If a histogram shows 'completed' actually represents 40% of all orders, the estimate would be closer to 400,000 rows.
Estimating Range Predicates
The planner uses histograms to estimate what fraction of values fall within the range. If the histogram shows 5% of
values fall in January 2023, the estimate would be 50,000 rows (5% of 1,000,000).
Estimating JOIN Operations
For joins, the planner uses the "join selectivity" formula. If
has 1,000,000 rows and
has 100,000 rows, with a 1:10 relationship, the join is estimated to return 1,000,000 rows (each order joins with exactly one customer).
Estimating Multiple Conditions
For multiple conditions, the planner combines individual selectivities, assuming independence between conditions (which is not always true). If 40% of orders are 'completed' and 30% have
total_amount > 100
, the estimate would be 120,000 rows (40% × 30% × 1,000,000).
The Independence Assumption Problem
Most query planners assume that predicates are independent, which can lead to estimation errors when columns are correlated. For example, if
order_value
customer_tier
are correlated (higher-tier customers tend to place larger orders), the planner might significantly underestimate or overestimate result sizes.
Some databases provide ways to capture these correlations:
PostgreSQL
: Extended statistics with
CREATE STATISTICS
SQL Server
: Multi-column statistics
: Column groups in
1-- Equality condition on a column
2SELECT * FROM orders WHERE status = 'completed';1-- Range condition on a column
2SELECT * FROM orders
3WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';1-- Join between two tables
2SELECT * FROM orders o
3JOIN customers c ON o.customer_id = c.customer_id;1-- Multiple conditions
2SELECT * FROM orders
3WHERE status = 'completed'
4AND total_amount > 100;One of the most critical decisions the planner makes is determining the order in which to join tables. The number of possible join orders grows factorially with the number of tables:
2 tables: 2 possible orders
3 tables: 6 possible orders
4 tables: 24 possible orders
5 tables: 120 possible orders
10 tables: 3,628,800 possible orders!
To handle this complexity, most query planners use heuristic algorithms:
Greedy algorithms
: Build the plan incrementally, choosing the best table to join next
Dynamic programming
: Build optimal plans for subsets of tables, then combine them
Genetic algorithms
: Start with random plans and evolve them through mutations and crossover
The planner uses statistics to estimate the size of each intermediate result and choose the most efficient path.
For each table in the query, the planner decides how to access the data:
Sequential Scan
Read all rows from the table sequentially. Cost is proportional to the table size.
When chosen
: When a large portion of the table needs to be read (typically >5-20% of rows, depending on the database).
Index Scan
Use an index to find qualifying rows, then fetch them from the table.
When chosen
: When a small portion of the table needs to be read and an appropriate index exists.
Index Only Scan
Use an index that contains all the columns needed by the query, avoiding table access.
When chosen
: When all required columns are in the index and a small portion of the table needs to be read.
Bitmap Scan
Use an index to create a bitmap of qualifying rows, then fetch them in physical order.
When chosen
: When a moderate portion of the table needs to be read, combining sequential access efficiency with index filtering.
The decision between these access methods relies heavily on statistics:
How many rows will match the condition? (selectivity)
How many disk pages will need to be read?
How random or sequential will the access pattern be?
What's the cost of processing each row?
The planner also chooses which join algorithm to use for each join operation:
Nested Loop Join
For each row from the outer table, scan the inner table for matches.
When chosen
: When the outer table is small and the inner table has an index on the join column.
Build a hash table from the smaller table, then probe it with rows from the larger table.
When chosen
: For equi-joins when one table is significantly smaller than the other.
Merge Join
Sort both tables on the join column (or use indexes), then merge them together.
When chosen
: When both tables are already sorted (e.g., by an index) or when the result needs to be sorted anyway.
Other Join Types
Databases may have specialized join algorithms like batch nested loop, index nested loop, etc.
When chosen
: Based on specific data characteristics and optimization strategies.
Again, the choice relies on statistics like table sizes, join column distributions, and available memory.
Most databases cache execution plans to avoid repeatedly planning the same queries:
Prepared statements
: Plans are generated once and reused with different parameters
Plan cache
: Plans for recent queries are kept in memory
Parameterized plans
: Generic plans that work for any parameter values
However, this can sometimes lead to performance issues when a plan optimal for one parameter value is not optimal for another. This is known as the "parameter sniffing" problem in SQL Server or "plan stability" issues in other databases.
Influencing the Query Planner
While you should generally trust the query planner, there are times when you may need to influence its decisions:
Update statistics
when data changes significantly
query hints
to force specific access paths or join methods when needed
appropriate indexes
to give the planner more options
cost parameters
to better reflect your hardware
schema design
with statistics in mind (e.g., avoid correlated columns in WHERE clauses)
Now that we understand how the planner works, let's look at how to manage statistics effectively and address common issues.
Managing database statistics effectively is crucial for consistent query performance. Let's explore how to keep statistics up-to-date, configure them optimally, and address common issues.
Each database system offers settings to control how and when statistics are collected:
PostgreSQL Statistics Configuration
Automatic Collection Parameters
Manual Collection Options
MySQL Statistics Configuration
System Variables
Manual Collection Options
1-- Control frequency of autovacuum
2ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.1; -- Default
3ALTER SYSTEM SET autovacuum_analyze_threshold = 50; -- Default
4
5-- Control statistics detail level
6ALTER SYSTEM SET default_statistics_target = 100; -- Default1-- Analyze all tables in database
2ANALYZE;
3
4-- Analyze specific table with custom stats target
5ANALYZE VERBOSE orders;
6
7-- Create extended statistics for correlated columns
8CREATE STATISTICS order_stats (dependencies)
9ON order_date, status FROM orders;1-- Control histogram generation (MySQL 8.0+)
2SET GLOBAL innodb_stats_persistent_sample_pages = 20; -- Default
3SET GLOBAL innodb_stats_persistent = ON; -- Default
4
5-- Control statistics persistence
6SET GLOBAL innodb_stats_auto_recalc = ON; -- Default1-- Update statistics for a table
2ANALYZE TABLE orders;
3
4-- Create histograms on specific columns
5ANALYZE TABLE orders UPDATE HISTOGRAM
6ON order_date, status WITH 100 BUCKETS;
7
8-- Drop histograms when no longer needed
9ANALYZE TABLE orders DROP HISTOGRAM
10ON order_date, status;Most databases use sampling to collect statistics, balancing accuracy with collection time. You can adjust these settings based on your needs:
Small Tables
For small tables (<10,000 rows), consider analyzing the entire table for maximum accuracy.
Medium Tables
For medium tables (10,000-10,000,000 rows), default sampling is usually sufficient.
Large Tables
For large tables (>10,000,000 rows), consider increasing statistics targets for important columns.
Balancing Accuracy and Overhead
There's a trade-off between statistics accuracy and collection overhead:
More detailed statistics
: Better query plans, but slower collection and more storage
More frequent updates
: More accurate statistics, but higher system load
Larger samples
: Better accuracy, but longer collection time
Find the right balance for your workload. Critical tables and columns might deserve more detailed and frequent statistics, while less important ones can use defaults.
1-- PostgreSQL (100% sampling)
2ALTER TABLE small_table SET (autovacuum_analyze_scale_factor = 0);
3ALTER TABLE small_table SET (autovacuum_analyze_threshold = 1);1-- PostgreSQL (default settings)
2-- No special configuration needed
3-- Default statistics target: 100
4-- Default scale factor: 0.1 (10% of rows)1-- PostgreSQL (increase statistics detail)
2ALTER TABLE large_table ALTER COLUMN important_col
3SET STATISTICS 1000; -- More detailed statistics
4
5-- Reduce sampling frequency to avoid overhead
6ALTER TABLE large_table SET (autovacuum_analyze_scale_factor = 0.05);
7ALTER TABLE large_table SET (autovacuum_analyze_threshold = 1000);While automatic statistics collection works well in most cases, there are situations where manual updates are necessary:
After Bulk Operations
When you insert, update, or delete a large number of rows, automatic statistics collection might not trigger immediately.
After Schema Changes
When you add indexes, constraints, or make significant schema changes, statistics may need updating.
Before Critical Operations
Before running important queries, reports, or batch jobs, ensure statistics are up-to-date.
When Troubleshooting Performance
If query performance suddenly degrades, outdated statistics might be the cause.
1-- After bulk operations
2-- PostgreSQL
3ANALYZE orders;
4
5-- MySQL
6ANALYZE TABLE orders;
7
8-- SQL Server
9UPDATE STATISTICS orders;
10
11-- Oracle
12BEGIN
13 DBMS_STATS.GATHER_TABLE_STATS(
14 ownname => 'schema',
15 tabname => 'ORDERS'
16 );
17END;1-- After adding an index
2CREATE INDEX idx_order_date ON orders(order_date);
3ANALYZE orders; -- Update statistics for the new index
4
5-- After adding columns with default values
6ALTER TABLE orders ADD COLUMN priority INT DEFAULT 1;
7ANALYZE orders; -- Update statistics to include the new column1-- Before running month-end reports
2ANALYZE orders;
3ANALYZE customers;
4ANALYZE products;
5
6-- Then run your reports
7SELECT /* critical report query */ ...1-- Check statistics age in PostgreSQL
2SELECT relname, last_analyze, last_autoanalyze
3FROM pg_stat_all_tables
4WHERE relname = 'orders';
5
6-- Update if old or if plan changed unexpectedly
7ANALYZE VERBOSE orders;One of the most common causes of poor plans is the "independence assumption" we mentioned earlier. Modern databases offer ways to capture correlations between columns:
PostgreSQL Extended Statistics
SQL Server Column Statistics
1-- Create extended statistics for functional dependencies
2CREATE STATISTICS order_dependencies (dependencies)
3ON customer_id, shipping_country FROM orders;
4
5-- Create extended statistics for n-distinct counts
6CREATE STATISTICS product_stats (ndistinct)
7ON category, subcategory, brand FROM products;
8
9-- View extended statistics
10SELECT * FROM pg_statistic_ext;
11
12-- Use the ANALYZE command to collect the extended statistics
13ANALYZE orders;
14ANALYZE products;1-- Create statistics on multiple columns
2CREATE STATISTICS order_stats
3ON orders(customer_id, shipping_country);
4
5-- Update the statistics
6UPDATE STATISTICS orders order_stats WITH FULLSCAN;
7
8-- View statistics information
9DBCC SHOW_STATISTICS('orders', order_stats);Regular monitoring of statistics health can prevent performance issues before they impact users:
PostgreSQL Statistics Monitoring
MySQL Statistics Monitoring
1-- Check statistics age
2SELECT
3 schemaname,
4 relname,
5 n_live_tup AS row_count,
6 last_analyze,
7 last_autoanalyze,
8 analyze_count,
9 autoanalyze_count
10FROM pg_stat_all_tables
11WHERE schemaname NOT LIKE 'pg_%'
12 AND schemaname != 'information_schema'
13ORDER BY n_live_tup DESC
14LIMIT 20;
15
16-- Find tables with stale statistics
17SELECT
18 schemaname,
19 relname,
20 n_live_tup,
21 last_analyze,
22 last_autoanalyze
23FROM pg_stat_all_tables
24WHERE (last_analyze IS NULL OR
25 last_analyze < now() - interval '1 week')
26 AND (last_autoanalyze IS NULL OR
27 last_autoanalyze < now() - interval '1 week')
28 AND n_live_tup > 1000
29ORDER BY n_live_tup DESC;1-- Check table statistics
2SELECT
3 table_schema,
4 table_name,
5 engine,
6 table_rows,
7 avg_row_length,
8 data_length,
9 index_length
10FROM information_schema.tables
11WHERE table_schema NOT IN
12 ('mysql', 'information_schema', 'performance_schema')
13ORDER BY table_rows DESC
14LIMIT 20;
15
16-- Check index statistics
17SELECT
18 table_name,
19 index_name,
20 column_name,
21 cardinality,
22 seq_in_index
23FROM information_schema.statistics
24WHERE table_schema = 'your_database'
25 AND table_name = 'orders'
26ORDER BY index_name, seq_in_index;Several performance issues can be traced back to statistics problems:
Outdated Statistics
: Sudden performance degradation, especially after data changes; queries that used to use indexes now doing sequential scans.
: Update statistics manually; check automatic statistics collection configuration.
Correlated Column Predicates
: Queries with multiple WHERE conditions returning far fewer or far more rows than estimated.
: Create extended/multi-column statistics; consider rewriting queries to avoid complex predicates.
Skewed Data Distribution
: Different performance for different parameter values; queries that are fast for some values but slow for others.
: Increase statistics target for affected columns; consider histograms; in extreme cases, use query hints.
Statistics Collection Overhead
: High CPU or I/O during statistics collection; slow operations during maintenance windows.
: Adjust sampling rates; schedule collection during off-peak hours; be selective about which tables need detailed statistics.
While you should generally trust the optimizer, sometimes you need to provide hints or ensure plan stability:
PostgreSQL Plan Management
SQL Server Plan Management
When to Use Hints
Use hints only as a last resort when:
You've verified that statistics are up-to-date and accurate
You've tried restructuring the query
You have detailed knowledge about the data that the optimizer doesn't
You need consistent performance regardless of data changes
You've tested thoroughly and confirmed that your hint consistently improves performance
Remember that hints can become harmful as data changes or when the database is upgraded with better optimization strategies.
With effective statistics management, you can ensure that your database consistently chooses optimal execution plans, leading to better performance and user experience.
Each database system has unique features and capabilities for statistics management. Let's explore the specifics of PostgreSQL, MySQL, and SQL Server to help you get the most out of your database's statistics capabilities.
1-- Force sequential scan (when you know it's better)
2SELECT /*+ SeqScan(orders) */ * FROM orders
3WHERE order_date > '2023-01-01';
4
5-- Use pg_hint_plan extension for more complex hints
6SELECT /*+ IndexScan(orders idx_order_date) */
7FROM orders WHERE order_date > '2023-01-01';
8
9-- Or use prepared statements with custom plans
10PREPARE order_plan(date) AS
11SELECT * FROM orders WHERE order_date > $1;
12
13EXECUTE order_plan('2023-01-01');1-- Force a specific index
2SELECT * FROM orders WITH (INDEX(idx_order_date))
3WHERE order_date > '2023-01-01';
4
5-- Use Query Store to force a specific plan
6-- First, find the plan you want
7SELECT qsp.plan_id, qsp.query_id, qst.query_sql_text
8FROM sys.query_store_plan qsp
9JOIN sys.query_store_query qsq
10 ON qsp.query_id = qsq.query_id
11JOIN sys.query_store_query_text qst
12 ON qsq.query_text_id = qst.query_text_id
13WHERE qst.query_sql_text LIKE '%orders%';
14
15-- Then force the plan
16EXEC sp_query_store_force_plan @query_id=42, @plan_id=142;PostgreSQL offers a robust statistics system with several advanced features:
PostgreSQL collects various types of statistics:
Basic statistics
: Stored in
pg_statistic
(internal) and exposed through
(user-friendly view)
Extended statistics
: Stored in
pg_statistic_ext
and exposed through
pg_stats_ext
Runtime statistics
pg_statio_*
views for monitoring
Most Common Values lists track exact frequencies for the most frequent values in a column.
Histograms
Histograms represent value distribution for columns with many distinct values.
Extended Statistics
Extended statistics capture correlations between multiple columns.
Expression Statistics
Statistics on expressions used frequently in WHERE clauses.
1-- Set statistics target for a column (1-10000, default 100)
2-- Higher values = more detailed histograms and more frequent MCV lists
3ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;
4
5-- Set storage parameters for a table to control automatic analysis
6ALTER TABLE orders SET (
7 autovacuum_analyze_scale_factor = 0.05, -- Analyze after 5% of rows change (default 0.1)
8 autovacuum_analyze_threshold = 100 -- Plus this many rows (default 50)
9);
10
11-- Create extended statistics for correlated columns
12CREATE STATISTICS customer_order_stats (dependencies, ndistinct)
13ON customer_id, order_total, order_status FROM orders;1-- View MCV list for a column
2SELECT attname, most_common_vals, most_common_freqs
3FROM pg_stats
4WHERE tablename = 'orders'
5AND attname = 'status';
6
7-- Increase MCV list size for a column with skewed data
8ALTER TABLE orders
9ALTER COLUMN status SET STATISTICS 1000;1-- View histogram details for a column
2SELECT attname, histogram_bounds
3FROM pg_stats
4WHERE tablename = 'orders'
5AND attname = 'order_date';
6
7-- More histogram buckets = more accurate estimates
8ALTER TABLE orders
9ALTER COLUMN order_date SET STATISTICS 500;1-- Create statistics capturing functional dependencies
2CREATE STATISTICS customer_order_deps (dependencies)
3ON customer_id, country FROM customers;
4
5-- Create statistics for accurate distinct counts
6CREATE STATISTICS product_category_stats (ndistinct)
7ON product_id, category_id FROM products;
8
9-- View extended statistics information
10SELECT * FROM pg_stats_ext;1-- Create statistics for an expression
2CREATE STATISTICS year_month_stats
3ON (EXTRACT(YEAR FROM order_date),
4 EXTRACT(MONTH FROM order_date))
5FROM orders;
6
7-- Create an expression index (which also collects statistics)
8CREATE INDEX idx_order_year_month
9ON orders ((EXTRACT(YEAR FROM order_date)),
10 (EXTRACT(MONTH FROM order_date)));1-- Check when statistics were last collected
2SELECT
3 schemaname,
4 relname,
5 last_analyze,
6 last_autoanalyze,
7 n_mod_since_analyze,
8 analyze_count,
9 autoanalyze_count
10FROM pg_stat_all_tables
11WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
12AND relname = 'orders';
13
14-- Check actual vs. estimated rows for recent queries
15SELECT
16 query,
17 calls,
18 rows,
19 mean_exec_time,
20 shared_blks_hit,
21 shared_blks_read
22FROM pg_stat_statements
23WHERE query ILIKE '%orders%'
24AND query NOT ILIKE '%pg_stat_statements%'
25ORDER BY mean_exec_time DESC
26LIMIT 10;MySQL and MariaDB have their own approaches to statistics management:
InnoDB, the default storage engine for MySQL, stores statistics in internal system tables:
You can configure statistics settings for individual tables:
MySQL 8.0 introduced histogram statistics for better selectivity estimates:
MySQL provides an optimizer trace feature to see how statistics affect plan choices:
1-- Enable persistent statistics (default in newer versions)
2SET GLOBAL innodb_stats_persistent = ON;
3
4-- Configure sampling for statistics collection
5SET GLOBAL innodb_stats_persistent_sample_pages = 20; -- Default
6
7-- Control automatic recalculation (default: ON)
8SET GLOBAL innodb_stats_auto_recalc = ON;
9
10-- Control recalculation threshold
11SET GLOBAL innodb_stats_modified_counter = 0; -- % of rows modified before auto-recalc1-- Set table-specific statistics settings
2ALTER TABLE orders STATS_PERSISTENT = 1; -- Use persistent statistics
3ALTER TABLE orders STATS_AUTO_RECALC = 1; -- Auto-recalculate
4ALTER TABLE orders STATS_SAMPLE_PAGES = 50; -- Use larger sample
5
6-- Manually update statistics
7ANALYZE TABLE orders;
8
9-- View statistics stored in information schema
10SELECT * FROM information_schema.STATISTICS
11WHERE table_schema = 'your_database'
12AND table_name = 'orders';1-- Create histogram statistics
2ANALYZE TABLE orders UPDATE HISTOGRAM
3ON customer_id, order_date, status WITH 100 BUCKETS;
4
5-- View histograms
6SELECT * FROM information_schema.COLUMN_STATISTICS
7WHERE table_name = 'orders';
8
9-- Drop histograms when no longer needed
10ANALYZE TABLE orders DROP HISTOGRAM ON customer_id;1-- Enable optimizer trace
2SET optimizer_trace = 'enabled=on';
3
4-- Run your query
5SELECT * FROM orders WHERE order_date > '2023-01-01';
6
7-- View the trace
8SELECT * FROM information_schema.OPTIMIZER_TRACE;
9
10-- Disable optimizer trace when done
11SET optimizer_trace = 'enabled=off';SQL Server has a sophisticated statistics system with several unique features:
SQL Server supports filtered statistics for better estimates on specific data subsets:
SQL Server provides several ways to view and manage statistics:
SQL Server's Query Store feature helps identify plan regressions related to statistics:
1-- Check auto-create and auto-update settings for a database
2SELECT name, is_auto_create_stats_on, is_auto_update_stats_on
3FROM sys.databases
4WHERE name = 'YourDatabase';
5
6-- Enable automatic statistics management
7ALTER DATABASE YourDatabase
8SET AUTO_CREATE_STATISTICS ON,
9 AUTO_UPDATE_STATISTICS ON,
10 AUTO_UPDATE_STATISTICS_ASYNC OFF;
11
12-- Manually create statistics
13CREATE STATISTICS order_status_stats
14ON orders(status)
15WITH FULLSCAN; -- or SAMPLE x PERCENT
16
17-- Manually update statistics
18UPDATE STATISTICS orders order_status_stats
19WITH FULLSCAN;1-- Create filtered statistics
2CREATE STATISTICS recent_order_stats
3ON orders(customer_id, total_amount)
4WHERE order_date > DATEADD(YEAR, -1, GETDATE())
5WITH FULLSCAN;
6
7-- Update filtered statistics
8UPDATE STATISTICS orders(recent_order_stats) WITH FULLSCAN;
9
10-- View statistics information
11DBCC SHOW_STATISTICS('orders', recent_order_stats);1-- View statistics on a table
2DBCC SHOW_STATISTICS('orders', idx_order_date);
3
4-- View statistics metadata
5SELECT
6 OBJECT_NAME(s.object_id) AS table_name,
7 s.name AS statistics_name,
8 s.auto_created,
9 s.user_created,
10 s.filter_definition,
11 s.is_temporary,
12 sp.last_updated,
13 sp.rows,
14 sp.rows_sampled,
15 sp.modification_counter
16FROM sys.stats s
17CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
18WHERE OBJECT_NAME(s.object_id) = 'orders';
19
20-- Find outdated statistics
21SELECT
22 OBJECT_NAME(s.object_id) AS TableName,
23 s.name AS StatisticsName,
24 sp.last_updated,
25 sp.modification_counter
26FROM sys.stats s
27CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
28WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
29AND sp.modification_counter > 0
30ORDER BY sp.modification_counter DESC;1-- Enable Query Store
2ALTER DATABASE YourDatabase
3SET QUERY_STORE = ON (
4 OPERATION_MODE = READ_WRITE,
5 CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
6 DATA_FLUSH_INTERVAL_SECONDS = 900,
7 INTERVAL_LENGTH_MINUTES = 60,
8 MAX_STORAGE_SIZE_MB = 1000,
9 QUERY_CAPTURE_MODE = ALL
10);
11
12-- Find queries with plan regression
13SELECT
14 qt.query_sql_text,
15 qp.plan_id,
16 qp.count_executions,
17 qp.avg_duration,
18 qp.last_execution_time
19FROM sys.query_store_query_text qt
20JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
21JOIN sys.query_store_plan qp ON q.query_id = qp.query_id
22WHERE qt.query_sql_text LIKE '%orders%'
23ORDER BY qp.last_execution_time DESC;Oracle Database has a comprehensive statistics management system via the DBMS_STATS package:
Oracle offers several histogram types and controls:
Best Practices for All Databases
Regular maintenance
: Schedule statistics updates during low-traffic periods
Critical tables
: Keep statistics more up-to-date on tables that change frequently and are used in important queries
Monitoring
: Track statistics age and use it as a factor in performance troubleshooting
: Test the impact of different statistics settings in a development environment before applying to production
Documentation
: Document any custom statistics settings for future reference
By leveraging the specific statistics features of your database system, you can ensure more accurate query plans and better performance for your applications.
Understanding how statistics and the query planner work enables you to design databases and write queries that are "statistics-friendly." This proactive approach helps ensure consistent performance and reduces the need for manual intervention.
1-- Gather statistics for a table
2BEGIN
3 DBMS_STATS.GATHER_TABLE_STATS(
4 ownname => 'SCHEMA_NAME',
5 tabname => 'ORDERS',
6 estimate_percent => 20,
7 method_opt => 'FOR ALL COLUMNS SIZE AUTO',
8 cascade => TRUE
9 );
10END;
11
12-- Gather statistics for a schema
13BEGIN
14 DBMS_STATS.GATHER_SCHEMA_STATS(
15 ownname => 'SCHEMA_NAME',
16 options => 'GATHER AUTO',
17 estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
18 method_opt => 'FOR ALL COLUMNS SIZE AUTO',
19 degree => DBMS_STATS.AUTO_DEGREE
20 );
21END;1-- Create histograms with specific sizing
2BEGIN
3 DBMS_STATS.GATHER_TABLE_STATS(
4 ownname => 'SCHEMA_NAME',
5 tabname => 'ORDERS',
6 method_opt => 'FOR COLUMNS SIZE 254 order_date,
7 FOR COLUMNS SIZE 10 status,
8 FOR COLUMNS SIZE SKEWONLY customer_id'
9 );
10END;
11
12-- Create extended statistics for multiple columns
13BEGIN
14 DBMS_STATS.CREATE_EXTENDED_STATS(
15 ownname => 'SCHEMA_NAME',
16 tabname => 'ORDERS',
17 extension => '(order_date, status)'
18 );
19END;1-- Configure automatic statistics job
2BEGIN
3 DBMS_AUTO_TASK_ADMIN.ENABLE(
4 client_name => 'auto optimizer stats collection',
5 operation => NULL,
6 window_name => NULL
7 );
8END;
9
10-- Set statistics preferences for the entire database
11BEGIN
12 DBMS_STATS.SET_GLOBAL_PREFS(
13 pname => 'STALE_PERCENT',
14 pvalue => '10' -- Consider stats stale after 10% changes
15 );
16
17 DBMS_STATS.SET_GLOBAL_PREFS(
18 pname => 'ESTIMATE_PERCENT',
19 pvalue => 'AUTO_SAMPLE_SIZE'
20 );
21END;1-- View statistics information
2SELECT
3 table_name,
4 num_rows,
5 blocks,
6 last_analyzed
7FROM all_tables
8WHERE owner = 'SCHEMA_NAME'
9AND table_name = 'ORDERS';
10
11-- View column statistics
12SELECT
13 column_name,
14 num_distinct,
15 density,
16 num_nulls,
17 histogram
18FROM all_tab_columns
19WHERE owner = 'SCHEMA_NAME'
20AND table_name = 'ORDERS';
21
22-- View stale statistics
23SELECT
24 t.table_name,
25 t.num_rows,
26 t.last_analyzed,
27 m.inserts,
28 m.updates,
29 m.deletes
30FROM all_tables t
31JOIN all_tab_modifications m
32 ON t.table_name = m.table_name
33WHERE t.owner = 'SCHEMA_NAME'
34AND m.table_owner = 'SCHEMA_NAME'
35AND (m.inserts + m.updates + m.deletes) > 0.1 * t.num_rows;Good schema design takes into account how statistics will be collected and used:
Primary Key Selection
: Choose primary keys that have high cardinality (many distinct values) and are monotonically increasing or random (like UUIDs) to avoid hotspots
Foreign Key Design
: Consider cardinality when designing relationships—the optimizer uses statistics about foreign keys to estimate join sizes
Index Column Order
: Place high-selectivity columns first in multi-column indexes to improve statistics usefulness
Statistics-Unfriendly Design
Statistics-Friendly Design
Choose data types that are efficient for the optimizer to work with:
Use precise types
: INTEGER instead of VARCHAR for numeric IDs
Right-size your columns
: Use the smallest data type that fits your needs
Normalize when appropriate
: Consider normalizing highly repetitive data to improve statistics
Problematic Data Types
Optimal Data Types
Table partitioning can help with statistics management in large tables:
Partition by access pattern
: Partition tables so that most queries target specific partitions
Per-partition statistics
: Benefit from more granular statistics that better represent data subsets
Faster statistics collection
: Update statistics only on changing partitions
Partitioning Benefits for Statistics
More accurate histograms
: Partition-level statistics capture data distribution better than table-wide statistics
Partition pruning
: The optimizer can eliminate partitions based on statistics before execution
Reduced collection overhead
: Only need to update statistics for active partitions
Better parallelism
: Statistics collection can be done in parallel across partitions
1-- Low-cardinality primary key (only a few values)
2CREATE TABLE regions (
3 region_code CHAR(1) PRIMARY KEY, -- Only A-Z possible
4 region_name VARCHAR(50) NOT NULL
5);
6
7-- Multi-column index with low-cardinality first
8CREATE INDEX idx_status_date ON orders(
9 status, -- Low cardinality (few distinct values)
10 order_date -- High cardinality (many distinct values)
11);1-- High-cardinality primary key
2CREATE TABLE regions (
3 region_id SERIAL PRIMARY KEY, -- Unique for each region
4 region_code CHAR(1) UNIQUE, -- Business key as separate column
5 region_name VARCHAR(50) NOT NULL
6);
7
8-- Multi-column index with high-cardinality first
9CREATE INDEX idx_date_status ON orders(
10 order_date, -- High cardinality first
11 status -- Low cardinality second
12);1-- Strings for numeric data
2CREATE TABLE orders (
3 order_id VARCHAR(20) PRIMARY KEY,
4 customer_id VARCHAR(20) NOT NULL,
5 amount VARCHAR(10) NOT NULL, -- Stored as string
6 order_date VARCHAR(10) NOT NULL -- Date as string
7);1-- Appropriate types for each column
2CREATE TABLE orders (
3 order_id BIGINT PRIMARY KEY,
4 customer_id INTEGER NOT NULL,
5 amount DECIMAL(10,2) NOT NULL, -- Numeric type
6 order_date DATE NOT NULL -- Date type
7);1-- PostgreSQL partitioning example
2CREATE TABLE orders (
3 order_id BIGINT,
4 customer_id INTEGER NOT NULL,
5 amount DECIMAL(10,2) NOT NULL,
6 order_date DATE NOT NULL,
7 PRIMARY KEY (order_date, order_id)
8) PARTITION BY RANGE (order_date);
9
10-- Create partitions
11CREATE TABLE orders_2023_q1 PARTITION OF orders
12 FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
13
14CREATE TABLE orders_2023_q2 PARTITION OF orders
15 FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');
16
17-- Update statistics on only the latest partition
18ANALYZE orders_2023_q2;Writing queries with statistics in mind can lead to more consistent and predictable performance:
The way you write WHERE conditions can significantly impact how statistics are used:
Statistics-Unfriendly Predicates
Statistics-Friendly Predicates
Structuring joins to work well with the optimizer's statistics:
Statistics-Unfriendly Joins
Statistics-Friendly Joins
How you structure complex queries affects statistics usage:
Potential Statistics Challenges
Better Approaches
Be aware of how parameterization affects plan selection:
Parameter Sniffing Problems
Parameter Sniffing Solutions
1-- Functions on indexed columns
2SELECT * FROM orders
3WHERE EXTRACT(YEAR FROM order_date) = 2023;
4
5-- Calculations in WHERE clause
6SELECT * FROM products
7WHERE price * 0.9 < 100;
8
9-- Combining unrelated columns
10SELECT * FROM orders
11WHERE order_date > '2023-01-01' OR status = 'pending';1-- Range condition on original column
2SELECT * FROM orders
3WHERE order_date >= '2023-01-01'
4 AND order_date < '2024-01-01';
5
6-- Move calculation to the other side
7SELECT * FROM products
8WHERE price < 100 / 0.9;
9
10-- Separate unrelated conditions
11-- or use UNION for OR conditions on different columns
12SELECT * FROM orders WHERE order_date > '2023-01-01'
13UNION
14SELECT * FROM orders WHERE status = 'pending';1-- Joining on expressions
2SELECT o.*, c.*
3FROM orders o
4JOIN customers c
5 ON LOWER(o.customer_email) = LOWER(c.email);
6
7-- Join without appropriate indexes
8SELECT p.*, c.*
9FROM products p
10JOIN categories c ON p.category_id = c.category_id
11WHERE p.price > 100;
12
13-- Mixing many-to-many relationships without proper filtering
14SELECT *
15FROM orders o
16JOIN order_items oi ON o.order_id = oi.order_id
17JOIN products p ON oi.product_id = p.product_id;1-- Join on indexed columns directly
2SELECT o.*, c.*
3FROM orders o
4JOIN customers c ON o.customer_id = c.customer_id;
5
6-- Apply filters before joining large tables
7SELECT p.*, c.*
8FROM (SELECT * FROM products WHERE price > 100) p
9JOIN categories c ON p.category_id = c.category_id;
10
11-- Use proper multi-column statistics and filtering
12SELECT o.order_id, p.name, SUM(oi.quantity)
13FROM orders o
14JOIN order_items oi ON o.order_id = oi.order_id
15JOIN products p ON oi.product_id = p.product_id
16WHERE o.order_date > '2023-01-01'
17GROUP BY o.order_id, p.name;1-- Correlated subquery in SELECT
2SELECT o.order_id,
3 (SELECT MAX(oi.price)
4 FROM order_items oi
5 WHERE oi.order_id = o.order_id) AS max_item_price
6FROM orders o;
7
8-- Non-deterministic functions in CTEs
9WITH random_orders AS (
10 SELECT *
11 FROM orders
12 WHERE RANDOM() < 0.5
13)
14SELECT * FROM random_orders;1-- Join instead of correlated subquery
2SELECT o.order_id, MAX(oi.price) AS max_item_price
3FROM orders o
4JOIN order_items oi ON o.order_id = oi.order_id
5GROUP BY o.order_id;
6
7-- Deterministic filtering in CTEs
8WITH recent_orders AS (
9 SELECT *
10 FROM orders
11 WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
12)
13SELECT * FROM recent_orders;1-- SQL Server procedure with parameter sniffing issue
2CREATE PROCEDURE GetOrdersByStatus
3 @status VARCHAR(20)
4AS
5BEGIN
6 SELECT * FROM orders
7 WHERE status = @status;
8END;
9
10-- Calling with a common status (could use index scan)
11EXEC GetOrdersByStatus 'completed';
12
13-- Calling with a rare status (could use table scan)
14EXEC GetOrdersByStatus 'cancelled';1-- SQL Server procedure with local variable
2CREATE PROCEDURE GetOrdersByStatus
3 @status VARCHAR(20)
4AS
5BEGIN
6 DECLARE @localStatus VARCHAR(20) = @status;
7
8 SELECT * FROM orders
9 WHERE status = @localStatus;
10END;
11
12-- Or with RECOMPILE hint
13CREATE PROCEDURE GetOrdersByStatus
14 @status VARCHAR(20)
15AS
16BEGIN
17 SELECT * FROM orders
18 WHERE status = @status
19 OPTION (RECOMPILE);
20END;Extend statistics awareness to your application design:
Eager loading configuration
: Configure ORMs to use efficient join strategies based on data volume
Query batching
: Batch related queries to leverage prepared statements and plan reuse
Statistics-aware query generation
: Consider generating different SQL for high vs. low volume data scenarios
Design bulk operations with statistics updates in mind:
Query routing
: Route queries to different versions based on parameter values and data distribution
Cached execution plans
: Consider application-level plan caching for complex, frequent queries
Statistics-based query generation
: Dynamically generate different query structures based on data characteristics
Statistics-Aware Development Checklist
Schema design
: Choose appropriate keys, data types, and normalization levels
Index design
: Create indexes with statistics and query patterns in mind
Query patterns
: Write SARGable queries that work well with statistics
Join strategies
: Consider cardinality and distribution when designing joins
Parameter handling
: Be aware of parameter sniffing and plan reuse issues
Bulk operations
: Update statistics after significant data changes
Application logic
: Consider how application patterns affect database statistics usage
By proactively designing your database and applications with statistics in mind, you can achieve more consistent performance, reduce the need for manual tuning, and create systems that scale gracefully as data volumes grow.
In this practical example, we'll diagnose and fix a common statistics-related performance issue. We have a reporting query that used to be fast but suddenly became slow after a data load operation.
1// Example with TypeORM (Node.js)
2
3// Less efficient for large datasets
4// May not use statistics effectively
5const users = await userRepository.find({
6 relations: ['orders', 'profile', 'payments']
7});
8
9// More statistics-friendly approach
10// Separate queries with specific joins and conditions
11const users = await userRepository.find();
12const userIds = users.map(user => user.id);
13
14const orders = await orderRepository
15 .createQueryBuilder('order')
16 .where('order.userId IN (:...userIds)', { userIds })
17 .getMany();
18
19const profiles = await profileRepository
20 .createQueryBuilder('profile')
21 .where('profile.userId IN (:...userIds)', { userIds })
22 .getMany();
23
24// Now associate the data in-memory
25// This allows the database to use statistics independently for each query1-- PostgreSQL transaction with statistics update
2BEGIN;
3-- Perform bulk operation
4INSERT INTO orders SELECT * FROM staging_orders;
5-- Update statistics immediately after bulk operation
6ANALYZE orders;
7COMMIT;
8
9-- MySQL with bulk operations
10START TRANSACTION;
11-- Load data in bulk
12LOAD DATA INFILE 'data.csv' INTO TABLE orders;
13-- Update statistics
14ANALYZE TABLE orders;
15COMMIT;1// Example application-level statistics awareness in Node.js
2
3async function getOrdersByStatus(status, db) {
4 // Use database-maintained statistics to make decisions
5 const statusStats = await db.query(
6 "SELECT most_common_vals FROM pg_stats WHERE tablename = 'orders' AND attname = 'status'"
7 );
8
9 const isCommonStatus = statusStats.rows[0].most_common_vals.includes(status);
10
11 if (isCommonStatus) {
12 // For common statuses, use index scan approach
13 return db.query(
14 "SELECT * FROM orders WHERE status = $1",
15 [status]
16 );
17 } else {
18 // For rare statuses, potentially use a different approach
19 // like a full scan with additional filters
20 return db.query(
21 "SELECT * FROM orders WHERE status = $1 AND order_date > current_date - interval '90 days'",
22 [status]
23 );
24 }
25}Following our initial query, let's examine what's going on step by step:
Step 1: Analyze the Execution Plan
The execution plan reveals a sequence of sequential scans instead of index scans, particularly on the orders table. The planner is choosing inefficient access methods because it has inaccurate statistics about our tables after the recent data load.
Seq Scan on orders o (cost=0.00..18245.67 rows=345987 width=24)
Filter: (order_date >= '2023-01-01'::date AND order_date <= '2023-12-31'::date)
... [additional plan details] ...
Notice that the planner chose a sequential scan on the orders table, even though we have an index on order_date. The estimated row count (345,987) is much higher than the actual number of rows that should match the date range.
Step 2: Check Statistics Age
The statistics age query shows that our tables haven't been analyzed since before the data load:
schemaname | relname | row_count | last_analyze | last_autoanalyze
-----------+--------------+-----------+----------------------+----------------
public | orders | 1250000 | 2023-06-15 10:45:22 | null
public | customers | 150000 | 2023-06-15 10:45:30 | null
public | order_items | 3500000 | 2023-06-15 10:45:40 | null
public | products | 25000 | 2023-06-15 10:45:45 | null
The row counts don't reflect our recent data load, and the last_analyze timestamps are from before the data import. This confirms our suspicion that we're working with outdated statistics.
Step 3: Examine Column Statistics
Let's look at the histogram and correlation information for the critical columns:
tablename | attname | n_distinct | correlation | most_common_vals | most_common_freqs
----------+-------------+------------+-------------+---------------------+------------------
orders | order_date | 150 | 0.85 |
orders | customer_id | 100000 | 0.25 | null | null
customers | status | 3 | -0.15 |
Notice the order_date column's most_common_vals don't include any dates from 2023, and the n_distinct value is too low. This confirms the statistics don't reflect our new data.
Now that we've diagnosed the issue, let's fix it by updating statistics on the relevant tables:
Step 1: Update Statistics on All Tables
Step 2: Increase Statistics Target for Critical Columns
Since the date range is important for our query performance, we'll increase the statistics target for the order_date column to get a more detailed histogram:
Step 3: Create Extended Statistics for Correlated Columns
The WHERE clause combines conditions on order_date and customer status, which might be correlated. Let's create extended statistics:
Step 4: Verify the New Plan
Let's run EXPLAIN ANALYZE again to see the new plan with updated statistics:
Index Scan using idx_orders_date on orders o (cost=0.43..8150.89 rows=125632 width=24)
Index Cond: (order_date >= '2023-01-01'::date AND order_date <= '2023-12-31'::date)
... [additional plan details showing index usage] ...
Now the planner correctly uses indexes and has a much more accurate row estimate!
1-- Update statistics for all tables involved in the query
2ANALYZE orders;
3ANALYZE customers;
4ANALYZE order_items;
5ANALYZE products;
6
7-- Or do them all at once
8ANALYZE;1-- Increase statistics detail for the critical columns
2ALTER TABLE orders ALTER COLUMN order_date SET STATISTICS 1000;
3ALTER TABLE customers ALTER COLUMN status SET STATISTICS 500;
4
5-- Re-analyze with increased detail
6ANALYZE orders;
7ANALYZE customers;1-- Create extended statistics for potentially correlated columns
2CREATE STATISTICS order_customer_stats (dependencies)
3ON customer_id, order_date FROM orders;
4
5-- Update the extended statistics
6ANALYZE orders;Before Statistics Update
Execution time: 32.5 seconds
Sequential scans on all tables
Inaccurate row estimates (off by ~300%)
Hash joins instead of nested loop joins
Excessive memory usage for hash tables
After Statistics Update
Execution time: 0.8 seconds (40x improvement)
Index scans on all tables with appropriate indexes
Accurate row estimates (within 10%)
Efficient nested loop joins for small result sets
Minimal memory usage
To prevent this issue from recurring, we should implement these practices:
Key Lessons from This Example
Statistics freshness is critical
for query performance, especially after bulk data changes
Statistics targets
for critical columns should be increased beyond defaults
Extended statistics
help the planner handle correlated columns
Always update statistics
after significant data modifications
Monitor statistics age
as part of your regular database maintenance
A 40x performance improvement was achieved with
no changes to the query itself
or indexes—just by updating statistics!
This practical example demonstrates how crucial accurate and up-to-date statistics are for database performance. By understanding how statistics affect the query planner, you can diagnose and fix many performance issues without resorting to query rewrites or schema changes.
In the next lesson, we'll explore
Connection Pooling & Management
, a critical component of database performance at scale.
You'll learn how to:
Configure and optimize database connection pools
Understand connection overhead and lifecycle management
Implement effective connection management patterns in applications
Monitor and troubleshoot connection-related performance issues
Scale connection capacity for high-traffic applications
Connection pooling is often the difference between an application that scales to hundreds of users and one that scales to thousands or millions.
1-- Add this to your data load scripts
2BEGIN;
3-- [Data load operations]
4COPY orders FROM '/path/to/data.csv' WITH CSV;
5-- Update statistics immediately after load
6ANALYZE orders;
7COMMIT;1-- Update autovacuum configuration for faster stats updates
2ALTER TABLE orders SET (
3 autovacuum_analyze_scale_factor = 0.05, -- Analyze after 5% changes (default: 0.1)
4 autovacuum_analyze_threshold = 100 -- Plus this many rows (default: 50)
5);1-- Create a monitoring view for stale statistics
2CREATE OR REPLACE VIEW stale_statistics AS
3SELECT
4 schemaname,
5 relname,
6 n_live_tup AS row_count,
7 n_mod_since_analyze AS modified_rows,
8 last_analyze,
9 last_autoanalyze
10FROM pg_stat_all_tables
11WHERE (n_mod_since_analyze > 1000 OR n_mod_since_analyze > n_live_tup * 0.1)
12AND schemaname NOT LIKE 'pg_%'
13AND schemaname != 'information_schema'
14ORDER BY n_mod_since_analyze DESC;
15
16-- Create a scheduled job to alert if statistics are stale
17-- (Implementation depends on your environment)Your progress is saved in this browser, on this device only.