کوئری کند PostgreSQL را با EXPLAIN ANALYZE بررسی کنیم

قبل از اضافه کردن ایندکس، مسئله را ببینید

وقتی صفحه فهرست سفارش‌ها دیر باز می‌شود، اضافه کردن یک ایندکس از روی حدس ممکن است هیچ کمکی نکند. مشکل شاید تعداد زیاد ردیف‌های خوانده‌شده، sort، join یا انتظار برای منابع باشد. EXPLAIN مسیر انتخابی برنامه‌ریز PostgreSQL را نشان می‌دهد و EXPLAIN ANALYZE با اجرای واقعی کوئری، اطلاعات عملکرد را اضافه می‌کند. این تفاوت مهم است: ANALYZE فقط نمایش نیست. نمونه این مقاله عمداً از SELECT و جدول آزمایشی استفاده می‌کند تا بررسی برنامه اجرا به تغییر ناخواسته اطلاعات عملیاتی منجر نشود.

هزینه cost در پلن، میلی‌ثانیه نیست؛ عدد تخمینی برای مقایسه گزینه‌هاست. actual time، rows و loops به اجرای واقعی مربوط‌اند. وقتی یک گره بارها اجرا می‌شود، خواندن زمان بدون توجه به loops گمراه‌کننده است. BUFFERS نیز درباره استفاده از buffer و خواندن داده کمک می‌کند؛ shared hit به معنی خواندن مستقیم از دیسک نیست. در تحلیل، پلن را از گره‌هایی که داده تولید می‌کنند دنبال کنید و اختلاف بزرگ میان estimated rows و actual rows را نشانه نیاز به بررسی آمار یا شرط کوئری بدانید.

پلن را در زمینه داده واقعی بخوانید

نمونه، سفارش‌ها را براساس customer_id فیلتر و جدیدترین‌ها را براساس created_at مرتب می‌کند. ایندکس مرکب با ترتیب مناسب می‌تواند این الگو را پشتیبانی کند. اما پیشنهاد این ایندکس برای تمام سامانه‌ها نیست: توزیع مشتریان، تعداد سفارش‌ها و کوئری‌های دیگر روی نتیجه اثر دارند. جدول کوچک یا شرط کم‌انتخاب ممکن است همچنان با sequential scan بهتر اجرا شود. اگر می‌خواهید تصمیم برنامه‌ریز را بفهمید، حجم و پراکندگی داده آزمایش را به محیط واقعی نزدیک کنید؛ اجبار به استفاده از ایندکس معیار موفقیت نیست.

بعد از بارگذاری داده یا تغییر جدی آن، آمار را به‌روز کنید. پارامترهای واقعی برنامه را همراه پلن ثبت کنید؛ درخواست یک مشتری پرتراکنش با مشتری کم‌تراکنش یکسان نیست. برنامه‌های دارای prepared statement ممکن است از پلن عمومی یا اختصاصی استفاده کنند و این مسئله باید در عیب‌یابی بررسی شود. انتخاب ستون‌های لازم به‌جای SELECT * و محدود کردن تعداد نتیجه نیز مهم است. اصلاح منطقی کوئری گاهی پیش از هر تغییر زیرساخت، حجم انتقال داده و کار پایگاه داده را کاهش می‌دهد.

پیشنهاد تحریریه این است که هر بار فقط یک تغییر اعمال کنید و کوئری، داده و شرایط بار را ثابت نگه دارید. اجرای اول با cache سرد را با اجرای بعدی مقایسه نکنید و آن را به حساب ایندکس نگذارید. زمان پاسخ برنامه، تعداد خواندن buffer، تعداد ردیف‌های پردازش‌شده و هزینه نوشتن را کنار هم ببینید. ایندکس تازه، فضا و زمان به‌روزرسانی مصرف می‌کند؛ اگر جدول مرتب تغییر می‌کند، سود خواندن باید این هزینه را توجیه کند. برنامه rollback تغییر ایندکس نیز بخشی از تصمیم است.

CREATE TEMP TABLE demo_orders AS
SELECT g AS id, (g % 1000)::int AS customer_id,
       timestamptz '2026-01-01 00:00:00+00'
         + g * interval '1 minute' AS created_at
FROM generate_series(1,100000) AS g;
ANALYZE demo_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id,created_at FROM demo_orders
WHERE customer_id=42 ORDER BY created_at DESC LIMIT 20;

CREATE INDEX ON demo_orders (customer_id,created_at DESC);
ANALYZE demo_orders;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id,created_at FROM demo_orders
WHERE customer_id=42 ORDER BY created_at DESC LIMIT 20;

یک تغییر، یک مقایسه قابل دفاع

گزارش نهایی باید کوئری مشکل‌دار، علت مشاهده‌شده، پلن قبل و بعد و نتیجه قابل اندازه‌گیری را نشان دهد. برای محیط تولید، زمان ساخت ایندکس و محدودیت قفل‌گذاری را با تیم پایگاه داده هماهنگ کنید؛ نمونه آموزشی CREATE INDEX معمولی است و نسخه مناسب مهاجرت آنلاین نیاز به تصمیم جدا دارد. اگر زمان پاسخ بهتر نشد، به جای افزودن ایندکس‌های بیشتر، فرضیه را بازبینی کنید. هدف داشتن پلنی پر از index scan نیست؛ پاسخ سریع‌تر و پایدارتر با هزینه عملیاتی قابل قبول است.

توضیح‌ها و پیشنهادهای اجرایی این مطلب، تحلیل تحریریه دانشنامه لیان هستند.منابع: PostgreSQL 18 — Using EXPLAIN · PostgreSQL — Multicolumn indexes · PostgreSQL — ANALYZE

این مطلب بازنویسی تحلیلی دانشنامه لیان بر پایه منبع اصلی است.مشاهده منبع اصلی