אופטימיזציה של שאילתות PostgreSQL איטיות: אבחון ותיקון ביומיים

אופטימיזציה של שאילתות SQL איטיות ב-PostgreSQL

אתם ממתינים 20 שניות לטעינת דוח. משתמשים עוזבים, מסד הנתונים הוא צוואר הבקבוק. תרחיש טיפוסי: שאילתה אחת לוקחת 5 שניות, ויש מאות כאלה בדקה. אנחנו מאבחנים למה ומתקנים: מפחיתים את זמן התגובה של מסד הנתונים פי 3–5 בעומס. האבחון לוקח יום, האופטימיזציה עוד כמה ימים. כל השינויים מתועדים, מדידות לפני/אחרי הן חובה.

שאילתות איטיות הן הגורם העיקרי לחוויית משתמש ירודה. 95% מבעיות ביצועי מסד הנתונים נפתרות באחת מארבע שיטות: הוספת אינדקס, שכתוב השאילתה, דה-נורמליזציה או קאשינג. הניסיון מראה שאופטימיזציה נכונה של 10–15 השאילתות הכבדות ביותר יכולה לפנות עד 40% ממשאבי השרת. בואו נראה כיצד לאבחן ולתקן שאילתות איטיות ב-PostgreSQL.

כיצד מאבחנים שאילתות איטיות?

pg_stat_statements הוא התוסף הראשון שכדאי להפעיל בסביבת ייצור. הוא אוסף סטטיסטיקות לכל שאילתה: זמן כולל וממוצע, מספר קריאות, סטיית תקן. מקדם השונות (coeff_var) עוזר לזהות שאילתות עם תוכניות לא יציבות.

תהליך אבחון בחמישה שלבים:

1. הפעלת pg_stat_statements (אם מושבת) ואיסוף סטטיסטיקות למספר שעות.
2. הרצת שאילתה עבור 20 הראשונות לפי total_exec_time.
3. לכל שאילתה חשודה, קבלת תוכנית באמצעות EXPLAIN (ANALYZE, BUFFERS).
4. זיהוי צמתי תוכנית: Seq Scan, Nested Loop, Hash Join עם Batches > 1.
5. יישום האופטימיזציה המתאימה: הוספת אינדקס, שכתוב שאילתה, כוונון work_mem.

-- Включение pg_stat_statements
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

-- Топ-20 по суммарному времени
SELECT round(total_exec_time::numeric, 2) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       calls,
       round((stddev_exec_time / mean_exec_time * 100)::numeric, 1) AS coeff_var_pct,
       left(query, 120) AS query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

-- Включение pg_stat_statements shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.max = 10000 pg_stat_statements.track = all -- Топ-20 по суммарному времени SELECT round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, calls, round((stddev_exec_time / mean_exec_time * 100)::numeric, 1) AS coeff_var_pct, left(query, 120) AS query FROM pg_stat_statements WHERE calls > 100 ORDER BY total_exec_time DESC LIMIT 20; — מקדם שונות: אחוז גבוה מצביע על תוכנית לא יציבה (פרמטרים שונים מניבים זמנים שונים באופן דרמטי). לאחר מכן הריצו כל שאילתה חשודה דרך EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.*, c.name AS category_name
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.status = 'published'
  AND p.created_at > NOW() - INTERVAL '30 days'
ORDER BY p.created_at DESC
LIMIT 50;

צמתים מרכזיים שכדאי לשים לב אליהם בתוכנית:

• coeff_var_pct על טבלה גדולה — אין אינדקס או שהפלנר חושב שהאינדקס לא מועיל.
• EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT p.*, c.name AS category_name FROM products p JOIN categories c ON c.id = p.category_id WHERE p.status = 'published' AND p.created_at > NOW() - INTERVAL '30 days' ORDER BY p.created_at DESC LIMIT 50; עם איטרציות רבות — N+1 ברמת SQL.
• Seq Scan עם Nested Loop — Hash Join לא מספיק.
• Batches > 1 ללא work_mem על עמודת ORDER BY — אין אינדקס מתאים.

מדוע אינדקסים לא בשימוש?

גם עם אינדקס, הפלנר עשוי להתעלם ממנו. סיבות עיקריות:

• פונקציה על עמודה ב-WHERE (לדוגמה, Sort)
• סלקטיביות נמוכה (אינדקס על עמודה בוליאנית עם התפלגות לא מאוזנת)
• סדר מיון שאינו תואם לסדר האינדקס

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

אילו אנטי-דפוסים הם הנפוצים ביותר?

