Statistics & Query Planning: Functions

**PostgreSQL:**

-- Update statistics for one table

ANALYZE orders;

-- Update statistics for specific columns

ANALYZE orders (status, created_at);

-- Update statistics for all tables

ANALYZE;

-- Vacuum and analyze (reclaim space + update stats)

VACUUM ANALYZE orders;

-- View statistics

SELECT * FROM pg_stats WHERE tablename = 'orders';

-- View table-level statistics

SELECT * FROM pg_class WHERE relname = 'orders';

-- Create extended statistics for correlated columns

CREATE STATISTICS city_state_stats (dependencies)

ON city, state FROM users;

-- Create extended statistics with n-distinct

CREATE STATISTICS order_stats (ndistinct)

ON customer_id, product_id FROM orders;

-- Drop extended statistics

DROP STATISTICS city_state_stats;

**MySQL:**

-- Update statistics for one table

ANALYZE TABLE orders;

-- Update statistics for multiple tables

ANALYZE TABLE orders, customers, products;

-- View statistics

SELECT * FROM mysql.innodb_table_stats WHERE table_name = 'orders';

SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'orders';

-- Configure persistent statistics

ALTER TABLE orders STATS_PERSISTENT=1;

-- Set sample size for statistics

ALTER TABLE orders STATS_SAMPLE_PAGES=100;

-- Force statistics recalculation

ANALYZE TABLE orders UPDATE HISTOGRAM ON status, priority;

-- Drop histogram

ALTER TABLE orders DROP HISTOGRAM ON status;

**SQL Server:**

-- Update statistics for one table

UPDATE STATISTICS orders;