// chapter_05.md

بهینه‌سازی Query در PostgreSQL

بهینه‌سازی Query یعنی پیدا کردن دلیل کندی query و رفع آن. در این فصل با EXPLAIN ANALYZE، ابزار pg_stat_statements، query planner، parameter‌های مهم مثل work_mem و الگوهای آنتی‌پترن آشنا می‌شویم.

بهینه‌سازی Query یعنی پیدا کردن دلیل کندی query و رفع آن. در این فصل با EXPLAIN ANALYZE، ابزار pg_stat_statements، query planner، parameter‌های مهم مثل work_mem و الگوهای آنتی‌پترن آشنا می‌شویم.

EXPLAIN – نقشه اجرای Query

-- فقط plan (بدون اجرا)
EXPLAIN SELECT * FROM orders WHERE user_id = 100;

-- plan + اجرای واقعی
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;

-- جزئیات کامل
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) 
SELECT * FROM orders WHERE user_id = 100;

خواندن خروجی EXPLAIN

Index Scan using idx_orders_user on orders 
  (cost=0.42..8.44 rows=1 width=64) 
  (actual time=0.029..0.030 rows=1 loops=1)
  Index Cond: (user_id = 100)
  Buffers: shared hit=4
Planning Time: 0.187 ms
Execution Time: 0.058 ms

cost=0.42..8.44: تخمین (شروع..کل)
rows=1: تعداد ردیف تخمینی
actual time=0.029..0.030: زمان واقعی
loops=1: تعداد دفعات اجرا
Buffers: shared hit=4: بلاک‌های خوانده‌شده از کش

نشانه‌های هشدار: اختلاف زیاد بین rows تخمینی و واقعی، Seq Scan روی جدول بزرگ، Hash Join با تعداد ردیف بالا، Sort با حجم زیاد در دیسک.

انواع Scan

Sequential Scan

Seq Scan on orders
  Filter: (status = 'pending')
  Rows Removed by Filter: 999000

خواندن کل جدول. خوب است وقتی:

جدول کوچک (< 1000 ردیف)
درصد بالایی از ردیف‌ها برمی‌گردد
بدون ایندکس مناسب

Index Scan

Index Scan using idx_orders_user on orders
  Index Cond: (user_id = 100)

سریع – مستقیم از ایندکس.

Bitmap Index Scan + Bitmap Heap Scan

Bitmap Heap Scan on orders
  -> Bitmap Index Scan on idx_orders_status
       Index Cond: (status = 'pending')

وقتی ایندکس چند هزار ردیف برمی‌گرداند – PostgreSQL bitmap می‌سازد و سپس heap را scan می‌کند.

Index-Only Scan

Index Only Scan using idx on orders
  Heap Fetches: 0

بهترین حالت – بدون نیاز به جدول. Heap Fetches: 0 یعنی کاملاً از ایندکس.

انواع Join

Nested Loop

Nested Loop
  -> Index Scan on orders
  -> Index Scan on users (user_id = orders.user_id)

خوب برای: یکی کوچک، یکی با ایندکس روی join key.

Hash Join

Hash Join
  Hash Cond: (orders.user_id = users.id)
  -> Seq Scan on orders
  -> Hash
        -> Seq Scan on users

خوب برای: جدول‌های بزرگ که هر دو پر هستند.

Merge Join

Merge Join
  Merge Cond: (orders.user_id = users.id)
  -> Sort
  -> Sort

خوب برای: داده‌های قبلاً مرتب.

pg_stat_statements – شناسایی Query کند

-- نصب
CREATE EXTENSION pg_stat_statements;

-- در postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
-- (نیاز به restart)

-- top 10 query کندترین
SELECT 
    substring(query, 1, 80) AS query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- query‌های تکراری
SELECT query, calls FROM pg_stat_statements ORDER BY calls DESC LIMIT 10;

-- ریست آمار
SELECT pg_stat_statements_reset();

تنظیمات Query Planner

