مہینوں پہلے - عوام PostgreSQL پر۔
اس وقت سے لے کر اب تک آپ اسے 6000 بار استعمال کر چکے ہیں، لیکن ایک کارآمد خصوصیت پر شاید کسی کا دھیان نہیں گیا ہو گا ساختی اشارے، جو کچھ اس طرح نظر آتے ہیں:
ان کو سنیں اور آپ کی درخواستیں "ریشمی ہموار ہو جائیں گی"۔ 🙂
لیکن سنجیدگی سے، بہت سے حالات جو درخواست کو سست اور وسائل کے لحاظ سے "کھوڑا" بناتے ہیں، عام ہیں اور پلان کی ساخت اور ڈیٹا سے پہچانا جا سکتا ہے۔.
اس صورت میں، ہر ایک ڈویلپر کو اپنے طور پر ایک اصلاحی آپشن تلاش کرنے کی ضرورت نہیں ہوگی، صرف اپنے تجربے پر انحصار کرتے ہوئے - ہم اسے بتا سکتے ہیں کہ یہاں کیا ہو رہا ہے، اس کی وجہ کیا ہو سکتی ہے، اور ایک حل کے ساتھ آنے کا طریقہ. جو ہم نے کیا۔
آئیے ان معاملات پر گہری نظر ڈالتے ہیں - ان کی تعریف کیسے کی جاتی ہے اور وہ کن سفارشات کی طرف لے جاتے ہیں۔
موضوع میں بہتر ڈوبنے کے لیے، آپ سب سے پہلے اس سے متعلقہ بلاک کو سن سکتے ہیں۔ ، اور تب ہی ہر مثال کے تفصیلی تجزیہ پر جائیں:
#1: انڈیکس "انڈر ترتیب"
کب کرتا ہے۔
کلائنٹ "LLC Kolokolchik" کے لیے آخری رسید دکھائیں۔
شناخت کرنے کا طریقہ
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
سفارشات
انڈیکس استعمال کیا گیا۔ ترتیب والے فیلڈز کے ساتھ پھیلائیں۔.
: مثال کے طور پر
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
آپ فوری طور پر دیکھ سکتے ہیں کہ انڈیکس کے ذریعہ 100 سے زیادہ ریکارڈز کو گھٹا دیا گیا تھا، جو پھر سب کو ترتیب دیا گیا تھا، اور پھر صرف ایک ہی رہ گیا تھا۔
ہم ٹھیک کرتے ہیں:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
ایسے قدیم نمونے پر بھی - 8.5x تیز اور 33x کم پڑھنا. اثر واضح ہو گا، ہر قدر کے لیے آپ کے پاس جتنے زیادہ "حقائق" ہوں گے۔ fk.
میں نوٹ کرتا ہوں کہ اس طرح کا انڈیکس ایک "سابقہ" انڈیکس کے طور پر کام کرے گا جس کے ساتھ دیگر سوالات کے لیے پچھلے ایک سے بدتر نہیں fk، جہاں کی طرف سے چھانٹ رہا ہے pk نہیں تھا اور نہیں ہے (آپ اس کے بارے میں مزید پڑھ سکتے ہیں۔ )۔ خاص طور پر، یہ عام فراہم کرے گا واضح غیر ملکی کلیدی حمایت اس فیلڈ کی طرف سے.
#2: انڈیکس انٹرسیکشن (BitmapAnd)
کب کرتا ہے۔
کلائنٹ "LLC Kolokolchik" کے لیے تمام معاہدے دکھائیں جو "NJSC Lyutik" کی جانب سے مکمل کیے گئے ہیں۔
شناخت کرنے کا طریقہ
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scanسفارشات
تخلیق جامع انڈیکس دونوں ماخذ سے فیلڈز کے ذریعے یا دوسرے سے موجودہ فیلڈز میں سے ایک کو پھیلائیں۔
: مثال کے طور پر
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
ہم ٹھیک کرتے ہیں:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
یہاں فائدہ چھوٹا ہے، کیونکہ بٹ میپ ہیپ اسکین اپنے طور پر کافی موثر ہے۔ لیکن ویسے بھی 7x تیز اور 2.5x کم پڑھنا.
#3: اشاریہ جات کو یکجا کرنا (BitmapOr)
کب کرتا ہے۔
پروسیسنگ کے لیے پہلے 20 قدیم ترین "اپنی" یا غیر تفویض کردہ درخواستوں کو اپنی ترجیح کے ساتھ دکھائیں۔
شناخت کرنے کا طریقہ
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scanسفارشات
استعمال کرنے کے لئے یونین [سب] ہر ایک شرط یا بلاکس کے لیے ذیلی سوالات کو یکجا کرنے کے لیے۔
: مثال کے طور پر
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
ہم ٹھیک کرتے ہیں:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
ہم نے اس حقیقت کا فائدہ اٹھایا کہ تمام 20 ضروری ریکارڈ فوری طور پر پہلے بلاک میں حاصل کر لیے گئے تھے، اس لیے دوسرا، زیادہ "مہنگا" بٹ میپ ہیپ اسکین کے ساتھ، پر عمل درآمد بھی نہیں کیا گیا - نتیجے کے طور پر 22 گنا تیز، 44 گنا کم پڑھنا!
اس اصلاح کے طریقہ کار کے بارے میں مزید تفصیلی کہانی ٹھوس مثالوں پر مضامین میں پڑھا جا سکتا ہے۔ и .
عمومی ورژن کئی کلیدوں کے ذریعہ انتخاب کا حکم دیا گیا۔ (اور نہ صرف const / NULL کے جوڑے کے لیے) مضمون میں بحث کی گئی ہے۔ .
#4: ہم بہت زیادہ پڑھتے ہیں۔
کب کرتا ہے۔
ایک اصول کے طور پر، یہ اس وقت ہوتا ہے جب آپ کسی موجودہ درخواست سے "دوسرا فلٹر منسلک" کرنا چاہتے ہیں۔
"اور آپ کے پاس ایسا نہیں ہے، لیکن موتی کے بٹنوں کے ساتھ؟ " فلم "ڈائمنڈ ہینڈ"
مثال کے طور پر، اوپر کام میں ترمیم کرتے ہوئے، پروسیسنگ کے لیے پہلی 20 قدیم ترین "تنقیدی" درخواستیں دکھائیں، چاہے ان کا مقصد کچھ بھی ہو۔
شناخت کرنے کا طریقہ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
سفارشات
[مزید] خصوصی بنائیں WHERE شق کے ساتھ اشاریہ یا انڈیکس میں اضافی فیلڈز شامل کریں۔
اگر فلٹرنگ کی حالت آپ کے کاموں کے لیے "جامد" ہے - یعنی توسیع شامل نہیں ہے مستقبل میں اقدار کی فہرست - WHERE انڈیکس استعمال کرنا بہتر ہے۔ مختلف بولین/اینوم سٹیٹس اس زمرے میں اچھی طرح فٹ ہوتے ہیں۔
اگر فلٹریشن کی حالت مختلف اقدار لے سکتے ہیں۔، ان فیلڈز کے ساتھ انڈیکس کو بڑھانا بہتر ہے - جیسا کہ بٹ میپ اور اوپر کی صورتحال میں ہے۔
: مثال کے طور پر
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
ہم ٹھیک کرتے ہیں:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
جیسا کہ آپ دیکھ سکتے ہیں، پلان سے فلٹرنگ مکمل طور پر ختم ہو گئی ہے، اور درخواست بن گئی ہے۔ 5 گنا تیز.
#5: ویرل میز
کب کرتا ہے۔
آپ کی اپنی ٹاسک پروسیسنگ کی قطار بنانے کی مختلف کوششیں، جب میز پر ریکارڈز کو اپ ڈیٹ کرنے / حذف کرنے کی ایک بڑی تعداد "مردہ" ریکارڈ کی ایک بڑی تعداد کی صورت حال کا باعث بنتی ہے۔
شناخت کرنے کا طریقہ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
سفارشات
دستی طور پر باقاعدگی سے لے ویکیوم [مکمل] یا مناسب طور پر بار بار پروسیسنگ حاصل کریں۔ اس کے پیرامیٹرز کو ٹھیک کرکے، بشمول .
زیادہ تر معاملات میں، اس طرح کے مسائل کاروباری منطق سے طلب کیے جانے پر سوال کی خراب ترتیب کی وجہ سے ہوتے ہیں، جیسا کہ .
لیکن ہمیں یہ سمجھنا چاہیے کہ ویکیوم فل بھی ہمیشہ مدد نہیں کر سکتا۔ اس طرح کے معاملات کے لئے، آپ کو اپنے آپ کو مضمون سے الگورتھم سے واقف ہونا چاہئے. .
#6: انڈیکس کے "وسط" سے پڑھنا
کب کرتا ہے۔
ایسا لگتا ہے کہ انہوں نے تھوڑا پڑھا ہے، اور ہر چیز کا حساب کتاب کیا گیا ہے، اور انہوں نے کسی کو اضافی فلٹر نہیں کیا ہے - لیکن پھر بھی، نمایاں طور پر ہماری پسند سے زیادہ صفحات پڑھے گئے۔
شناخت کرنے کا طریقہ
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
سفارشات
استعمال شدہ انڈیکس کی ساخت اور استفسار میں بیان کردہ کلیدی فیلڈز پر گہری نظر ڈالیں - غالباً، انڈیکس کا حصہ سیٹ نہیں ہے۔. آپ کو زیادہ تر ممکنہ طور پر اسی طرح کا انڈیکس بنانے کی ضرورت ہوگی، لیکن پریفکس فیلڈز کے بغیر، یا .
: مثال کے طور پر
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
سب کچھ ٹھیک لگتا ہے، یہاں تک کہ انڈیکس کے لحاظ سے بھی، لیکن کسی نہ کسی طرح مشکوک ہے - پڑھے گئے 20 ریکارڈز میں سے ہر ایک کے لیے، 4 صفحات کا ڈیٹا گھٹانا پڑا، 32KB فی ریکارڈ - کیا یہ جرات مندانہ نہیں ہے؟ ہاں اور اشاریہ نام tbl_fk_org_fk_cli_idx سوچ کی طرف لے جاتا ہے.
ہم ٹھیک کرتے ہیں:
CREATE INDEX ON tbl(fk_cli);
اچانک - پڑھنے کے لیے 10 گنا تیز اور 4 گنا کم!
اشاریہ جات کے غیر موثر استعمال کی مزید مثالوں کے لیے، مضمون دیکھیں .
#7: CTE × CTE
کب کرتا ہے۔
درخواست میں "چربی" CTE اسکور کیا۔ مختلف میزوں سے، اور پھر ان کے درمیان کرنے کا فیصلہ کیا۔ JOIN.
کیس v12 سے نیچے کے ورژن یا اس کے ساتھ درخواستوں کے لیے متعلقہ ہے۔ WITH MATERIALIZED.
شناخت کرنے کا طریقہ
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
سفارشات
درخواست کا بغور تجزیہ کریں۔ ? اگر ہاں تو hstore/json میں "لغت" کا اطلاق کریں۔ میں بیان کردہ ماڈل کے مطابق .
#8: ڈسک میں تبدیل کریں (ٹمپریچر لکھا ہوا)
کب کرتا ہے۔
ریکارڈ کی ایک بڑی تعداد کی ایک بار پروسیسنگ (چھانٹنا یا منفرد بنانا) اس کے لیے مختص کردہ میموری میں فٹ نہیں ہوتا ہے۔
شناخت کرنے کا طریقہ
-> *
&& temp written > 0سفارشات
اگر آپریشن کے ذریعے استعمال ہونے والی میموری کی مقدار پیرامیٹر کی سیٹ ویلیو سے بہت زیادہ نہیں ہے۔ ، اسے درست کیا جانا چاہئے۔ آپ فوری طور پر سب کے لیے ترتیب میں کر سکتے ہیں، یا آپ کے ذریعے کر سکتے ہیں۔ SET [LOCAL] ایک مخصوص درخواست/لین دین کے لیے۔
: مثال کے طور پر
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
ہم ٹھیک کرتے ہیں:
SET work_mem = '128MB'; -- перед выполнением запроса
واضح وجوہات کی بناء پر، اگر صرف میموری استعمال کی جاتی ہے، اور ڈسک نہیں، تو استفسار بہت تیزی سے عمل میں آئے گا۔ ایک ہی وقت میں، لوڈ کا حصہ بھی HDD سے ہٹا دیا جاتا ہے.
لیکن آپ کو یہ سمجھنے کی ضرورت ہے کہ بہت ساری میموری مختص کرنا ہمیشہ کام نہیں کرے گا - یہ ہر ایک کے لئے کافی نہیں ہوگا۔
#9: غیر متعلقہ اعدادوشمار
کب کرتا ہے۔
اڈے میں ایک ساتھ بہت کچھ ڈالا گیا، لیکن ان کے پاس اسے بھگانے کا وقت نہیں تھا۔ ANALYZE.
شناخت کرنے کا طریقہ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10سفارشات
اسی طرح خرچ کریں۔ ANALYZE.
اس صورت حال میں مزید تفصیل سے بیان کیا گیا ہے۔ .
#10: "کچھ غلط ہو گیا"
کب کرتا ہے۔
مسابقتی درخواست کے انتظار میں ایک تالا تھا، یا CPU/hypervisor ہارڈویئر وسائل کافی نہیں تھے۔
شناخت کرنے کا طریقہ
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
سفارشات
ایک بیرونی استعمال کریں۔ نگرانی کے نظام مسدود کرنے یا وسائل کے غیر معمولی استعمال کے لیے سرور۔ ہم پہلے ہی سینکڑوں سرورز کے لیے اس عمل کو منظم کرنے کے اپنے ورژن کے بارے میں بات کر چکے ہیں۔ и .
ماخذ: www.habr.com