پوسٽ گريس جي دنيا ۾، انڊيڪسز ڊيٽابيس اسٽوريج کي موثر طريقي سان نيويگيٽ ڪرڻ لاءِ اهم آهن (هيپ سڏيو ويندو آهي). Postgres ان لاءِ ڪلسترنگ کي سپورٽ نٿو ڪري، ۽ 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 کان بچڻ ۾ مدد ڪري ٿي.
اڄ، ڍڪيل انڊيڪس صرف بي وڻن لاء موجود آهن. بهرحال، هن معاملي ۾ سار سنڀال جي ڪوشش وڌيڪ هوندي.
جزوي انڊيڪس استعمال ڪندي
جزوي انڊيڪس انڊيڪس صرف ٽيبل جي قطارن جو ھڪڙو ذيلي سيٽ. هي توهان کي انڊيڪس سائيز کي بچائڻ ۽ تيزيءَ سان اسڪين ڪرڻ جي اجازت ڏئي ٿو.
اچو ته چئو ته اسان کي ڪيليفورنيا ۾ اسان جي گراهڪن لاء اي ميل پتي جي هڪ فهرست حاصل ڪرڻ جي ضرورت آهي. درخواست هن طرح ٿيندي:
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)انڊيڪس تي به غور نه ڪيو ويو. B-tree index کي ڪا به خبر ناهي ته انفرادي عنصرن جي وجود جي قدرن ۾ اها انڊيڪس ڪري ٿي.
اسان کي هڪ 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 انڊيڪس انفرادي قدرن کي انڊيڪس ڪيل جامع قدرن جي نقشي سازي جي حمايت ڪري ٿو، نتيجي ۾ سوال جي منصوبي جي اڌ کان وڌيڪ قيمت.
نقلي انڊيڪس مان نجات حاصل ڪرڻ
انڊيڪس وقت سان گڏ گڏ ٿيندا آهن، ۽ ڪڏهن ڪڏهن هڪ نئين انڊيڪس ۾ ساڳي تعريف شامل ٿي سگھي ٿي جيئن اڳئين مان هڪ. توھان استعمال ڪري سگھوٿا catalog view انساني-پڙھڻ لائق 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 انڊيڪس ٺاھڻ سمورو آھي. تخليق جي عمل کي تيز ڪرڻ لاء، ڪيترن ئي متوازي ڪارڪنن کي استعمال ڪري سگهجي ٿو. بهرحال، پڪ ڪريو ته اهي ٺاھ جوڙ سيٽنگون صحيح طور تي سيٽ ڪيل آھن:
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ھي انڊيڪس ٺاھڻ جو طريقو عام ھڪڙي کان مختلف آھي جنھن ۾ ان کي ٽيبل کي بند ڪرڻ جي ضرورت نه آھي، ۽ تنھنڪري لکڻ جي عملن کي بلاڪ نٿو ڪري. ٻئي طرف، اهو گهڻو وقت وٺندو آهي ۽ وڌيڪ وسيلن کي استعمال ڪري ٿو.
Postgres مهيا ڪري ٿي انڊيڪس ٺاهڻ لاءِ ڪيترائي لچڪدار آپشنز ۽ ڪنهن خاص ڪيس کي سنڀالڻ جا طريقا، ۽ پڻ مهيا ڪري ٿي ڊيٽابيس کي منظم ڪرڻ جا طريقا جيڪڏهن توهان جي ايپليڪيشن ڌماڪي سان وڌي ٿي. اسان کي اميد آهي ته اهي ٽوٽڪا توهان جي سوالن کي تيز ڪرڻ ۽ توهان جي ڊيٽابيس کي پيماني تي تيار ڪرڻ ۾ مدد ڪندا.
جو ذريعو: www.habr.com