كيفية تحسين أداء استعلام PostgreSQL في عام 2026: الفهارس والشرح والضبط

تعود معظم مشكلات أداء PostgreSQL إلى مجموعة من الأسباب: فهرس مفقود، أو فهرس لا يستطيع المخطط استخدامه، أو استعلام يسحب صفوفًا أكثر بكثير مما يحتاج إليه، أو إحصائيات لم تعد تعكس البيانات. يغطي هذا الدليل كيفية العثور على الجهاز الموجود لديك وإصلاحه، بالترتيب الذي يجد المشكلات بشكل أسرع.

الخطوة 1: ابحث عن الاستعلامات البطيئة

لا تخمن. تمكينpg_stat_statements، الذي يسجل إحصائيات التنفيذ لكل شكل استعلام.

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
-- then restart, and in your database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Highest total time — usually where the real wins are
SELECT
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

فرز حسبالمجموع الوقت وليس يعني. الاستعلام الذي يستغرق 20 مللي ثانية ولكن تشغيله 100000 مرة في الساعة يكلف أكثر بكثير من استعلام يستغرق ثانيتين مرتين يوميًا، وعادةً ما يكون إصلاحه أسهل.

قم أيضًا بتسجيل الاستعلامات البطيئة حتى تتمكن من معرفة ما يحدث في الإنتاج وليس في الاختبار.

-- postgresql.conf
log_min_duration_statement = 500   -- log anything over 500ms
log_lock_waits = on
log_temp_files = 0                 -- log every temp file (indicates spilling)

الخطوة 2: اقرأ الشرح والتحليل بشكل صحيح

استخدم دائمًاEXPLAIN (ANALYZE, BUFFERS). عاديEXPLAIN يظهر تقدير المخطط. ANALYZE في الواقع يقوم بتشغيل الاستعلام والإبلاغ عن الواقع، وغالبًا ما تكون الفجوة بين الاثنين هي الخطأ.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= now() - interval '7 days'
  AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;

اقرأ الناتج من العقدة الأعمق إلى الخارج، وابحث عن أربعة أشياء.

الصفوف المقدرة مقابل الصفوف الفعلية. rows=10 ... actual rows=48000 يعني أن المخطط كان مخطئًا للغاية، وكل قرار اتخذه في اتجاه مجرى النهر هو موضع شك. عادة ما تكون الإحصاءات قديمة.

عمليات المسح المتسلسلة على طاولات كبيرة. جيد على الطاولات الصغيرة وعلامة حمراء على الطاولات الكبيرة ذات مرشح انتقائي.

تمت إزالة الصفوف بواسطة عامل التصفية. الرقم الكبير يعني أن قاعدة البيانات تقرأ صفوفًا أكثر بكثير مما أرجعته – وهو توقيع الفهرس المفقود الكلاسيكي.

الدمج الخارجي أو الفرز المعتمد على القرص. امتد الفرز إلى القرص بسببwork_mem كان صغيرا جدا لذلك.

-- Refresh statistics when estimates are wrong
ANALYZE orders;

-- Collect more detail on a column with skewed distribution
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

الخطوة 3: فهرسة الطريق الصحيح

ترتيب الأعمدة في الفهرس المركب له أهمية كبيرة. القاعدة: أعمدة المساواة أولاً، ثم أعمدة النطاق، ثم الأعمدة التي تقوم بالفرز حسبها.

-- For: WHERE status = 'pending' AND created_at >= ... ORDER BY created_at DESC
CREATE INDEX CONCURRENTLY idx_orders_status_created
  ON orders (status, created_at DESC);

معstatus أولاً، يضيق الفهرس إلى الصفوف المطابقة ثم يقرأها مرتبة بالفعل حسبcreated_at، لذلك يختفي النوع تمامًا. اعكس الترتيب وسيصبح الفهرس أقل فائدة بكثير.

فهارس جزئية تكون أصغر بشكل كبير عندما تقوم بالاستعلام عن مجموعة فرعية فقط.

-- If 98% of orders are completed and you only query pending ones
CREATE INDEX CONCURRENTLY idx_orders_pending
  ON orders (created_at DESC)
  WHERE status = 'pending';

تغطية الفهارس اسمح للإجابة على الاستعلام من الفهرس وحده، مع تجنب الجدول بالكامل.

CREATE INDEX CONCURRENTLY idx_orders_lookup
  ON orders (user_id, created_at DESC)
  INCLUDE (total, status);
-- EXPLAIN then shows "Index Only Scan"

قم دائمًا ببناء فهارس على الأنظمة الحية باستخدامCONCURRENTLY، والذي لا يمنع الكتابة.

الخطوة 4: لماذا يتم تجاهل الفهرس

الإحباط الشائع هو أن الفهرس موجود ولن يستخدمه المخطط. الأسباب المعتادة:

يتم تطبيق دالة على العمود. وهذا يجعل الفهرس غير قابل للاستخدام.

-- ❌ Index on email cannot be used
WHERE lower(email) = 'user@example.com';

-- ✅ Index the expression instead
CREATE INDEX CONCURRENTLY idx_users_email_lower ON users (lower(email));

