رفع مشکل پرفرمنس PostgreSQL با کوئری های پارامتری در دات نت

mohsen•1 سال قبل
ارسال شده در
mohsen
postgresqlc sharp

یک مشکل عجیب که ممکن است در زمان کار با کوئری های PostgreSQL مواجه شوید. مربوط به اجرای کوئری های پارامتری است. کوئری های اجرا شده توسط Entity Framework با استفاده از پارامترها مانند کوئری زیر اجرا می شوند:

      SELECT a.*
      FROM alarms AS a
      WHERE a.group = @__AG_0
      ORDER BY a.datetime DESC
      LIMIT @__p_2 OFFSET @__p_1

و در این کد سه پارامتر @__AG_0 و @__p_2 و @__p_1 به کوئری تولید شده با انتیتی فریم ورک اضافه شده است.

در جدول alarms نوع فیلد group را char تعیین کرده ایم ولی با اجرای دستور روی بعضی از کوئری ها با مشکل کندی در اجرا گاها تا 24 ثانیه تاخیر مواجه می شویم. دلیل این مشکل انتخاب پلن اجرای اشتباه توسط PostgreSQL است.

برای بررسی بیشتر کوئری زیر را برای بررسی پلن اجرایی مورد استفاه اجرا می کنیم:

      DEALLOCATE stmt;

PREPARE stmt(text) AS
SELECT * FROM alarms WHERE group = $1;
EXPLAIN ANALYZE EXECUTE stmt('C');

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

      Gather  (cost=1000.00..1904715.25 rows=498950 width=64) (actual time=2799.037..2824.644 rows=2 loops=1)

آنالیز داده های این خروجی:

  • Cost=1000.00..1904715.25 مشخص می کند که کوئری باید قسمت بزرگی از جدول را برای انجام آن اسکن کند
  • rows=498950 تعداد ردیف هایی که تخمین زده شده که احتمالن این کوئری می خواند اما در واقع این مقدار دو ردیف بوده
  • در ادامه هم زمان تقریبی 27 ثانیه را برای اجرای واقعی نمایش داده است

اما با اجرای دستور بدون پارامتر به صورت زیر نتایج کاملن متفاوت است:

      EXPLAIN ANALYZE SELECT * FROM alarms WHERE alarm_group = 'C';

نتایج بدون پارامتر:

      Append  (cost=0.42..1195.28 rows=910 width=65) (actual time=4.272..5.190 rows=2 loops=1)

همانطور که می بینیم فاصله بین تخمین و واقعیت خیلی کمتر شد.

و اما مشکل کجاست؟

مشکل در این مورد از نوع پارامتر تعریف شده بود. چون در دات نت نوع فیلد مشخص نشده بود و از string در کلاس Alarm استفاده شده بود. Npgsql پیش فرض از نوع Text استفاده می کرد. در حالی که نوع ستون در دیتابیس char تعریف شده است. با اضافه کردن تعریف نوع ستون در زمان تعریف جدول در دات نت، PostgreSQL از پلن بهتری برای اجرای کوئری استفاده کرد و مشکل برطرف شد:

      builder.Property(x => x.Group).HasColumnName("group").HasColumnType("char");
رای
0
ارسال نظر
مرتب سازی:
اولین نفری باشید که نظر می دهید!