-- مهم‌ترین‌ها:
SHOW work_mem;                    -- حافظه برای sort/hash هر operation
SHOW shared_buffers;              -- کش data
SHOW effective_cache_size;        -- تخمین کش OS - برای تصمیم planner
SHOW random_page_cost;            -- 4.0 (HDD)، 1.1 (SSD)
SHOW seq_page_cost;               -- 1.0
SHOW cpu_tuple_cost;
SHOW default_statistics_target;   -- 100 (پیش‌فرض)، تا 1000

-- موقت تغییر دهید برای تست
SET work_mem = '256MB';
SET random_page_cost = 1.1;

EXPLAIN ANALYZE ...;

-- برای session فعلی reset کنید
RESET work_mem;

work_mem

Sort Method: external merge  Disk: 1500kB

این یعنی sort روی دیسک انجام شده (کند). work_mem را بالا ببرید:

Sort Method: quicksort  Memory: 50MB

هشدار: work_mem برای هر operation در هر اتصال است. اگر 100 اتصال داشته باشید و query‌ها 5 sort/hash داشته باشند، مصرف حداکثر = 100 × 5 × work_mem.

آمار جدول‌ها (ANALYZE)

Planner برای تصمیم‌گیری به آمار توزیع داده نیاز دارد:

-- بروزرسانی آمار
ANALYZE orders;
ANALYZE;                          -- همه جدول‌ها

-- آمار جدول
SELECT * FROM pg_stats WHERE tablename = 'orders' AND attname = 'user_id';

-- اگر داده پراکندگی غیرعادی دارد، آمار دقیق‌تری بگیرید:
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000;
ANALYZE orders;

آنتی‌پترن‌های رایج

۱. SELECT *

-- ❌ بد
SELECT * FROM products;

-- ✅ خوب
SELECT id, name, price FROM products;
-- 1) network کمتر  
-- 2) شاید Index-Only Scan
-- 3) refactoring امن‌تر

۲. N+1 Query

# ❌ بد - N+1
for order in orders:
    user = User.objects.get(id=order.user_id)  # N query!

# ✅ خوب - select_related (Django ORM)
orders = Order.objects.select_related('user').all()
# یا در SQL خام: JOIN

۳. OFFSET بزرگ

-- ❌ بد - برای صفحه 1000، 50000 ردیف skip می‌کند
SELECT * FROM products ORDER BY id LIMIT 50 OFFSET 50000;

-- ✅ خوب - Keyset pagination
SELECT * FROM products 
WHERE id > 50000        -- آخرین id صفحه قبل
ORDER BY id 
LIMIT 50;

۴. NOT IN با NULL

-- ❌ خطرناک - اگر یک NULL در subquery باشد، همه چیز return نمی‌شود
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned);

-- ✅ خوب
SELECT * FROM users u 
WHERE NOT EXISTS (SELECT 1 FROM banned b WHERE b.user_id = u.id);

۵. Function در WHERE

-- ❌ ایندکس کار نمی‌کند
SELECT * FROM users WHERE LOWER(email) = 'ali@example.com';

-- ✅ راه ۱: ذخیره lowercase
INSERT INTO users (email) VALUES (LOWER('Ali@Example.com'));

-- ✅ راه ۲: Expression index
CREATE INDEX idx_email_lower ON users(LOWER(email));

۶. LIKE با wildcard اول

-- ❌ Sequential scan
SELECT * FROM products WHERE name LIKE '%کاشان%';

-- ✅ pg_trgm
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
-- حالا LIKE '%X%' ایندکس می‌شود

۷. JOIN روی expression

-- ❌ ایندکس کار نمی‌کند
SELECT * FROM a JOIN b ON CAST(a.id AS TEXT) = b.ref;

-- ✅ نوع داده را تطبیق دهید
ALTER TABLE b ALTER COLUMN ref TYPE INTEGER USING ref::integer;
SELECT * FROM a JOIN b ON a.id = b.ref;

CTE (WITH) – دو نکته

-- در PostgreSQL 11- CTE یک optimization fence بود (always materialized)
-- در PostgreSQL 12+ خودکار inline می‌شود (مگر MATERIALIZED صریح)

