آشنایی با دستور EXPLAIN در دیتابیس PostgreSQL

PostgreSQL یک سیستم پایگاه داده رابطه‌ای پیشرفته، در سطح سازمانی است که به صورت منبع باز ارائه می شود. PostgreSQL از کوئری های SQL (رابطه ای) و JSON (غیر رابطه ای) پشتیبانی می کند. PostgreSQL یک پایگاه داده بسیار پایدار است که دارای دستورات مختلفی مانند دستور EXPLAIN برای دریافت کوئری های مختلف است. این سیستم قریب به 20 سال است که توسط علاقه مندان و کاربرانش توسعه داده و پشتیبانی می شود. پستگرس کیو ال به عنوان پایگاه داده اولیه برای بسیاری از برنامه های کاربردی وب و همچنین برنامه های کاربردی تلفن همراه و تحلیلی استفاده می شود. این سیستم از زبان‌های برنامه نویسی مختلفی چون روبی، سی شارپ، جاوا، پایتون، جاوا اسکریپت و پریل پشتیبانی می کند.

چرا از PostgreSQL استفاده می‌شود؟

سیستم PostgreSQL نسبت به SQL به شدت قدرتمندتر است و به دلایل بسیاری استفاده می شود که از جمله می توان به موارد زیر اشاره نمود:

پشتیبانی از دستورات مختلف: یکی از دستوراتی که در دیتابیس PostgreSQL پشتیبانی می شود، دستور EXPLAIN است که در ادامه با کاربردهای این دستور آشنا خواهیم شد.

انواع داده ها: PostgreSQL از تمام انواع داده‌های مورد نیاز مانند اسناد، اصول اولیه، هندسه، ساختارها و غیره پشتیبانی می کند.

یکپارچگی داده ها: Postgres یکپارچگی داده های شما را با معرفی محدودیت ها و تنظیم داده هایی که اضافه می کنید فراهم می کند. با دیتابیس PostgreSQL می‌توانید سوابق نامعتبر را مشاهده و از بین ببرید.

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

بازیابی کامل: PostgreSQL بالاترین سطح اطمینان را برای داده های شما فراهم می کند. با گزینه های پیچیده تکراری آن، داده‌های شما کاملاً ایمن هستند. علاوه بر این، همیشه می توانید از با ارزش ترین اطلاعات خود یک نسخه پشتیبان تهیه نمایید و با دستور EXPLAIN اطلاعات کامل آن را مشاهده کنید.

توسعه پذیری: این پایگاه داده، امکان انتخاب طیف وسیعی از انواع داده‌ها را برای شما فراهم می کند و حتی می توانید از دستور EXPLAIN برای نمایش اطلاعات بیشتر این داده‌ها استفاده نمایید.

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

پشتیبانی از داده‌های غیر رابطه‌ای: پشتیبانی از اسناد JSON، XML، Hstore و Cstore در واقع Postgres را به پایگاه داده NoSQL تبدیل می کند.

موارد استفاده از PostgreSQL

از این سیستم در موارد زیر استفاده می‌شود:

1) پایگاه داده قوی در پشته LAPP:

LAPP مخفف Linux، Apache، PostgreSQL وPHP (یا Python و Perl) است. PostgreSQL در درجه اول به عنوان یک پایگاه داده پشتیبان قوی استفاده می شود که بسیاری از وب سایت ها و برنامه های کاربردی وب داینامیک را پوشش می دهد و با برخورداری از دستورهای مختلف از جمله دستور EXPLAIN تمام نیازهای شما برای گرفتن کوئری‌های مختلف را برطرف می کند.

2) پایگاه داده معاملات عمومی

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

3) پایگاه داده جغرافیایی

PostgreSQL با پسوند PostGIS از پایگاه داده های مکانی برای سیستم های اطلاعات جغرافیایی (GIS) پشتیبانی می کند.

دستور Explain در PostgreSQL چیست؟