-- Плохо: SELECT * тянет лишние колонки; OFFSET увеличивает нагрузку; OR не использует индекс; функция на колонке; NOT IN с NULL
SELECT * FROM products WHERE category_id = 5 LIMIT 50 OFFSET 10000;
SELECT * FROM users WHERE email = $1 OR phone = $1;
SELECT * FROM orders WHERE DATE(created_at) = $1;
SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM order_items);
-- Хорошо: только нужные поля; keyset pagination; UNION ALL; range condition; NOT EXISTS
SELECT id, title, slug FROM products WHERE (created_at, id) > ($1, $2) ORDER BY created_at DESC LIMIT 50;
SELECT * FROM users WHERE email = $1 UNION ALL SELECT * FROM users WHERE phone = $1 LIMIT 1;
SELECT * FROM orders WHERE created_at >= $1 AND created_at < $2;
SELECT p.* FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

השוואת שיטות פגינציה:

אופטימיזציה של JOIN: אינדקסים מרוכבים

-- Добавляем составной индекс для типичного фильтра
CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC);
-- Запрос использует index scan без Sort
SELECT id, total, status, created_at
FROM orders
WHERE user_id = $1 AND status = 'completed'
ORDER BY created_at DESC
LIMIT 10;

סדר עמודות באינדקס: תנאי שוויון קודם (Index Scan, DATE(created_at)), ואז טווח/מיון (-- Плохо: SELECT * тянет лишние колонки; OFFSET увеличивает нагрузку; OR не использует индекс; функция на колонке; NOT IN с NULL SELECT * FROM products WHERE category_id = 5 LIMIT 50 OFFSET 10000; SELECT * FROM users WHERE email = $1 OR phone = $1; SELECT * FROM orders WHERE DATE(created_at) = $1; SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM order_items); -- Хорошо: только нужные поля; keyset pagination; UNION ALL; range condition; NOT EXISTS SELECT id, title, slug FROM products WHERE (created_at, id) > ($1, $2) ORDER BY created_at DESC LIMIT 50; SELECT * FROM users WHERE email = $1 UNION ALL SELECT * FROM users WHERE phone = $1 LIMIT 1; SELECT * FROM orders WHERE created_at >= $1 AND created_at < $2; SELECT p.* FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id); ).

כוונון work_mem ושימוש ב-LATERAL

אם EXPLAIN ANALYZE מראה -- Добавляем составной индекс для типичного фильтра CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC); -- Запрос использует index scan без Sort SELECT id, total, status, created_at FROM orders WHERE user_id = $1 AND status = 'completed' ORDER BY created_at DESC LIMIT 10; במהלך Sort — הגדילו את user_id = $1 עבור הסשן:

SET work_mem = '64MB'; -- Выполняем тяжёлый аналитический запрос 

ב-status = 'completed' עדיף לשמור על created_at DESC נמוך (ברירת מחדל 4–8MB) ולהעלות אותו לשאילתות ספציפיות דרך external merge (Disk: ...).

-- LATERAL: для row-dependent subqueries
SELECT u.id, u.email, recent.total
FROM users u
CROSS JOIN LATERAL (
    SELECT SUM(total) AS total
    FROM orders o
    WHERE o.user_id = u.id
      AND o.created_at > NOW() - INTERVAL '30 days'
) AS recent;

LATERAL מניב לעיתים קרובות תוכנית טובה יותר מאשר JOIN על CTE מצטבר.

היקף העבודה לאופטימיזציה

• ביקורת על 10–15 השאילתות הכבדות ביותר דרך pg_stat_statements ו-EXPLAIN ANALYZE
• שכתוב שאילתות כדי לבטל אנטי-דפוסים
• הוספה וכוונון של אינדקסים (כולל מרוכבים וחלקיים)
• כוונון פרמטרים של PostgreSQL (shared_buffers, work_mem, effective_cache_size)
• מסירת דוח עם מדידות לפני/אחרי והמלצות לצוות
• אופציונלי: הדרכת מפתחים בקריאת תוכניות שאילתות

בפרויקט אחד, קיצרנו את זמן ביצוע השאילתה מ-4 שניות ל-200 ms — זה הפחית את עומס השרת ואפשר לנו להימנע משדרוג תשתית. אופטימיזציה של 15 שאילתות איטיות יכולה לפנות חלק משמעותי ממשאבי השרת.

ציר זמן ומחירים

אבחון ואופטימיזציה של 10–15 שאילתות איטיות — 2–3 ימים. ביקורת עמוקה על סכמה ושאילתות עבור יישום בעומס גבוה — 3–5 ימים. התמחור מחושב באופן אישי לאחר הערכת היקף.

כדי להתחיל פרויקט, צרו קשר דרך טלגרם או אימייל — נעשה ניתוח של שתי השאילתות הראשונות. הזמינו אבחון וקבלו דוח עם מדידות לפני/אחרי. אנו מבטיחים הפחתה מדידה בזמן השאילתה של לפחות 30% — אנו מתקנים תוצאות לפני ואחרי האופטימיזציה.