אופטימיזציית שאילתות PostgreSQL: EXPLAIN ANALYZE, אינדקסים וה-Planner

אופטימיזציית שאילתות PostgreSQL: EXPLAIN ANALYZE, אינדקסים ו-Planner

שאילתות איטיות ב-PostgreSQL הן כאב ראש לכל מהנדס DevOps. שאילתה שלקחה חצי שנייה עם 100 אלף שורות מתחילה להיתקע ל-8 שניות ב-2 מיליון — וכולם מסתכלים על המפתח. השאילתה עצמה נכונה, הסכמה תקינה, החומרה לא השתנתה. הבעיה היא ה-planner של PostgreSQL, שבחר תוכנית אחת לטבלה קטנה ועבר לאחרת ככל שהנתונים גדלו. או ש-ANALYZE לא רץ כבר חודש, והסטטיסטיקות מיושנות. או שיש אינדקס, אבל ה-planner לא משתמש בו בגלל statistics target שגוי. נתקלנו בבעיות האלה עשרות פעמים ופיתחנו גישה שיטתית לאופטימיזציה. המהנדסים שלנו עם ניסיון של 10+ שנים מבטיחים הפחתה של עד 50% בזמן התגובה של מסד הנתונים וירידה בעומס השרת. נבחן את הפרויקט שלך: צור קשר לייעוץ.

איך לקרוא EXPLAIN ANALYZE נכון?

EXPLAIN מציג את התוכנית ללא ביצוע השאילתה. EXPLAIN ANALYZE מבצע בפועל ומראה שורות בפועל לעומת מוערכות. ההבדל ביניהם הוא המפתח לאבחון.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.email, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > CURRENT_DATE - INTERVAL '1 year'
GROUP BY u.email;

הדגל EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT u.email, COUNT(o.id) FROM users u JOIN orders o ON o.user_id = u.id WHERE u.created_at > CURRENT_DATE - INTERVAL '1 year' GROUP BY u.email; מוסיף מידע על פגיעות ב-shared_buffers וגישה לדיסק — קריטי להבנת כמה השאילתה מעמיסה על קלט/פלט. קרא את התוכנית מלמטה למעלה: צמתים נמוכים יותר מתבצעים ראשונים.

מה לחפש קודם:

אי-התאמה בהערכת שורות. אם ה-planner ציפה ל-10 שורות (BUFFERS) אבל קיבל 50000 (rows=10), הסטטיסטיקות מיושנות או ש-actual rows=50000 נמוך מדי. זה מאלץ את ה-planner לבחור Nested Loop במקום Hash Join.

-- посмотреть статистику и увеличить детализацию
SELECT n_distinct, correlation, most_common_vals
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

סריקה סדרתית (Seq Scan) על טבלה גדולה. לא תמיד רע — אם שאילתה מחזירה >5-10% משורות הטבלה, Seq Scan זול יותר מ-Index Scan. אבל אם השורות המוחזרות מעטות ועדיין נבחר Seq Scan, כנראה שהאינדקס לא קיים או לא מכסה את התנאי.

Hash Join עם statistics_target גדול. אם -- посмотреть статистику и увеличить детализацию SELECT n_distinct, correlation, most_common_vals FROM pg_stats WHERE tablename = 'orders' AND attname = 'status'; ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500; ANALYZE orders; ב-Hash Join, טבלת ה-hash לא נכנסה ל-Batches ונשפכה לדיסק. פתרון: Batches > 1 לשאילתות אנליטיות כבדות (לא גלובלית — זה יצרוך את כל הזיכרון עם סשנים מקבילים).

למה ה-Planner בוחר תוכנית שגויה?

PostgreSQL משתמש באופטימיזר מבוסס עלויות. עלויות הפעולות נקבעות על ידי פרמטרים ב-work_mem. המקרה הנפוץ ביותר של תוכנית שגויה הוא פרמטרי עלות מיושנים עבור SSD:

# Для SSD снижаем random_page_cost (default=4.0)
random_page_cost = 1.1
effective_cache_size = 12GB  # ~75% от RAM сервера

עם SET work_mem = '64MB', ה-planner מחשיב גישה אקראית לדיסק כיקרה ומעדיף Seq Scan גם במקום ש-Index Scan הגיוני יותר. על SSD, זה מוביל להחלטות שגויות. תיעוד PostgreSQL: יש להוריד את postgresql.conf עבור SSD.

אם אחרי איסוף סטטיסטיקות ותיקון פרמטרים השאילתה עדיין בוחרת תוכנית גרועה, אפשר לכפות אותה דרך # Для SSD снижаем random_page_cost (default=4.0) random_page_cost = 1.1 effective_cache_size = 12GB # ~75% от RAM сервера (הרחבה) או פרמטרי סשן:

-- принудительно отключить Nested Loop для текущей сессии
SET enable_nestloop = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_nestloop = on;

זה כלי אבחוני, לא פתרון קבוע. השבתה קבועה של אופרטור planner פוגעת בשאילתות אחרות.

אינדקסים: איזה, מתי ולמה

אינדקס B-tree הסטנדרטי עובד לשוויון וטווחים על טיפוסים רגילים. למשימות ספציפיות, נדרשים טיפוסים אחרים:

-- частичный индекс: индексируем только активные заказы
CREATE INDEX idx_orders_active ON orders(created_at) WHERE status = 'active';

-- составной индекс: порядок колонок важен
-- работает для WHERE user_id = ? AND status = ?
-- и для WHERE user_id = ? (первая колонка)
-- НЕ работает для WHERE status = ? (только вторая колонка)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- covering index: включаем нужные колонки, избегая heap fetch
CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total_amount, created_at);

-- GIN для JSONB
CREATE INDEX idx_events_payload ON events USING GIN(payload jsonb_path_ops);

אינדקסים מכסים (עם random_page_cost = 4.0) שימושיים במיוחד לשאילתות שמבוצעות בתדירות גבוהה וקוראות 2-3 שדות ספציפיים. ה-planner מבצע Index Only Scan — נתונים נלקחים ישירות מהאינדקס, ללא גישה ל-heap.

אינדקסים נפוחים הם בעיה נפרדת. אחרי UPDATE/DELETE המוני, אינדקסים צוברים עמודים מתים. random_page_cost מציג נפיחות:

SELECT * FROM pgstatindex('idx_orders_user_status'); -- leaf_fragmentation > 30% → пора REINDEX CONCURRENTLY 

pg_hint_plan לא חוסם קריאות וכתיבות — בניגוד ל--- принудительно отключить Nested Loop для текущей сессии SET enable_nestloop = off; EXPLAIN ANALYZE SELECT ...; SET enable_nestloop = on; הרגיל.

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending'; 

SELECT * FROM products WHERE category_id = 5 AND is_active = true ORDER BY created_at DESC LIMIT 20 OFFSET 10000;