عبارت EXPLAIN یک کلمه کلیدی رزرو شده است که در پایگاه داده های مختلف SQL استفاده می شود. دستور EXPLAIN اطلاعاتی در مورد نحوه اجرای یک پرس و جو پایگاه داده SQL را به شما ارائه می دهد. در MySQL، از EXPLAIN می‌توان  در کوئری هایی مانند SELECT, INSERT, DELETE, REPLACE, UPDATE استفاده کرد. دستور آن برای یک کوئری ساده، به صورت زیر است:

EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';

MySQL در خروجی طرح اجرای دستورات خود را (با توضیح اینکه کدام فرآیندها در هنگام اجرای دستور به ترتیب انجام می شوند) نشان می دهد.

توجه: اگر دستور EXPLAIN در سیستم شما کار نمی کند، کاربر پایگاه داده شما ممکن است فیچر SELECT جداول یا نماهایی را که در دستور خود استفاده می کنید، نداشته باشد.

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

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

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

فقط مطمئن شوید که به جای اینکه فوراً نتیجه ای در SQL ایجاد شود، دلیل محاسبه را در برنامه خود مستند کنید تا شخص دیگری که قرار است با پایگاه داده کار کند به راحتی متوجه عملیات بشود. دستور EXPLAIN به کاربرانی که دسترسی سطح بالا ندارند، جزئیات کوئری را نمایش نمی دهد؛ از این رو امنیت دیتاها را به خوبی حفظ می کند. نکته اصلی در مورد دستور EXPLAIN این است که در ابتدای کوئری یعنی قبل از SELECT، INSERT، UPDATE و غیره استفاده می شود.

نمایش خطاها با دستور EXPLAIN در SQL

اگر کوئری که در آن از دستور EXPLAIN استفاده کردید به درستی تجزیه نشد، می توانید دستور SHOW WARNINGS را در ویرایشگر کوئری MySQL خود تایپ کنید تا اطلاعات مربوط به آخرین دستوری را که اجرا شده و تشخیصی نداشته، نشان دهد. SQL نمی تواند اجرای کوئری بهتری نسبت به EXPLAIN ارائه دهد. اما ممکن است نکاتی را در مورد آن قطعات کوئری که می تواند پردازش کند به شما ارائه دهد. فرض کنید از کوئری زیر در پایگاه داده ای استفاده می کنیم که در واقع جدول foo ندارد:

 EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';

خروجی MySQL این کوئری مانند زیر خواهد بود:

ERROR 1146 (42S02): Table 'db.foo' doesn't exist

متغیرهای قابل استفاده در دستور EXPLAIN

دستور EXPLAIN در PostgreSQL به صورت زیر استفاده می شود:

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

البته به جای Option می‌توان مقادیر زیر را نیز قرار داد:

