Menu

אינדקסים ב-PostgreSQL: ‏CREATE INDEX, סוגים ודוגמאות

CREATE INDEX בונה כברירת מחדל אינדקס B-tree, כך ש-PostgreSQL מוצא שורות בלי לקרוא את כל הטבלה. סוגים אחרים (GIN, ‏GiST, ‏BRIN, ‏hash) מכסים JSONB, מערכים, חיפוש טקסט מלא, טווחים וטבלאות ענקיות.

בדף הזה יש עורכים שאפשר להריץ. לערוך, להריץ ולראות את הפלט מיד.

CREATE INDEX name ON table (column); בונה אינדקס שמאפשר ל-PostgreSQL למצוא שורות מתאימות בלי לקרוא את כל הטבלה. סוג ברירת המחדל הוא B-tree, שמשרת את =, <, >, BETWEEN, IN, IS NULL ו-ORDER BY. בוחרים סוג אחר עם USING: ‏gin, gist, spgist, brin או hash.

השורה הראשונה בתוכנית היא Index Scan using users_email_idx on users: ‏PostgreSQL מחפש את הערך באינדקס וקורא רק את השורה שמתאימה. הסירו את שורת ה-CREATE INDEX והריצו שוב כדי לראות Seq Scan on users, שקורא את כל 10,000 השורות.

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

לבחור סוג אינדקס

סוגהשתמשו בו ל
B-tree (ברירת מחדל)שוויון, טווחים, מיון, LIKE 'prefix%' (עם ה-collation ‏C או text_pattern_ops)
GINערכים שיש בהם הרבה חלקים: jsonb (@>, ?), מערכים (@>, &&), ‏tsvector לחיפוש טקסט מלא
GiSTגיאומטריה, טווחים (&&, חפיפה), חיפוש טקסט מלא, השכן הקרוב ORDER BY ... <->
SP-GiSTנתונים שמתחלקים לאזורים שלא חופפים: נקודות, כתובות IP, קידומות של טקסט
BRINטבלאות ענקיות שהערכים שלהן הולכים לפי הסדר הפיזי, כמו created_at בטבלה שרק מוסיפים לה
Hashרק שוויון; B-tree עושה אותו דבר ויותר, ולכן בוחרים בו לעיתים רחוקות

אינדקס GIN על עמודת jsonb גורם לשאילתות הכלה להשתמש באינדקס:

התוכנית מציגה Bitmap Index Scan על אינדקס ה-GIN, שמזין Bitmap Heap Scan שקורא את השורות שמתאימות. הדף על JSONB מסביר את האופרטורים שלו.

אינדקס על כמה עמודות

אינדקס B-tree מרובה עמודות ממיין לפי העמודה הראשונה, ואז לפי השנייה בתוך כל ערך של הראשונה. הוא משרת שאילתות שמסננות לפי העמודה המובילה, או לפי המובילה והבאות אחריה. סינון לפי העמודה השנייה לבד מרוויח ממנו מעט, אז שימו ראשונה את העמודה שתמיד מסננים לפיה עם =.

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at);

-- served well by the index above
SELECT * FROM orders WHERE customer_id = 7 AND created_at > '2026-01-01';
SELECT * FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;

INCLUDE ‏(PostgreSQL 11+) שומר באינדקס עמודות נוספות בלי למיין לפיהן: CREATE INDEX ON orders (customer_id) INCLUDE (total);. שאילתה שקוראת רק את העמודות האלה יכולה אז לקבל תשובה מהאינדקס בלבד, Index Only Scan.

אינדקסים ייחודיים וחלקיים: לאכוף כלל על חלק מהשורות

CREATE UNIQUE INDEX דוחה ערכים כפולים. אינדקס חלקי מוסיף WHERE ומכניס לאינדקס רק את השורות שמתאימות לו. הוא קטן יותר, והוא יכול להחיל כלל ייחודיות על תת קבוצה של שורות:

שתי השורות מתקבלות כי רק אחת מהן לא מחוקה. שורה פעילה שלישית עם אותו אימייל נכשלת עם duplicate key value violates unique constraint "accounts_email_live". שאילתה משתמשת באינדקס חלקי רק כשה-WHERE שלה גורר את התנאי של האינדקס, כאן WHERE NOT deleted.

אינדקס על ביטוי

אינדקס יכול לשמור את התוצאה של ביטוי. שאילתה חייבת לחזור על אותו ביטוי כדי להשתמש בו, ואינדקס על email לא עוזר ל-WHERE lower(email) = ...:

התוכנית משתמשת ב-users_email_lower_idx כי ה-WHERE חוזר בדיוק על lower(email). הביטוי חייב להיות immutable, ולכן now() או פונקציה שתלויה בהגדרה נדחים.

להציג, לבנות ולמחוק אינדקסים במסד נתונים חי

pg_indexes מציג כל אינדקס עם הפקודה שהייתה יוצרת אותו מחדש:

המפתח הראשי ואילוץ ה-UNIQUE יצרו אינדקסים משלהם, users_pkey ו-users_email_key. ב-psql, ‏\d users מציג את אותה רשימה מתחת לעמודות של הטבלה.

בטבלה שמקבלת כתיבות, בנו ומחקו אינדקסים עם CONCURRENTLY כדי שכתיבות לא ייחסמו. הצורות האלה לא יכולות לרוץ בתוך בלוק טרנזקציה. בנייה במקביל שנכשלה משאירה אינדקס INVALID, שצריך למחוק וליצור מחדש:

CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);
DROP INDEX CONCURRENTLY orders_status_idx;
REINDEX INDEX CONCURRENTLY orders_status_idx;  -- rebuild a bloated index

שאלות נפוצות

האם PostgreSQL יוצר אינדקסים אוטומטית?

רק עבור PRIMARY KEY, ‏UNIQUE ואילוצי exclusion. עמודת מפתח זר לא מקבלת אינדקס, אז הוסיפו אחד בעצמכם כשמצרפים לפיה או מוחקים שורות מהטבלה שמפנים אליה.

למה PostgreSQL לא משתמש באינדקס שלי?

סיבות נפוצות: הטבלה קטנה, כך שקריאה של כולה זולה יותר; השאילתה מחזירה חלק גדול מהשורות; ה-WHERE עוטף את העמודה בפונקציה או בהמרה שאין באינדקס; או שהסטטיסטיקות לא מעודכנות (הריצו ANALYZE). ‏EXPLAIN מראה איזו תוכנית נבחרה.

כמה אינדקסים הם יותר מדי?

אין מספר קבוע. כל אינדקס מאיץ חלק מהקריאות ומאט כל INSERT ואת רוב ה-UPDATE על הטבלה, והוא תופס מקום בדיסק. ‏pg_stat_user_indexes.idx_scan מראה כמה פעמים משתמשים בכל אינדקס, כך שאפשר למחוק את אלה שנשארים באפס.

האם CREATE INDEX נועל את הטבלה?

CREATE INDEX רגיל חוסם INSERT, ‏UPDATE ו-DELETE על הטבלה עד שהוא מסתיים, אבל קריאות ממשיכות. ‏CREATE INDEX CONCURRENTLY מאפשר כתיבות בזמן הבנייה, לוקח יותר זמן ולא יכול לרוץ בתוך בלוק טרנזקציה.

איור של שפות התכנות ב-Coddy

ללמוד תכנות עם Coddy

להתחיל