በፖስትግሬስ ዓለም፣ የመረጃ ቋቱን (ዳታቤዙን) በብቃት ለማሰስ ኢንዴክሶች ወሳኝ ናቸው። 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 የሽፋን ኢንዴክሶችን አስተዋውቋል። በመረጃ ጠቋሚው ውስጥ አንድ ወይም ከዚያ በላይ ተጨማሪ አምዶችን እንዲያካትቱ ያስችሉዎታል - እሴቶቻቸው በመረጃ ጠቋሚ ማከማቻ ውስጥ ይቀመጣሉ።
ይህንን ባህሪ ከተጠቀምን እና የኢሜል እሴቱን በመረጃ ጠቋሚው ውስጥ ከጨመርን ፖስትግሬስ ለዋጋው የጠረጴዛ ክምር ውስጥ መፈለግ አያስፈልገውም። 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.
ባለብዙ እሴት ኢንዴክሶችን በመጠቀም
አንዳንድ ጠቋሚዎች መጠቆሚያ የሚያስፈልጋቸው አምዶች scalar የውሂብ አይነት ላይኖራቸው ይችላል። እንደ አምድ ዓይነቶች 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)መረጃ ጠቋሚው እንኳን ግምት ውስጥ አልገባም. የቢ-ዛፍ መረጃ ጠቋሚ በሚያመለክታቸው እሴቶች ውስጥ ስለ ግለሰባዊ አካላት መኖር ምንም ሀሳብ የለውም።
የጂአይኤን መረጃ ጠቋሚ እንፈልጋለን።
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)የጂአይኤን መረጃ ጠቋሚ የነጠላ እሴቶችን ወደ የተጠቆሙ ጥምር እሴቶች ማቀናጀትን ይደግፋል፣ ይህም የጥያቄ ዕቅዱ ከግማሽ በላይ ወጪን ያስከትላል።
የተባዙ ኢንዴክሶችን ማስወገድ
ኢንዴክሶች በጊዜ ሂደት ይከማቻሉ, እና አንዳንድ ጊዜ አዲስ ኢንዴክስ ከቀዳሚዎቹ ውስጥ አንዱን ተመሳሳይ ፍቺ ሊይዝ ይችላል. በሰው ሊነበብ የሚችል የ 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 ለማንኛውም ልዩ ጉዳዮች ኢንዴክሶችን እና መፍትሄዎችን ለመፍጠር ብዙ ተለዋዋጭ አማራጮችን ይሰጣል እንዲሁም መተግበሪያዎ በፈንጂ እያደገ ከሆነ የመረጃ ቋቱን የሚያስተዳድሩባቸውን መንገዶች ያቀርባል። እነዚህ ምክሮች ጥያቄዎችዎን ፈጣን ለማድረግ እና የውሂብ ጎታዎን ለመለካት ዝግጁ እንዲሆኑ እንደሚረዱዎት ተስፋ እናደርጋለን።
ምንጭ: hab.com