• ANALYZE [ boolean ]
• VERBOSE [ boolean ]
• COSTS [ boolean ]
• SETTINGS [ boolean ]
• BUFFERS [ boolean ]
• WAL [ boolean ]
• TIMING [ boolean ]
• SUMMARY [ boolean ]
• FORMAT { TEXT | XML | JSON | YAML {

با اجرای قطعه کد فوق نتیجه نشان می دهد که چگونه جدول های ارجاع داده شده اسکن می شود. با اسکن متوالی ساده، اسکن فهرست، و دیگر روش های اسکن، از الگوریتم های خاصی برای پیوستن به ردیف های مورد نیاز استفاده می شود. مهم‌ترین بخش نمایشگر، هزینه برآوردی اجرای دستور EXPLAIN است که حدس برنامه در مورد مدت زمان اجرای دستور می باشد. (در واحدهای هزینه که دلخواه هستند، اما معمولاً به معنای واکشی حافظه، اندازه گیری می شوند). در واقع دو عدد نشان داده شده است:

• هزینه راه اندازی قبل از بازگرداندن ردیف اول
• هزینه کل برای بازگرداندن تمام ردیف ها

 برای بیشتر کوئری ها، هزینه کل مهم است، اما در زمینه هایی مانند استعلام فرعی در EXISTS، برنامه به جای کوچکترین هزینه کل، کوچکترین هزینه راه‌اندازی را انتخاب می کند (زیرا به هر حال دستور اجرایی پس از دریافت یک ردیف متوقف می شود). همچنین، اگر تعداد ردیف‌ها را با یک LIMIT محدود کنید، برنامه بین هزینه های نقطه پایانی، میانگین گیری مناسبی انجام می دهد تا تخمین بزند که کدام طرح واقعاً کمترین هزینه را دارد و بهینه است.

گزینه ANALYZE باعث می‌شود که دستور واقعاً اجرا شود. سپس آمار زمان اجرای واقعی به نمایشگر اضافه می شود.

آمار زمان اجرا شامل: کل زمان سپری شده در هر گره پلان (بر حسب میلی ثانیه) و تعداد کل ردیف هایی که واقعاً برگردانده شده است. این نتیجه گیری به منظور مقایسه دو نتیجه انتخابی پیش فرض و نتیجه واقعی انجام می شود. (می‌توان نتیجه گرفت آیا هزینه تخمین زده شده توسط برنامه به واقعیت نزدیک بوده است یا خیر). به خاطر داشته باشید که دستور در واقع زمانی اجرا می شود که از گزینه ANALIZE استفاده شده باشد. اگر چه دستور EXPLAIN هر خروجی‌ای را که SELECT برمی‌گرداند نشان نمی دهد، اما اگر می خواهید EXPLAIN ANALYZE در یک دستور INSERT یا بدون دستور روی داده های شما اعمال شود، از دستور زیر استفاده کنید:

BEGIN;
EXPLAIN ANALYZE ;
ROLLBACK;

در این دستور فقط گزینه های ANALYZE و VERBOSE را می توان به ترتیب، بدون احاطه کردن لیست گزینه ها مشخص کرد.

معرفی مولفه‌های دستور EXPLAIN

در دستور EXPLAIN مولفه‌های زیر به کار گرفته می شوند:

• ANALYZE

اجرای این دستور زمان واقعی اجرا و سایر آمارها را نشان داده می دهد. مقدار این پارامتر به طور پیش فرض روی FALSE قرار دارد.

• VERBOSE

این پارامتر در دستور EXPLAIN  برای نمایش اطلاعات تکمیلی در مورد طرح، فهرست ستون های خروجی را برای هر گره در درخت پلان، جدول و نام های توابع واجد شرایط اسکیما، استفاده می شود.

همیشه متغیرها را در عبارات با نام مستعار جدول محدوده آن‌ها برچسب گذاری کنید و همیشه نام هر تریگر را که در آمار نمایش داده می شود پرینت کنید. شناسه کوئری نیز اگر محاسبه شده باشد نمایش داده می شود، برای جزئیات بیشتر از compute_query_id استفاده کنید. مقدار این پارامتر به طور پیشفرض روی FALSE قرار دارد.

• COSTS

شامل اطلاعات مربوط به راه‌اندازی تخمینی و هزینه کل هر گره طرح، و همچنین تعداد تخمینی ردیف ها و عرض تخمینی هر ردیف باشد. مقدار این پارامتر به طور پیشفرض روی TRUE قرار دارد.

• SETTINGS

SETTING در دستور EXPLAIN شامل اطلاعات مربوط به پارامترهای پیکربندی است. به طور خاص، گزینه هایی را که روی کوئری تأثیر می گذارد ارائه می دهد. مقدار این پارامتر به طور پیشفرض روی FALSE قرار دارد.

• BUFFERS

این پارامتر اطلاعاتی را درباره استفاده از بافر اضافه می کند. BUFFERS فقط زمانی از این اطلاعات استفاده می کند که ANALYZE فعال باشد. به طور پیشفرض، مقدار پارامتر BUFFERS روی FALSE قرار دارد. اطلاعات اضافه مربوط به این پارامتر در دستور EXPLAIN، شامل تعداد بلوک های به اشتراک گذاشته شده، خوانده شده، نوشته شده، تعداد بلوک های محلی، خوانده شده و نوشته شده، تعداد بلوک های موقت خوانده شده و نوشته شده، و زمان صرف شده برای خواندن و نوشتن بلوک های فایل داده (در میلی ثانیه) است که اگر پارامتر track_io_timing فعال باشد، نشان داده می شود.

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

• WAL

به طور خاص، تعداد رکوردها، تعداد تصاویر تمام صفحه (fpi) و مقدار WAL تولید شده بر حسب بایت را شامل می شود. در قالب متن، فقط مقادیر غیر صفر چاپ می شوند. این پارامتر فقط زمانی استفاده می شود که ANALYZE فعال باشد. مقدار پیشفرض آن روی FALSE قرار دارد.

• TIMING

با اجرای این دستور زمان واقعی راه اندازی و زمان صرف شده در هر گره در خروجی چاپ می شود. سربار خواندن مکرر ساعت سیستم، می تواند اجرای کوئری را به طور قابل توجهی در برخی سیستم ها کند کند؛ بنابراین ممکن است تنظیم این پارامتر در دستور EXPLAIN روی FALSE زمانی مفید باشد که فقط تعداد ردیف های دیتا و نه زمان های دقیق آن ها مورد نیاز باشد. در این دستور زمان اجرای کل عبارت اندازه گیری می شود. این پارامتر فقط زمانی استفاده می شود که ANALYZE فعال باشد و مقدار پیشفرض آن TRUE است.

• SUMMARY

با اجرای این دستور می توانید اطلاعات خلاصه (مثلاً اطلاعات زمان بندی کل) را بعد از طرح کوئری درج کنید. این اطلاعات خلاصه در هنگام استفاده از ANALYZE گنجانده می شوند؛ اما به طور پیشفرض گنجانده نمی شود و باید با استفاده از این گزینه، آن را فعال کرد. زمان برنامه ریزی در EXPLAIN EXECUTE شامل زمان مورد نیاز برای واکشی طرح از حافظه کش و زمان مورد نیاز برای برنامه ریزی مجدد در صورت لزوم است.

• FORMAT

با اجرای این پارامتر در دستور EXPLAIN  می توانید فرمت خروجی را که شامل TEXT، XML، JSON یا YAML است، مشخص نمایید. خروجی غیر متنی حاوی اطلاعاتی مشابه فرمت خروجی متن است، اما تجزیه و تحلیل برای برنامه ها آسان تر است. این پارامتر به طور پیشفرض روی TEXT قرار دارد.

• boolean

این پارامتر مشخص می کند که آیا گزینه انتخاب شده باید 0 یا 1 باشد و می تواند مقادیر TRUE، ON یا 1 برای فعال کردن گزینه، و FALSE، OFF یا 0 برای غیرفعال کردن را به خود اختصاص بدهد. می توان مقدار پیشفرض Boolean را حذف کرد که در این صورت باید به TRUE تغییر یابد.

• statement

این پارامتر شامل هر جمله یا دستوری است که می‌خواهید اجرای آن را به صورت کیفی ببینید. در واقع عباراتی مانند SELECT, INSERT, UPDATE, DELETE, VALUES, EXECUTE, DECLARE, CREATE TABLE AS, و یا CREATE MATERIALIZED VIEW AS را در بر می گیرد. نتیجه استفاده از پارامتر statement در دستور EXPLAIN یک توصیف متنی از طرح انتخاب شده است، که به صورت اختیاری با آمار اجرا ادغام شده است.

برای اینکه به برنامه کوئری PostgreSQL اجازه داده شود هنگام بهینه سازی کوئری ها تصمیمات منطقی و آگاهانه اتخاذ کند، pg_statistic داده ها باید برای همه جداول استفاده شده در کوئری به روز باشند.

به منظور اندازه گیری هزینه زمان اجرای هر گره در طرح اجرا، اجرای EXPLAIN ANALYZE سربار پروفایل را به اجرای کوئری اضافه می کند. در نتیجه، اجرای EXPLAIN ANALYZE بر روی یک کوئری گاهی اوقات می تواند به طور قابل توجهی بیشتر از اجرای همان کوئری در حالت معمول زمان ببرد.

مقدار سربار بستگی به ماهیت کوئری و همچنین پلتفرم مورد استفاده دارد. بدترین حالت برای گره‌های پلان اتفاق می افتد که به زمان بسیار کمی برای اجرا نیاز داشته باشند و در ماشین هایی که سیستم عامل نسبتاً کندی دارند، نیاز به دریافت زمان دارند.

آشنایی بیشتر با مفهوم EXPLAIN با ارائه یک مثال

ما در این بخش برای آشنایی بیشتر با مفهوم دستور EXPLAIN به بررسی یک مثال می پردازیم. برای نمایش طرح برای یک کوئری ساده روی یک جدول با یک ستون integer و 10000 ردیف دستور زیر را وارد می کنیم:

EXPLAIN SELECT * FROM foo;
                       QUERY PLAN
 Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4)
(row 1)

فرمت خروجی باید به صورت زیر باشد:

EXPLAIN (FORMAT JSON) SELECT * FROM foo;
           QUERY PLAN
[                             +
   {                           +
     "Plan": {                 +
       "Node Type": "Seq Scan",+
       "Relation Name": "foo", +
       "Alias": "foo",         +
       "Startup Cost": 0.00,   +
       "Total Cost": 155.00,   +
       "Plan Rows": 10000,     +
       "Plan Width": 4         +
     }                         +
   }                           +
 ]
(1 row)

اگر پروفایلی وجود داشته باشد و از یک کوئری با شرط  WHERE استفاده کنیم، EXPLAIN طرح متفاوتی را نشان می دهد:

EXPLAIN SELECT * FROM foo WHERE i = 4;
                         QUERY PLAN
Index Scan using fi on foo  (cost=0.00..5.98 rows=1 width=4)
   Index Cond: (i = 4)
(2 rows)

در اینجا مجدد همان کوئری را تکرار می کنیم اما در قالب YAML:

EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4';
          QUERY PLAN
- Plan:                      +
     Node Type: "Index Scan"  +
     Scan Direction: "Forward"+
     Index Name: "fi"         +
     Relation Name: "foo"     +
     Alias: "foo"             +
     Startup Cost: 0.00       +
     Total Cost: 5.98         +
     Plan Rows: 1             +
     Plan Width: 4            +
     Index Cond: "(i = 4)"    
(1 row)

قطعه کد زیر همان کوئری را با ارائه هزینه زمانی نشان می دهد:

EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4;
        QUERY PLAN
Index Scan using fi on foo
   Index Cond: (i = 4)
(2 rows)

قطعه کد زیر در دستور EXPLAIN همان کوئری را با استفاده از یک تابع جمع آوری شده نشان می دهد:

EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
                             QUERY PLAN
Aggregate  (cost=23.93..23.93 rows=1 width=4)
   ->  Index Scan using fi on foo  (cost=0.00..23.92 rows=6 width=4)
         Index Cond: (i < 10)
(3 rows)

قطعه کد زیر استفاده از EXPLAIN EXECUTE برای نمایش طرح اجرا را برای یک کوئری نشان می دهد:

PREPARE query(int, int) AS SELECT sum(bar) FROM test
    WHERE id > $1 AND id < $2
    GROUP BY foo;
EXPLAIN ANALYZE EXECUTE query(100, 200);
                                                       QUERY PLAN                                                       
HashAggregate  (cost=9.54..9.54 rows=1 width=8) (actual time=0.156..0.161 rows=11 loops=1)
   Group Key: foo
   ->  Index Scan using test_pkey on test  (cost=0.29..9.29 rows=50 width=8) (actual time=0.039..0.091 rows=99 loops=1)
         Index Cond: ((id > $1) AND (id < $2))
 Planning time: 0.197 ms
 Execution time: 0.225 ms
(6 rows)

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

کاردینالیته و گزینش پذیری

قبلاً دیدیم که کوئری ما باید 98 درصد از ردیف های جدول را بازیابی کند. در اینجا دو اصطلاح معمول برای پایگاه‌های داده استفاده می شود:

کاردینالیته: Cardinality به تعداد مقادیر منحصر به فرد در یک ستون خاص در جدول اشاره دارد.

انتخابی: انتخاب تعداد مقادیر منحصر به فرد تولید شده توسط یک عملیات (به عنوان مثال، اسکن شاخص یا فیلتر)، نسبت به تعداد کل ردیف هاست. هرچه گزینش پذیری در دستور EXPLAIN بالاتر باشد، احتمال بیشتری وجود دارد که PostgreSQL بتواند از یک شاخص استفاده کند.