-- اگر می‌خواهید inline شود (سریع‌تر):
WITH active_orders AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE status = 'active'
)
SELECT * FROM active_orders WHERE user_id = 1;

-- اگر نیاز به cache دارید (وقتی چند بار استفاده می‌شود):
WITH expensive AS MATERIALIZED (
    SELECT user_id, COUNT(*) FROM orders GROUP BY user_id
)
SELECT * FROM expensive WHERE count > 100;

Query‌های تشخیصی مفید

-- query‌های در حال اجرا
SELECT pid, age(clock_timestamp(), query_start) AS duration, state, query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

-- kill یک query
SELECT pg_cancel_backend(pid);     -- soft
SELECT pg_terminate_backend(pid);  -- hard

-- Lock‌ها
SELECT 
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- جدول‌های پر مصرف
SELECT 
    schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
    n_live_tup, n_dead_tup,
    last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 10;

-- bloat
SELECT 
    schemaname, tablename,
    n_dead_tup, n_live_tup,
    round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY dead_percent DESC NULLS LAST
LIMIT 10;

چک‌لیست بهینه‌سازی

EXPLAIN ANALYZE روی query کند
آیا Seq Scan روی جدول بزرگ؟ → ایندکس بسازید
آیا rows تخمینی با واقعی فاصله زیاد دارد؟ → ANALYZE
آیا Sort روی دیسک؟ → work_mem را بالا ببرید
آیا Hash Join با میلیون‌ها ردیف؟ → ایندکس روی join key
آیا N+1 query در ORM؟ → JOIN یا prefetch
آیا OFFSET بزرگ؟ → keyset pagination
آیا تابع روی WHERE column؟ → expression index
آیا Stats قدیمی؟ → autovacuum config
آیا bloat بالا؟ → VACUUM FULL یا pg_repack

Parallel Query

-- PostgreSQL می‌تواند query را موازی اجرا کند
EXPLAIN SELECT count(*) FROM orders WHERE total > 1000;
-- Gather (workers planned: 4)
--   -> Parallel Seq Scan on orders

-- پارامترها
SHOW max_parallel_workers_per_gather;     -- 2 پیش‌فرض
SHOW max_parallel_workers;                -- 8 پیش‌فرض
SHOW min_parallel_table_scan_size;        -- 8MB

-- موقت غیرفعال
SET max_parallel_workers_per_gather = 0;

بهترین شیوه‌ها

همیشه با EXPLAIN ANALYZE شروع کنید
بعد از تغییرات بزرگ ANALYZE بزنید
pg_stat_statements در همه پروداکشن‌ها فعال باشد
log_min_duration_statement = 1000 برای log کردن query کند
قبل از اضافه کردن ایندکس، EXPLAIN را ببینید
بعد از حذف، idx_scan را چک کنید (شاید لازم بود!)
SELECT * را اجتناب کنید

جمع‌بندی

EXPLAIN ANALYZE نقشه اجرای query را نشان می‌دهد
Index Scan > Bitmap Scan > Seq Scan
pg_stat_statements برای پیدا کردن query کند
work_mem تأثیر بزرگی بر sort/hash دارد
ANALYZE برای آمار دقیق
آنتی‌پترن‌های رایج: SELECT *، N+1، OFFSET بزرگ، function در WHERE

EXPLAIN – نقشه اجرای Query

خواندن خروجی EXPLAIN

انواع Scan

Sequential Scan

Index Scan

Bitmap Index Scan + Bitmap Heap Scan

Index-Only Scan

انواع Join

Nested Loop

Hash Join

Merge Join

pg_stat_statements – شناسایی Query کند

تنظیمات Query Planner

work_mem

آمار جدول‌ها (ANALYZE)

آنتی‌پترن‌های رایج

۱. SELECT *

۲. N+1 Query

۳. OFFSET بزرگ

۴. NOT IN با NULL

۵. Function در WHERE

۶. LIKE با wildcard اول

۷. JOIN روی expression

CTE (WITH) – دو نکته

Query‌های تشخیصی مفید

چک‌لیست بهینه‌سازی

Parallel Query

بهترین شیوه‌ها

جمع‌بندی

نمایش سایت