PostgreSQL-ში ინდექსების ყველა მახასიათებლის გამოყენება

Postgres-ის სამყაროში ინდექსები აუცილებელია მონაცემთა ბაზის საცავში ეფექტური ნავიგაციისთვის (ე.წ. "გროვა"). Postgres არ უჭერს მხარს კლასტერირებას და MVCC არქიტექტურა იწვევს თქვენ დასრულებას ერთი და იგივე ტუპლის მრავალი ვერსიით. ამიტომ, ძალიან მნიშვნელოვანია, რომ შეძლოთ ეფექტური ინდექსების შექმნა და შენარჩუნება აპლიკაციების მხარდასაჭერად.

აქ მოცემულია რამდენიმე რჩევა ინდექსების ოპტიმიზაციისა და გამოყენების გასაუმჯობესებლად.

შენიშვნა: ქვემოთ ნაჩვენები მოთხოვნები მუშაობს შეუცვლელად pagila ნიმუშის მონაცემთა ბაზა.

დაფარვის ინდექსების გამოყენება

მოდით შევხედოთ მოთხოვნას ელ.ფოსტის მისამართების ამოღების შესახებ არააქტიური მომხმარებლებისთვის. მაგიდა 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-ხის ინდექსი:

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_index მაგიდიდან 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