آمار، ANALYZE، pg_stat_statements و ساخت ایندکس بدون توقف

برنامه‌ریز به اندازه‌ی آمارش باهوش است

برنامه‌ریز برای هر ستون یک خلاصه نگه می‌دارد: درصد NULL، تعداد مقدارهای متمایز، پرتکرارترین مقدارها (MCV) و هیستوگرام توزیع. این آمار را ANALYZE با نمونه‌گیری (۳۰۰ برابر default_statistics_target ردیف، یعنی ۳۰ هزار ردیف) می‌سازد و autovacuum وقتی حدود ده درصد جدول تغییر کرد، خودکار اجرایش می‌کند.

SELECT attname, null_frac, n_distinct, most_common_vals, correlation
FROM pg_stats
WHERE schemaname = 'factory' AND tablename = 'orders';

SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables ORDER BY n_mod_since_analyze DESC LIMIT 10;

وقتی آمار کافی نیست

برنامه‌ریز فرض می‌کند ستون‌ها مستقل‌اند. فرض کنید جدول مشتری ستون province هم دارد؛ شهر «کاشان» و استان «اصفهان» کاملاً وابسته‌اند؛ برنامه‌ریز احتمال دو شرط را ضرب می‌کند و تعداد ردیف را خیلی کم تخمین می‌زند. آمار چندستونی این را درست می‌کند:

CREATE STATISTICS customers_city_prov (dependencies, ndistinct, mcv)
  ON city, province FROM factory.customers;
ANALYZE factory.customers;

-- ستون با توزیع نامتقارن: نمونه‌ی بزرگ‌تر
ALTER TABLE factory.order_items ALTER COLUMN design_code SET STATISTICS 1000;
ANALYZE factory.order_items;

pg_stat_statements: کدام کوئری واقعاً گران است؟

کندترین کوئری لزوماً مشکل اصلی نیست؛ کوئری ۵ میلی‌ثانیه‌ای که روزی دو میلیون بار اجرا می‌شود، از گزارش ماهانه‌ی ۲۰ ثانیه‌ای سنگین‌تر است. pg_stat_statements همه‌ی کوئری‌ها را با پارامترهای نرمال‌شده جمع می‌زند:

# postgresql.conf (نیاز به restart)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top
compute_query_id = on

CREATE EXTENSION pg_stat_statements;

SELECT round(total_exec_time) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

SELECT pg_stat_statements_reset();  -- شروع یک دوره‌ی اندازه‌گیری تازه

CREATE INDEX CONCURRENTLY

CREATE INDEX معمولی تا پایان ساخت، نوشتن در جدول را قفل می‌کند؛ روی جدول ده میلیونی یعنی چند دقیقه توقف ثبت سفارش. نسخه‌ی CONCURRENTLY جدول را دو بار پیمایش می‌کند و منتظر تمام تراکنش‌های قدیمی می‌ماند، اما نوشتن را متوقف نمی‌کند:

CREATE INDEX CONCURRENTLY orders_due_idx ON factory.orders (due_date);

-- اگر وسط کار خطا داد، ایندکس INVALID باقی می‌ماند
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY orders_due_idx;

-- بازسازی ایندکس پف‌کرده بدون توقف (نسخه‌ی 12 به بعد)
REINDEX INDEX CONCURRENTLY factory.orders_status_created_at_idx;

نکته‌هایی که کمتر کسی می‌داند

• CREATE INDEX CONCURRENTLY داخل بلوک تراکنش اجرا نمی‌شود؛ در جنگو از عملیات AddIndexConcurrently (در django.contrib.postgres.operations) استفاده کنید و در کلاس Migration مقدار atomic = False بگذارید.
• یک تراکنش طولانی یا idle in transaction، CONCURRENTLY را تا ابد منتظر نگه می‌دارد؛ پیشرفت را در pg_stat_progress_create_index ببینید.
• autovacuum جدول‌های موقت (TEMP) و جدول والد partition‌شده را ANALYZE نمی‌کند؛ بعد از پر کردن جدول موقت بزرگ، خودتان ANALYZE بزنید.
• بعد از بارگذاری انبوه داده (مثلاً ورود اطلاعات یک سال از اکسل)، ANALYZE دستی بزنید؛ تا autovacuum برسد، کوئری‌های گزارش با پلن غلط اجرا می‌شوند.
• maintenance_work_mem بزرگ‌تر (مثلاً 1GB فقط برای همان نشست با SET) ساخت ایندکس روی جدول بزرگ را چند برابر سریع‌تر می‌کند.