عدم تطابق النوع. مقارنةbigint العمود إلى سلسلة يفرض قالبًا يهزم الفهرس. قم بمطابقة الأنواع في معلمات الاستعلام الخاصة بك.

حرف البدل الرائد في LIKE. LIKE '%term' لا يمكن استخدام فهرس B-tree. استخدم فهرس trigram لذلك.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
  ON products USING gin (name gin_trgm_ops);
-- now ILIKE '%widget%' can use an index

الاستعلام ليس انتقائيًا. إذا كان المرشح يطابق جزءًا كبيرًا من الجدول، فإن المسح التسلسلي يكون أسرع حقًا ويكون المخطط صحيحًا. ليس كل “فهرس غير مستخدم” يعد خطأً.

الخطوة 5: إصلاح استعلامات N+1

السبب الأكثر شيوعًا على مستوى التطبيق هو تحميل قاعدة البيانات، وعادةً ما يكون غير مرئي في سجلات الاستعلام البطيء لأن كل استعلام فردي سريع.

-- 1 query for orders, then one per order for the user: 101 round trips
SELECT * FROM orders LIMIT 100;
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2;   -- ... and so on

-- ✅ One query
SELECT o.*, u.email, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
LIMIT 100;

في ORM، هذا يعني التحميل المتلهف —includes في السجل النشط،selectinload في SQLAlchemy،include في بريزما. اكتشاف النمط عن طريق حساب الاستعلامات لكل طلب في التطوير؛ القفزة المفاجئة بحجم القائمة هي التوقيع.

الخطوة 6: ترقيم الصفحات الذي يتغير حجمه

OFFSET يصبح الأمر أبطأ كلما تعمقت صفحتك، لأنه يجب على قاعدة البيانات إنشاء وتجاهل كل صف تم تخطيه.

-- ❌ Reads and throws away 100,000 rows
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- ✅ Keyset pagination — constant time at any depth
SELECT * FROM posts
WHERE (created_at, id) < ($1, $2)   -- values from the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 20;

بما في ذلكid نظرًا لأن الفاصل الزمني يجعل إجمالي الطلب، مما يمنع تخطي الصفوف أو تكرارها عند تصادم الطوابع الزمنية.

الخطوة 7: التكوين يستحق التغيير

الإعدادات الافتراضية متحفظة وتفترض أجهزة متواضعة للغاية.

random_page_cost هو الأكثر تأثيرا والأقل شهرة. يفترض الإعداد الافتراضي 4.0 أن القراءات العشوائية أغلى بأربع مرات من القراءات المتسلسلة، وهو ما ينطبق على محركات الأقراص الميكانيكية. على محركات الأقراص ذات الحالة الثابتة (SSD)، فإنه يجعل المخطط يتجنب الفهارس التي يجب أن يستخدمها.

-- Test the effect on a single session before changing it globally
SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

الخطوة 8: الانتفاخ والفراغ

يحتفظ PostgreSQL بإصدارات الصفوف القديمة حتى يستعيدها الفراغ. يؤدي التحديث أو الحذف المكثف إلى حدوث تضخم، مما يجعل عمليات المسح تقرأ صفحات أكثر من اللازم.

-- Which tables are bloated and when were they last vacuumed?
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Rebuild a bloated table without a long exclusive lock
VACUUM (ANALYZE, VERBOSE) orders;

-- For severe bloat, rebuild indexes concurrently
REINDEX INDEX CONCURRENTLY idx_orders_status_created;

بالنسبة للجداول ذات حركة الكتابة الكثيفة، اجعل الفراغ التلقائي أكثر قوة على هذا الجدول على وجه التحديد وليس على المستوى العالمي.

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

البحث عن الفهارس غير المستخدمة والمكررة

يؤدي كل فهرس إلى إبطاء عملية الكتابة ويستهلك المساحة. تدقيقها بشكل دوري.

-- Indexes that have never been scanned
SELECT
  schemaname, relname AS table_name, indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size,
  idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

تحقق من وقت التشغيل قبل التصرف — قد يكون الفهرس غير المستخدم منذ إعادة التشغيل الأسبوع الماضي ضروريًا لتقرير شهري.

الخلاصة

تحسين PostgreSQL هو تسلسل وليس تخمينًا:ابحث عن الاستعلامات الباهظة الثمن باستخدامpg_stat_statements مرتبة حسب الوقت الإجمالي، اقرأEXPLAIN (ANALYZE, BUFFERS) بالنسبة للفجوات والصفوف التقديرية مقابل الفعلية التي تمت إزالتها بواسطة الفلتر، قم بإضافة فهارس مركبة مع أعمدة المساواة أولاً وأعمدة النطاق بعد ذلك، وقم بإزالة أنماط N+1 مع التحميل المتحمس، واستبدل OFFSET العميق بترقيم الصفحات لمجموعة المفاتيح، وقم بتعيينrandom_page_cost بشكل مناسب لمحركات أقراص SSD. ثم حافظ على تحديث الإحصائيات وضبطها تلقائيًا، لأن قاعدة البيانات المفهرسة جيدًا والتي تحتوي على إحصائيات قديمة لا تزال تنتج خططًا سيئة.