بهینه‌سازی کوئری در PostgreSQL خیلی وقت‌ها پیش می‌آید که می‌خواهیم پرفرمنس نرم‌افزار خود را بهبود دهیم، اما دیتابیس گلوگاه سیستم شده است، در چنین مواقعی خوب است که کوئری‌هایی که به دیتابیس میزنیم را بررسی کنیم و ببینیم که آیا می‌شود آن‌ها را از نظر پرفرمنسی بهبود داد یا خیر.

برای این موضوعPostgreSQL دستور “EXPLAIN” را ارائه می‌دهد که پلن اجرای کوئری (منظور همان execution plan است) را نشان می‌دهد و به تشخیص نقاط ضعف احتمالی کمک می‌کند.

به طور کلی بهبود کوئری‌ چند مرحله دارد که در ادامه به آن خواهیم پرداخت.

1.استفاده از EXPLAIN:
هنگامی که یک کوئری را با عبارت کلیدی EXPLAIN پیشوند می‌کنید، PostgreSQL یک پلن اجرایی تولید می‌کند بدون این که لازم باشد کوئری را به طور واقعی روی دیتابیس اجرا کند. به عنوان مثال:

EXPLAIN SELECT * 
FROM your_table 
WHERE some_column = 'some_value';

2.خواندن پلن اجرایی:
پلن اجرایی یک دید کلی‌ای فراهم می‌کند از نحوه‌ی‌ اجرای کوئری. این شامل جزئیاتی مانند ترتیب اسکن جدول، اسکن ایندکس، الگوریتم‌های Join، و استفاده‌های احتمالی از توابع تجمعی و یا پنجره‌ای است.

3.تجزیه و تحلیل برنامه:
به دنبال اسکن‌های متوالی، اسکن‌های ایندکس، انواع Join (حلقه تو در تو، هش، ادغام)، و دنباله‌های احتمالی عملیات باشید. بعد از این مرحله میتوانید اسکن‌های کاملی که روی جدول انجام میشود، ایندکس‌هایی که بدون استفاده می‌مانند و ایندکس‌هایی که به بهترین شکل عمل نمیکنند را پیدا کنید.

۴.استفاده از ایندکس:
اطمینان حاصل کنید که ایندکس‌های مناسب موجود هستند و توسط کوئری به طور موثر استفاده می‌شوند.

۵.بازنویسی کوئری:
کوئری را برای بهینه‌سازی عملکرد بازنویسی کنید. این ممکن است شامل بازسازی کوئری برای بهره‌وری بیشتر از ایندکس‌ها، اجتناب از Join‌های ناکارآمد، یا بازنویسی زیرکوئری‌ها برای بهبود کارایی باشد.

۶.تحلیل پس از بهینه‌سازی:
بعد از انجام تغییرات، دستور EXPLAIN را مجددا اجرا کنید تا اطمینان حاصل کنید که کوئری جدید از نظر پرفرمنسی عملکرد بهتری از کوئری قبلی خواهد داشت

اما همه مطالب گفته شده مستلزم داشتن دانش کافی از دستور Explain است بنابراین در بخش بعدی قصد داریم با این دستور آشنا شویم و بدانیم که خروجی‌های احتمالی این دستور چه مفهومی را منتقل میکند.

دستور EXPLAIN

۱. Sequential Scan:
یک Sequential Scan شامل اسکن کل جدول از ابتدا تا انتها است. این روش معمولا روش کندی‌ است، به ویژه برای جداول بزرگ، و معمولا زمانی استفاده می‌شود که یک ایندکس در دسترس نباشد یا مفید نباشد. منظور از مفید نبودن ایندکس چیست؟ بعضی وقت‌ها پیش می‌آید در یک کوئری با وجود این که روی فیلد‌های کوئری ایندکس وجود دارد، اما وجود ایندکس به پیدا کردن خروجی کوئری کمکی نمیکند و بلکه فرآیند یافتن خروجی را کندتر میکند، در ادامه به این موضوع خواهم پرداخت.

۲. اسکن ایندکس:
اسکن ایندکس به معنای استفاده از یک ایندکس برای به دست آوردن خروجی کوئری است، این روش معمولا از روش Sequential Scan کاراتر است. انواع مختلفی از اسکن ایندکس وجود دارد، مانند اسکن Bitmap Index یا اسکن Index-only که بسته به نوع کوئری و پیکربندی ایندکس به کار گرفته میشود.
در ادامه به بررسی أنواع ایندکس اسکن خواهیم پرداخت.

