اس وقت سے لے کر اب تک آپ اسے 6000 بار استعمال کر چکے ہیں، لیکن ایک کارآمد خصوصیت پر شاید کسی کا دھیان نہیں گیا ہو گا ساختی اشارے، جو کچھ اس طرح نظر آتے ہیں:
ان کو سنیں اور آپ کی درخواستیں "ریشمی ہموار ہو جائیں گی"۔ 🙂
لیکن سنجیدگی سے، بہت سے حالات جو درخواست کو سست اور وسائل کے لحاظ سے "کھوڑا" بناتے ہیں، عام ہیں اور پلان کی ساخت اور ڈیٹا سے پہچانا جا سکتا ہے۔.
اس صورت میں، ہر ایک ڈویلپر کو اپنے طور پر ایک اصلاحی آپشن تلاش کرنے کی ضرورت نہیں ہوگی، صرف اپنے تجربے پر انحصار کرتے ہوئے - ہم اسے بتا سکتے ہیں کہ یہاں کیا ہو رہا ہے، اس کی وجہ کیا ہو سکتی ہے، اور ایک حل کے ساتھ آنے کا طریقہ. جو ہم نے کیا۔
آئیے ان معاملات پر گہری نظر ڈالتے ہیں - ان کی تعریف کیسے کی جاتی ہے اور وہ کن سفارشات کی طرف لے جاتے ہیں۔
موضوع میں بہتر ڈوبنے کے لیے، آپ سب سے پہلے اس سے متعلقہ بلاک کو سن سکتے ہیں۔ PGConf.Russia 2020 میں میری رپورٹ، اور تب ہی ہر مثال کے تفصیلی تجزیہ پر جائیں:
انڈیکس استعمال کیا گیا۔ ترتیب والے فیلڈز کے ساتھ پھیلائیں۔.
: مثال کے طور پر
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); -- отбор по конкретной паре
یہاں فائدہ چھوٹا ہے، کیونکہ بٹ میپ ہیپ اسکین اپنے طور پر کافی موثر ہے۔ لیکن ویسے بھی 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 گنا کم پڑھنا!
ایک اصول کے طور پر، یہ اس وقت ہوتا ہے جب آپ کسی موجودہ درخواست سے "دوسرا فلٹر منسلک" کرنا چاہتے ہیں۔
"اور آپ کے پاس ایسا نہیں ہے، لیکن موتی کے بٹنوں کے ساتھ؟ " فلم "ڈائمنڈ ہینڈ"
مثال کے طور پر، اوپر کام میں ترمیم کرتے ہوئے، پروسیسنگ کے لیے پہلی 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;
جیسا کہ آپ دیکھ سکتے ہیں، پلان سے فلٹرنگ مکمل طور پر ختم ہو گئی ہے، اور درخواست بن گئی ہے۔ 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
سفارشات
دستی طور پر باقاعدگی سے لے ویکیوم [مکمل] یا مناسب طور پر بار بار پروسیسنگ حاصل کریں۔ آٹو ویکیوم اس کے پیرامیٹرز کو ٹھیک کرکے، بشمول ایک مخصوص میز کے لئے.
ایسا لگتا ہے کہ انہوں نے تھوڑا پڑھا ہے، اور ہر چیز کا حساب کتاب کیا گیا ہے، اور انہوں نے کسی کو اضافی فلٹر نہیں کیا ہے - لیکن پھر بھی، نمایاں طور پر ہماری پسند سے زیادہ صفحات پڑھے گئے۔
شناخت کرنے کا طریقہ
-> 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 سوچ کی طرف لے جاتا ہے.
ریکارڈ کی ایک بڑی تعداد کی ایک بار پروسیسنگ (چھانٹنا یا منفرد بنانا) اس کے لیے مختص کردہ میموری میں فٹ نہیں ہوتا ہے۔
شناخت کرنے کا طریقہ
-> *
&& temp written > 0
سفارشات
اگر آپریشن کے ذریعے استعمال ہونے والی میموری کی مقدار پیرامیٹر کی سیٹ ویلیو سے بہت زیادہ نہیں ہے۔ ورک_میم، اسے درست کیا جانا چاہئے۔ آپ فوری طور پر سب کے لیے ترتیب میں کر سکتے ہیں، یا آپ کے ذریعے کر سکتے ہیں۔ SET [LOCAL] ایک مخصوص درخواست/لین دین کے لیے۔
: مثال کے طور پر
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
واضح وجوہات کی بناء پر، اگر صرف میموری استعمال کی جاتی ہے، اور ڈسک نہیں، تو استفسار بہت تیزی سے عمل میں آئے گا۔ ایک ہی وقت میں، لوڈ کا حصہ بھی HDD سے ہٹا دیا جاتا ہے.
لیکن آپ کو یہ سمجھنے کی ضرورت ہے کہ بہت ساری میموری مختص کرنا ہمیشہ کام نہیں کرے گا - یہ ہر ایک کے لئے کافی نہیں ہوگا۔
#9: غیر متعلقہ اعدادوشمار
کب کرتا ہے۔
اڈے میں ایک ساتھ بہت کچھ ڈالا گیا، لیکن ان کے پاس اسے بھگانے کا وقت نہیں تھا۔ ANALYZE.
شناخت کرنے کا طریقہ
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10
مسابقتی درخواست کے انتظار میں ایک تالا تھا، یا CPU/hypervisor ہارڈویئر وسائل کافی نہیں تھے۔
شناخت کرنے کا طریقہ
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
سفارشات
ایک بیرونی استعمال کریں۔ نگرانی کے نظام مسدود کرنے یا وسائل کے غیر معمولی استعمال کے لیے سرور۔ ہم پہلے ہی سینکڑوں سرورز کے لیے اس عمل کو منظم کرنے کے اپنے ورژن کے بارے میں بات کر چکے ہیں۔ یہاں и یہاں.