پوسٹگریس کی دنیا میں، ڈیٹا بیس اسٹوریج (جسے ہیپ کہا جاتا ہے) کو مؤثر طریقے سے نیویگیٹ کرنے کے لیے اشاریہ جات اہم ہیں۔ پوسٹگریس اس کے لیے کلسٹرنگ کی حمایت نہیں کرتا ہے، اور MVCC فن تعمیر آپ کو ایک ہی ٹیپل کے بہت سے ورژن کے ساتھ ختم کرنے کا سبب بنتا ہے۔ لہذا، ایپلی کیشنز کو سپورٹ کرنے کے لیے موثر انڈیکس بنانے اور برقرار رکھنے کے قابل ہونا بہت ضروری ہے۔
میں اشاریہ جات کے استعمال کو بہتر بنانے اور بہتر بنانے کے لیے کچھ نکات آپ کی توجہ میں لاتا ہوں۔
نوٹ: ذیل میں دکھائے گئے سوالات غیر ترمیم شدہ پر کام کرتے ہیں۔ .
کورنگ اشاریہ جات کا استعمال
آئیے غیر فعال صارفین کے ای میل پتوں کو بازیافت کرنے کی درخواست پر غور کریں۔ میز میں customer ایک کالم ہے active، اور استفسار آسان ہے:
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows)
استفسار مکمل ٹیبل اسکین ترتیب کو کہتے ہیں۔ customer. آئیے کالم پر ایک انڈیکس بنائیں active:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_cust1 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
مدد ملی، بعد میں اسکین "میں بدل گیاindex scan" اس کا مطلب یہ ہے کہ پوسٹگریس انڈیکس کو کرال کرے گا "idx_cust1"، اور پھر دوسرے کالموں کی قدروں کو پڑھنے کے لیے ٹیبل ہیپ کو تلاش کرنا جاری رکھیں (اس صورت میں، کالم email) جس کی درخواست کی ضرورت ہے۔
PostgreSQL 11 نے کورنگ انڈیکس متعارف کرایا۔ وہ آپ کو انڈیکس میں ایک یا زیادہ اضافی کالم شامل کرنے کی اجازت دیتے ہیں - ان کی قدریں انڈیکس ڈیٹا اسٹور میں محفوظ ہوتی ہیں۔
اگر ہم اس خصوصیت کو استعمال کرتے ہیں اور انڈیکس کے اندر ای میل ویلیو شامل کرتے ہیں، تو پوسٹگریس کو قدر کے لیے ٹیبل ہیپ میں دیکھنے کی ضرورت نہیں ہوگی۔ email. آئیے دیکھتے ہیں کہ آیا یہ کام کرتا ہے:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using idx_cust2 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
«Index Only Scan" ہمیں بتاتا ہے کہ استفسار کو اب صرف انڈیکس کی ضرورت ہے، جو ٹیبل کے ڈھیر کو پڑھنے کے لیے تمام ڈسک I/O سے بچنے میں مدد کرتا ہے۔
آج، کورنگ انڈیکس صرف B-trees کے لیے دستیاب ہیں۔ تاہم، اس معاملے میں دیکھ بھال کی کوششیں زیادہ ہوں گی۔
جزوی اشاریہ جات کا استعمال
جزوی اشاریہ جات ٹیبل کی قطاروں کے صرف ایک ذیلی سیٹ کو انڈیکس کرتے ہیں۔ یہ آپ کو انڈیکس کا سائز بچانے اور تیزی سے اسکین کرنے کی اجازت دیتا ہے۔
ہم کہتے ہیں کہ ہمیں کیلیفورنیا میں اپنے صارفین کے لیے ای میل پتوں کی فہرست حاصل کرنے کی ضرورت ہے۔ درخواست اس طرح ہوگی:
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=15.65..32.22 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=15.54..15.54 rows=9 width=4)
-> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4)
Filter: (district = 'California'::text)
(6 rows)باقاعدہ اشاریہ جات ہمیں کیا دیں گے:
pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
---------------------------------------------------------------------------------------
Hash Join (cost=12.98..29.55 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.87..12.87 rows=9 width=4)
-> Bitmap Heap Scan on address a (cost=4.34..12.87 rows=9 width=4)
Recheck Cond: (district = 'California'::text)
-> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0)
Index Cond: (district = 'California'::text)
(8 rows)
سکین address انڈیکس سکیننگ کی طرف سے تبدیل کر دیا گیا ہے idx_address1، اور پھر ڈھیر کو اسکین کیا گیا۔ address.
چونکہ یہ ایک متواتر استفسار ہے اور اسے بہتر بنانے کی ضرورت ہے، اس لیے ہم ایک جزوی اشاریہ استعمال کر سکتے ہیں، جو صرف ان قطاروں کو ان پتے کے ساتھ ترتیب دیتا ہے جن میں علاقہ ‘California’:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
------------------------------------------------------------------------------------------------
Hash Join (cost=12.38..28.96 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.27..12.27 rows=9 width=4)
-> Index Only Scan using idx_address2 on address a (cost=0.14..12.27 rows=9 width=4)
(5 rows)
اب درخواست صرف پڑھتی ہے۔ idx_address2 اور میز کو ہاتھ نہیں لگاتا address.
ملٹی ویلیو انڈیکس کا استعمال
کچھ کالم جن کو انڈیکس کرنے کی ضرورت ہے ان میں اسکیلر ڈیٹا کی قسم نہیں ہوسکتی ہے۔ کالم کی اقسام جیسے jsonb, arrays и tsvector مرکب یا متعدد معنی پر مشتمل ہے۔ اگر آپ کو ایسے کالموں کو انڈیکس کرنے کی ضرورت ہے، تو آپ کو عام طور پر ان کالموں میں تمام انفرادی اقدار کو تلاش کرنا پڑتا ہے۔
آئیے ناکام فلموں کے کلپس پر مشتمل تمام فلموں کے عنوانات تلاش کرنے کی کوشش کرتے ہیں۔ میز میں film ایک ٹیکسٹ کالم کہا جاتا ہے special_features. اگر کسی فلم میں یہ "خصوصی خاصیت" ہے، تو کالم میں متن کی صف کی شکل میں ایک عنصر ہوتا ہے۔ Behind The Scenes. ایسی تمام فلموں کو تلاش کرنے کے لیے، ہمیں "پردے کے پیچھے" والی تمام قطاروں کو منتخب کرنے کی ضرورت ہے۔ کسی بھی صف اقدار special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
کنٹینمنٹ آپریٹر @> چیک کرتا ہے کہ آیا دائیں طرف بائیں طرف کا سب سیٹ ہے۔
سوال کا منصوبہ:
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)جو 67 کی لاگت کے ساتھ مکمل ہیپ اسکین کی درخواست کرتا ہے۔
آئیے دیکھتے ہیں کہ کیا ایک باقاعدہ بی ٹری انڈیکس ہماری مدد کرے گا:
pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)انڈیکس پر بھی غور نہیں کیا گیا۔ بی ٹری انڈیکس کو ان اقدار میں انفرادی عناصر کے وجود کے بارے میں کوئی اندازہ نہیں ہے جو یہ انڈیکس کرتا ہے۔
ہمیں ایک GIN انڈیکس کی ضرورت ہے۔
pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on film (cost=8.04..23.58 rows=5 width=15)
Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
-> Bitmap Index Scan on idx_film2 (cost=0.00..8.04 rows=5 width=0)
Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)GIN انڈیکس انفرادی اقدار کو انڈیکس شدہ جامع اقدار کے ساتھ نقشہ بنانے میں معاونت کرتا ہے، جس کے نتیجے میں استفسار کے منصوبے کی نصف سے زیادہ لاگت آتی ہے۔
ڈپلیکیٹ اشاریہ جات سے چھٹکارا حاصل کرنا
اشاریہ جات وقت کے ساتھ ساتھ جمع ہوتے رہتے ہیں، اور بعض اوقات ایک نئے اشاریہ میں وہی تعریف شامل ہو سکتی ہے جو گزشتہ میں سے ایک ہے۔ آپ انسانی پڑھنے کے قابل SQL انڈیکس کی تعریفیں حاصل کرنے کے لیے کیٹلاگ کا منظر استعمال کر سکتے ہیں۔ pg_indexes. آپ آسانی سے وہی تعریفیں بھی تلاش کر سکتے ہیں:
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-# FROM pg_indexes
pagila-# GROUP BY defn
pagila-# HAVING count(*) > 1;
indexes | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX ON public.payment_p2017_01 USING btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX ON public.payment_p2017_02 USING btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX ON public.payment_p2017_03 USING btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX ON public.payment_p2017_04 USING btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX ON public.payment_p2017_05 USING btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX ON public.payment_p2017_06 USING btree (customer_id
(6 rows)
سپر سیٹ اشاریہ جات
ایسا ہو سکتا ہے کہ آپ بہت سے اشاریہ جات جمع کر لیں، جن میں سے ایک کالموں کے سپر سیٹ کو انڈیکس کرتا ہے جو کہ دوسرے اشاریہ جات کو ترتیب دیتے ہیں۔ یہ مطلوبہ بھی ہو سکتا ہے یا نہیں بھی ہو سکتا ہے - سپر سیٹ کے نتیجے میں صرف انڈیکس اسکین ہو سکتا ہے، جو اچھا ہے، لیکن اس میں بہت زیادہ جگہ لگ سکتی ہے، یا جس سوال کو سپر سیٹ کو بہتر بنانا تھا وہ اب استعمال میں نہیں ہے۔
اگر آپ کو اس طرح کے اشاریہ جات کی تعریف خودکار کرنے کی ضرورت ہے، تو آپ شروع کر سکتے ہیں۔ میز سے pg_catalog.
غیر استعمال شدہ اشاریہ جات
جیسے جیسے ڈیٹا بیس استعمال کرنے والی ایپلیکیشنز تیار ہوتی ہیں، اسی طرح ان کے استعمال کردہ سوالات بھی تیار ہوتے ہیں۔ پہلے شامل کیے گئے اشاریہ جات اب کسی بھی سوالات کے لیے استعمال نہیں کیے جا سکتے ہیں۔ ہر بار جب انڈیکس کو اسکین کیا جاتا ہے، اسے شماریات مینیجر اور سسٹم کیٹلاگ کے منظر میں نوٹ کرتا ہے۔ pg_stat_user_indexes آپ قدر دیکھ سکتے ہیں۔ idx_scanجو کہ ایک مجموعی کاؤنٹر ہے۔ اس قدر کو ایک مدت کے دوران (ایک مہینہ کہتے ہیں) کو ٹریک کرنے سے اس بات کا اچھا اندازہ ہو جائے گا کہ کون سے اشاریہ استعمال نہیں ہو رہے ہیں اور انہیں چھوڑا جا سکتا ہے۔
یہاں اسکیما میں موجود تمام اشاریہ جات کے موجودہ اسکین شمار حاصل کرنے کی درخواست ہے۔ ‘public’:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM pg_catalog.pg_stat_user_indexes
pagila-# WHERE schemaname = 'public'
pagila-# LIMIT 10;
relname | indexrelname | idx_scan
---------------+--------------------+----------
customer | customer_pkey | 32093
actor | actor_pkey | 5462
address | address_pkey | 660
category | category_pkey | 1000
city | city_pkey | 609
country | country_pkey | 604
film_actor | film_actor_pkey | 0
film_category | film_category_pkey | 0
film | film_pkey | 11043
inventory | inventory_pkey | 16048
(10 rows)کم تالے کے ساتھ اشاریہ جات کو دوبارہ بنانا
اشاریہ جات کو اکثر دوبارہ بنانے کی ضرورت ہوتی ہے، مثال کے طور پر جب وہ پھول جاتے ہیں، اور دوبارہ تخلیق سکیننگ کو تیز کر سکتی ہے۔ اشاریہ جات بھی خراب ہو سکتے ہیں۔ انڈیکس کے پیرامیٹرز کو تبدیل کرنے کے لیے اس کی دوبارہ تخلیق کی بھی ضرورت پڑ سکتی ہے۔
متوازی انڈیکس تخلیق کو فعال کریں۔
PostgreSQL 11 میں، B-Tree index کی تخلیق ایک ساتھ ہے۔ تخلیق کے عمل کو تیز کرنے کے لیے، کئی متوازی کارکنوں کو استعمال کیا جا سکتا ہے۔ تاہم، یقینی بنائیں کہ یہ کنفیگریشن سیٹنگز درست طریقے سے سیٹ ہیں:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;پہلے سے طے شدہ قدریں بہت چھوٹی ہیں۔ مثالی طور پر، یہ تعداد پروسیسر کور کی تعداد کے ساتھ ساتھ بڑھنی چاہیے۔ میں مزید پڑھیں .
پس منظر انڈیکس کی تخلیق
آپ آپشن کا استعمال کرتے ہوئے پس منظر میں انڈیکس بنا سکتے ہیں۔ CONCURRENTLY ٹیمیں CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXانڈیکس بنانے کا یہ طریقہ کار معمول سے مختلف ہے کہ اس میں ٹیبل کو لاک کرنے کی ضرورت نہیں ہے، اور اس وجہ سے تحریری کارروائیوں کو مسدود نہیں کرتا ہے۔ دوسری طرف، یہ زیادہ وقت لگتا ہے اور زیادہ وسائل استعمال کرتا ہے.
پوسٹگریس انڈیکس بنانے اور کسی خاص کیس کے حل کے لیے بہت سے لچکدار اختیارات فراہم کرتا ہے، اور اگر آپ کی ایپلی کیشن دھماکہ خیز طریقے سے بڑھتی ہے تو ڈیٹا بیس کو منظم کرنے کے طریقے بھی فراہم کرتی ہے۔ ہمیں امید ہے کہ یہ تجاویز آپ کے سوالات کو تیز کرنے اور آپ کے ڈیٹا بیس کو پیمانے کے لیے تیار کرنے میں آپ کی مدد کریں گی۔
ماخذ: www.habr.com