• اسکن Bitmap Index:
  این روش اسکن هنگامی استفاده می‌شود که چندین ایندکس بتوانند از طریق عملیات بیتمپ به طور کارا شرایط پیچیده کوئری را ارزیابی نمایند.

• اسکن Index-only:
  اگر کوئری تنها نیاز به داده از خود ایندکس دارد، PostgreSQL ممکن است یک اسکن Index-only انجام دهد که نیازی به دسترسی به داده‌های جدول واقعی را ندارد.

مثال‌ها

بیایید فرض کنیم که یک جدول به نام “users” با ستون‌های “id”، “name”، و “age” داریم. بر روی ستون‌های “name” و “age” ایندکس‌های جداگانه داریم. می‌خواهیم یک کوئری انجام دهیم که از هر دو ایندکس استفاده کند تا بازیابی داده‌ها را سریع‌تر کند.

-- ایجاد یک جدول نمونه
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- ایجاد ایندکس بر روی ستون‌های name و age
CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_age ON users (age);

حالا فرض کنید می‌خواهیم یک کوئری بزنیم که از این ایندکس‌ها استفاده کند. به عنوان مثال، می‌خواهیم شناسه‌های کاربرانی که نام “جان” داشته و در بازه‌ی سنی بین ۳۰ و ۴۰ سال قرار دارند را بازیابی کنیم.

EXPLAIN SELECT id
FROM users
WHERE name = 'John' AND age BETWEEN 30 AND 40;

زمانی که این کوئری را اجرا می‌کنید، ممکن است ببینید که PostgreSQL از یک اسکن بیتمپ استفاده می‌کند تا به طور کارا نتایج از ایندکس‌های “name” و “age” را ترکیب کرده و اطلاعات مورد نیاز را بازیابی کند. اسکن بیتمپ از نظر پرفرمنسی موثر است زیرا ترکیب چندین ایندکس می‌تواند تعداد ردیف‌هایی که باید از جدول بازیابی شود را به طور قابل توجهی کاهش دهد.

بیایید تصور کنیم که یک جدول به نام “products” با ستون‌های “product_id”, “product_name”, و “price” داریم. ما یک ایندکس بر روی ستون “price” ایجاد می‌کنیم و می‌خواهیم تنها قیمت‌ها را از جدول با استفاده از ایندکس بدون بررسی داده‌های خود جدول بازیابی کنیم.

-- ایجاد یک جدول نمونه
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
);

-- ایجاد یک ایندکس بر روی ستون price
CREATE INDEX idx_price ON products (price);

حالا، بیایید یک کوئری اجرا کنیم که می‌تواند از اسکن Index-only بهره ببرد تا از دسترسی به داده‌های جدول اجتناب کند:

EXPLAIN SELECT price
FROM products;

وقتی چنین کوئری‌ای زده میشود، با توجه به این که هیچ شرطی ندارد که از اطلاعاتی که ایندکس روی آن وجود ندارد استفاده کند، بدون این که داده‌ای از خود داده‌های اصلی جدول مورد نظر بازیابی شود، خروجی کوئری به دست می‌آید. این گونه از کوئری سریعترین نوع کوئری است.

حالا بیایید یک سناریو را در نظر بگیریم که یک کوئری ممکن است از یک اسکن ایندکس یا یک Sequential Scanاستفاده کند.

بیایید فرض کنیم که یک جدول مشابه به نام “orders” با ستون‌های “order_id”, “customer_id”, “order_date”, و “total_amount” داریم. ما یک ایندکس بر روی ستون “order_date” ایجاد می‌کنیم.

-- ایجاد یک جدول نمونه
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- ایجاد یک ایندکس بر روی ستون order_date
CREATE INDEX idx_order_date ON orders (order_date);

حال بیایید یک کوئری ایجاد کنیم که ممکن است از یک اسکن ایندکس یا یک Sequential Scanاستفاده کند:

EXPLAIN SELECT order_id, total_amount
FROM orders
WHERE order_date >= '2023-01-01' AND 
      order_date < '2023-02-01';

بسته به توزیع داده و انتخاب مقادیر “order_date”، PostgreSQL اگر ببیند استفاده از ایندکس به دلیل شرایط فیلتری که در کوئری استفاده شده است کارا‌تر است، تصمیم میگیرد که از اسکن ایندکس استفاده کند، از طرفی اگر ببینید استفاده از ایندکس کارا نیست یا بخشی بزرگی از جدول نیاز به دسترسی داشته باشد، ممکن است به Sequential Scan روی بیاورد.

در یک پست دیگر به زودی به join ها و توابع پنجره‌ای و تجمعی در خروجی Explain خواهیم پرداخت