שימוש בכל התכונות של אינדקסים ב-PostgreSQL

בעולם Postgres, אינדקסים חיוניים לניווט יעיל באחסון של מסד נתונים (המכונה "ערימה"). Postgres לא תומך באשכולות עבורו, וארכיטקטורת MVCC גורמת לך לקבל גרסאות רבות של אותו tuple. לכן, חשוב מאוד להיות מסוגל ליצור ולתחזק אינדקסים יעילים לתמיכה באפליקציות.

להלן מספר טיפים לייעול ושיפור השימוש באינדקסים.

הערה: השאילתות המוצגות להלן פועלות על קובץ ללא שינוי מסד נתונים לדוגמה של 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". זה אומר שפוסטגרס יסרוק את האינדקס "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' אומר לנו שהשאילתה זקוקה כעת רק לאינדקס, מה שעוזר להימנע מכל קלט/פלט דיסק כדי לקרוא את ערימת הטבלה.

אינדקסים מכסים זמינים כרגע רק עבור עצי 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)

אינדקסים של Superset

יכול לקרות שבסופו של דבר תגיעו עם אינדקסים רבים, שאחד מהם אינדקס קבוצה של עמודות שמאנדקסות אינדקסים אחרים. זה עשוי להיות רצוי ואולי לא - ערכת העל עשויה לגרום לסריקות אינדקס בלבד, וזה טוב, אבל היא עשויה לתפוס יותר מדי מקום, או שהשאילתה שמערכת העל נועדה לייעל אינה בשימוש עוד.

אם אתה צריך להפוך את ההגדרה של אינדקסים כאלה לאוטומטי, אתה יכול להתחיל עם 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