د پوسټګریس نړۍ کې، شاخصونه د ډیټابیس ذخیره کولو اغیزمن نیویګیشن لپاره اړین دي (د "هیپ" په نوم یادیږي). پوسټګریس د دې لپاره د کلستر کولو ملاتړ نه کوي، او د 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 ونو لپاره شتون لري. په هرصورت، پدې حالت کې، د ساتنې هڅې به لوړې وي.
د جزوی شاخصونو کارول
جزوی شاخصونه په جدول کې یوازې د قطارونو فرعي سیټ شاخص کوي. دا د شاخصونو اندازه خوندي کوي او سکینونه ګړندي کوي.
راځئ چې ووایو موږ غواړو په کالیفورنیا کې زموږ د پیرودونکو بریښنالیک پتې لیست ترلاسه کړو. غوښتنه به داسې وي:
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"}';
Nesting Operator @> ګوري چې ښي اړخ د کیڼ اړخ فرعي سیټ دی.
د غوښتنې پلان:
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 لګښت سره د بشپړ هپ سکین غوښتنه کوي.
راځئ وګورو چې ایا منظم B-tree شاخص زموږ سره مرسته کوي:
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-ونې شاخص په شاخص شوي ارزښتونو کې د انفرادي عناصرو شتون څخه خبر نه دی.
موږ د 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 شاخص جوړول همغږي دي. د جوړولو پروسې ګړندي کولو لپاره ، ډیری موازي کارګران کارول کیدی شي. په هرصورت، ډاډ ترلاسه کړئ چې دا د ترتیب کولو اختیارونه په سمه توګه تنظیم شوي دي:
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