Postgres ලෝකයේ, දත්ත සමුදා ආචයනයේ කාර්යක්ෂම සංචාලනය සඳහා දර්ශක අත්යවශ්ය වේ ("ගොඩක්" ලෙස හැඳින්වේ). Postgres එය සඳහා පොකුරු කිරීමට සහය නොදක්වයි, සහ MVCC ගෘහ නිර්මාණ ශිල්පය ඔබට එකම tuple හි බොහෝ අනුවාද සමඟ අවසන් වීමට හේතු වේ. එබැවින්, යෙදුම් සඳහා සහාය වීම සඳහා කාර්යක්ෂම දර්ශක නිර්මාණය කිරීමට සහ පවත්වා ගැනීමට හැකි වීම ඉතා වැදගත් වේ.
දර්ශක භාවිතය ප්‍රශස්ත කිරීම සහ වැඩිදියුණු කිරීම සඳහා උපදෙස් කිහිපයක් මෙන්න.
සටහන: පහත දැක්වෙන විමසුම් වෙනස් නොකළ එකක් මත ක්‍රියා කරයි .
ආවරණ දර්ශක භාවිතා කිරීම
අක්‍රිය පරිශීලකයින් සඳහා විද්‍යුත් තැපැල් ලිපින උපුටා ගැනීමට ඉල්ලීමක් බලමු. වගුව 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". මෙයින් අදහස් කරන්නේ Postgres දර්ශකය පරිලෝකනය කරන බවයි.idx_cust1", ඉන්පසු අනෙකුත් තීරු වල අගයන් කියවීමට වගු ගොඩ සෙවීම දිගටම කරගෙන යන්න (මෙම අවස්ථාවේදී, තීරුව email) විමසුමට අවශ්ය බව.
PostgreSQL 11 හි ආවරණ දර්ශක හඳුන්වා දී ඇත. දර්ශකය තුළම අමතර තීරු එකක් හෝ කිහිපයක් ඇතුළත් කිරීමට ඒවා ඔබට ඉඩ සලසයි - ඒවායේ අගයන් දර්ශක දත්ත ගබඩාවේ ගබඩා කර ඇත.
අපි මෙම විශේෂාංගයෙන් ප්‍රයෝජන ගෙන දර්ශකය තුළ විද්‍යුත් තැපෑල අගය එකතු කළහොත්, Postgres අගය සඳහා වගු ගොඩ සෙවීමට අවශ්ය නොවනු ඇත. 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"}';
කැදලි ක්‍රියාකරු @> දකුණු පැත්ත වම් පැත්තේ උප කුලකයක් දැයි පරීක්ෂා කරයි.
ඉල්ලීම් සැලැස්ම:
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-tree දර්ශකය සුචිගත අගයන්හි තනි මූලද්‍රව්‍යවල පැවැත්ම ගැන නොදනී.
අපට 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මෙම දර්ශක නිර්මාණය කිරීමේ ක්‍රියා පටිපාටිය සාමාන්‍ය ක්‍රමයට වඩා වෙනස් වන්නේ එයට මේසය මත අගුලක් අවශ්ය නොවන අතර එබැවින් ලිවීමේ මෙහෙයුම් අවහිර නොකරයි. අනෙක් අතට, එය වැඩි කාලයක් ගත වන අතර වැඩි සම්පත් පරිභෝජනය කරයි.
Postgres විසින් දර්ශක නිර්මාණය කිරීම සඳහා බොහෝ නම්‍යශීලී බවක් සහ ඕනෑම විශේෂ අවස්ථා විසඳීමට ක්‍රම මෙන්ම, ඔබේ යෙදුම පිපිරෙන ලෙස වර්ධනය වුවහොත් දත්ත සමුදාය කළමනාකරණය කිරීමේ ක්‍රම සපයයි. මෙම ඉඟි ඔබට ඔබේ විමසුම් ඉක්මනින් ලබා ගැනීමට සහ ඔබේ දත්ත සමුදාය පරිමාණයට සූදානම් කිරීමට උපකාරී වනු ඇතැයි අපි බලාපොරොත්තු වෙමු.
මූලාශ්රය: www.habr.com