Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
PAGE 10
Глава 11. Індекси
Індекс дозволяє серверу бази підвищити швидкість пошуку даних. Слід однак зауважити, що індекси також додають надлишкові затрати, тому їх ефективність потребує дослідження.
Нехай маємо таку таблицю:
CREATE TABLE test1(id integer, content varchar);
і застосовуємо запит:
SELECT content FROM test1 WHERE id = constant;
Без попереднього приготування системі довелося б проглянути повну таблицю test1 рядок за рядком, щоб знайти усі відповідні входи. Якщо є багато рядків в test і тільки декілька рядків (можливо нуль або один), які повернув би такий запит, це ясно неефективний метод. Але якщо система була інструктована, щоб застосувати індекс на id стовпці, він може використовувати ефективніший метод для розташування відповідності рядів. Наприклад, цьому, можливо, тільки довелося б піти декілька рівнів вглиб в дереві пошуку.
Подібний підхід використовується у більшості книгах наукової літератури: терміни і поняття, які часто шукають читачі, сортовані в алфавітному порядку у кінці книги. Зацікавлений читач може знайти індекс відносно швидко перейти до відповідної сторінки, замість необхідності читати повну книгу, щоб знайти потрібний матеріал. Тільки якщо завдання автора – знайти потрібні елементи, то програмісту бази даних необхідно передбачати корисні індекси.
Наступна команда може бути використана для створення індекса за id стовпцем:
CREATE INDEX test1_id_index ON test1 (id);
Ім’я індекса test1_id_index може бути вибране довільно, але слід вибрати такий індекс, щоб можна згадати для чого він застосовується.
Щоб видалити індекс, використовують команду DROP INDEX. Індекси можуть бути додані і видалені із таблиць у будь-який час.
Як тільки індекс стане створеним, ніякого подальшого втручання не потрібно, система обновить індекс, коли таблиця буде змінена, і буде використовувати індекс у запитах, коли вирішить що так буде ефективніше, ніж послідовне табличне сканування. Але вам, можливо, довелося б виконувати команду ANALYZE регулярно, щоб модифікувати статистику, щоб дозволити планувальникові запиту прийняти завчені рішення. Подивіться Главу 14 для інформації про те, як з’ясувати чи індекс використаний, коли і чому планувальник, можливо, захотів би не використати індекс.
Індекси можуть також принести вигоду командам UPDATE і DELETE з пошуковими умовами. Індекси можуть, окрім того, бути використані в пошуках приєднання. Тут, індекс визначений на стовпці, який є частиною умови приєднання, може також значно збільшити швидкість запиту з приєднаннями.
Створення індексу для великої таблиці може зайняти довгий час. За замовчуванням PostgreSQL дозволяє читати таблиці паралельно із створенням індексів, але записи (INSERT, UPDATE, DELETE) є блоковані поки створення індексів не закінчиться. У виробничих середовищах це часто неприйнятно. Взагалі можливо дозволити записам з’являтися одночасно із створенням індексів, але є декілька застережень (для більш конкретної інформації слід переглянути Будівельні Індекси Одночасно – Building Indexes Concurrently).
Після того, як індекс створений, системі доведеться зберігати його, синхронізуваним із таблицею. Це додає надлишковість до операцій маніпуляції даних. Тому індекси, які рідко або ніколи не використовуються в запитах, повинні бути видаленими.
Порядкові типи. PostgreSQL забезпечує декілька порядкових типів індексів: B-tree, Хеш, GiST і GIN. Кожен порядковий тип використовує різний алгоритм, який найкраще підходить до різних видів запитів. За замовчуванням команда CREATE INDEX створює B-tree індекси, які відповідають найзагальнішим ситуаціям. B-trees можуть обробити тотожність і збудувати запити на даних, які можуть бути посортовані в певному порядку. Зокрема, планувальник запиту PostgreSQL розглядає використання B-tree індексу кожного разу при залученні до порівняння індексного стовпця, коли використовується один із таких операторів: <, <=, =,
>=, >.
Конструкції еквівалентні до комбінацій цих операторів, такі наприклад як BETWEEN і IN, теж можуть здійснюватися з B-tree індексним пошуком. Також умови IS NULL або IS NOT NULL теж можуть бути використані з B-tree індексом. Оптимізатор може також використовувати B-tree індекс для запитів, що включають оператори LIKE і ~ якщо шаблон є константою і стоїть на якорі на початок рядка, наприклад, col LIKE ’foo%’ або col ~ ’^foo’, але не col LIKE ’%bar’. Проте, якщо ваша база даних не користується регіоном C, вам треба буде створити індекс з класом спеціального оператора, щоб підтримувати індексацію з запитами типу зразок-відповідність. Також можливо користуватися B-tree індексами ILIKE і ~*, але тільки якщо шаблон розпочинається з неазбучних символів, тобто, символи, в яких немає верхнього або нижнього регістру.
Індекси Хеш можуть обробляти тільки прості порівняння тотожності. Планувальник запиту розглядатиме використання хеш індекс кожного разу, коли індексований стовпець залучається до порівняння, користуючись = оператором. Для створення хеш індекса використана команда:
CREATE INDEX name ON table USING hash (column);
Зауважимо, що тепер операції хеш індексу не WAL-logged (Wal - задокументовані), тому хеш індексам, можливо, треба б бути перебудованими з REINDEX після аварійної відмови бази даних. Тому, використання хеш індексу у цьому випадку є доволі небажаним.
Індекси GiST по суті не є одним видом індексу, але швидше інфраструктурою, в межах якої може здійснюватися багато інших стратегій індексації. Відповідно, специфічні оператори, з якими GiST індекс може бути використаний змінюються залежно від стратегії (клас оператора) індексації. Як приклад стандартна версія PostgreSQL включає класи оператора GiST для деякої двовимірної кількості типів геометричних даних, які підтримують індексовані запити, користуючись такими операторами:
<<
&<
&>
>>
<<|
&<|
|&>
|>>
@>
<@
~=
&&
Інші класи оператора GiST доступні у contrib колекції або як окремі проекти.
Індекси GIN - перевернені індекси, які можуть обробити значення, які містять більш ніж однин ключ, наприклад масиви. Подібно до GiST, GIN може підтримувати багато інших визначених користувачем стратегій індексації окремих операторів з якими індекс GIN може бути використаний, що змінюються залежно від стратегії індексації. Як приклад стандартна версія PostgreSQL включає класи оператора GIN одновимірних масивів, які підтримують індексовані запити, користуючись такими операторами:
<@
@>
=
&&
Інші класи оператора GIN доступні у contrib колекції або як окремі проекти.
Багатоколонні індекси. Індекс може бути визначений більше ніж на одному стовпці таблиці. Наприклад, якщо маємо таблицю такої форми:
CREATE TABLE test2(major int, minor int, name varchar);
і часто використовуються запити:
SELECT name FROM test2 WHERE major = constant AND minor = constant;
Потім це може бути доступне для визначення індексу колонок major і minor разом, наприклад:
CREATE INDEX test2_mm_idx ON test2 (major, minor);
Зараз, тільки індекси B-tree, GiST і GIN підтримують багатоколонні індекси. Аж до 32 стовпців можуть бути вказані. (Ця межа може бути змінена під час складання PostgreSQL, переглянемо файл pg_config_manual.h.)
Багатоколонний B-tree індекс може бути використаний з умовами запиту, які включають будь-яку підмножину стовпців індексу, але індекс є більш ефективним, коли є обмеження на лідируючих (крайніх лівих) стовпцях.
Точним правилом є те, що обмеження тотожності на лідируючих стовпцях, плюс будь-які обмеження нерівності на першому стовпці, який не має обмеження тотожності, будуть використані, щоб обмежити порцію індексу, що сканується. Обмеження на стовпцях праворуч від цих стовпців відмічені в індексі, тож вони зберігають відвідування таблиці коректно, але вони не скорочують порцію індексу, який доведеться проглянути. Нехай, наприклад, дано, що індекс на (а, b, c) і умова запиту, WHERE a = 5 AND b >= 42 AND c < 77, то індекс довелося б проглянути від першого входу а = 5 і b = 42 через останній вхід а = 5. Індексові входи з c >= 77 були би пропущені, але їх все ще довелося б просканувати. Цей індекс зміг би, в принципі, бути використаний для запитів, які мають обмеження на b та/чи c без обмежень на а, але повний індекс всетаки довелося б проглянути, тож у більшості випадків планувальник повинен віддати перевагу послідовному табличному скануванню а не користуванням індексами.
Багатоколонний індекс GiST може бути використаний з умовами запиту, які включають будь-яку підмножину стовпців індексу. Умови на додаткових стовпцях обмежують входи, повернені індексом, але умовою на першому стовпці - найголовніша для визначення, скільки з індексу має бути проскановано. Індекс GiST буде відносно неефективний, якщо його перший стовпець має тільки декілька чітких значень, навіть якщо є багато чітких значень в додаткових стовпцях.
Багатоколонний індекс GIN може бути використаний з умовами запиту, які включають будь-яку підмножину стовпців індексу. На відміну від B-tree або GiST, ефективність пошуку індексу та ж, незважаючи на якому стовпці індексу використовуються умови запиту.
Звичайно, що кожен стовпець має бути використаний з операторами відповідно до типу індексу; варіанти, які включають інші оператори не розглядатимуться.
Багатоколонні індекси мають бути використані економно. У більшості ситуаціях, індекс на єдиному стовпці є достатній і заощаджує місце і час. Більш ніж з трьома стовпцями індекси навряд чи корисні хіба що використання таблиці є екстимально стилізованим. (Секція 11.5 надає деякі обговорення про достоїнства різних індексних конфігурацій.)
Індекси і ORDER BY. На додаток до простого знаходження послідовностей запитом, індекс може повернути їх у специфічно посортованому вигляді. Це дозволяє специфікації ORDER BY запиту бути користною без додаткового кроку сортування. Із зараз підтримуваних типів індексів тільки B-tree може видавати сортований вивід, інші індексні типи повертають відповідність рядів у невизначеному, залежному від виконання порядку.
Планувальник буде розгядати задовільним специфікацію ORDER BY і сканування з використання індексів, і сканування в фізичному порядку з подальшим сортуванням .Для запиту, який вимагає сканування великої частини таблиці, явний вид, ймовірно швидший, чим користування індексом, тому що він вимагає менше I/O диску через слідування послідовного шаблону доступу. Індекси більш корисні коли потрібно, щоб були вибрані тільки декілька послідовностей. Важливим є окремий випадок – ORDER BY у комбінації з LIMIT n: явне сортування змушене буде обробити усі дані, щоб ідентифікувати перші n рядів, але якщо є індекс, який відповідає ORDER BY, то перші n рядів взагалі можуть бути знайдені безпосередньо без сканування решти.
За замовчанням, B-tree індекси зберігають свої входи в висхідному порядку з нулем вкінці. Це означає, що сканування в прямому порядку індексу на стовпці x дає вивід, що задовільняє ORDER BY x (або більше багатослівно ORDER BY x ASC NULLS LAST). Індекс може також бути просканований в зворотньому порядку, з задовільним для ORDER BY x DESC (або більше багатослівно, ORDER BY x DESC NULLS FIRST, оскільки NULLS
FIRST - є значення по умовчанню для ORDER BY DESC).
Можна регулювати впорядкування за B-tree індексом по включенню опції ASC, DESC, NULLS FIRST та/або NULLS LAST, створюючи індекс; наприклад:
CREATE INDEX test2_info_nulls_low ON test2 (info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
Індекс у висхідному порядку, з нулями спочатку може задовольнити будь-якому ORDER BY x ASC NULLS FIRST або
ORDER BY x DESC NULLS LAST, залежно від напряму сканування.
Можна поставити питання, чому забезпечуються всі чотири опції, коли дві опції разом з можливістю зворотного сканування покривають усі варіанти ORDER BY. У одноколонних індексах опції вони дійсно надмірні, але у багатоколонних індексах вони можуть бути корисними. Наприклад двоколонний індекс на (x, y) може задовольнити ORDER BY x, y, якщо відбувається сканування вперед, або ORDER BY x DESC, y DESC, якщо – назад. Але може бути що програмі треба буде використати ORDER BY x ASC, y DESC. Немає ніякої можливості отримати такий порядок, використовуючи явний індекс, але це можливо, якщо індекс визначений, як (x ASC, y DESC) або (x DESC, y ASC).
Очевидно, що індекси із впорядкуванням виду, незаданого за замовчуванням, є досись специфічними, але іноді вони можуть значно забезпечити приріст швидкості виконання для певних запитів. Наявність підтримки такого індексу залежить від того, як часто відбувається користування запитами, які вимагають спеціального виду впорядкування.
Комбінування множиних індексів. Одиничне сканування індексу може тільки використовувати умови запитів, які використовують колонки індексу з операторами його операторного класу і є з’єднані з допомогою AND. Якщо для прикладу дано індекс на (a, b), умова запиту типу WHERE a = 5 AND b = 6, то можна використовувати індекс, але запит типу WHERE a = 5 OR b = 6 не може безпосередньо використовувати індекс.
Зауважимо, що PostgreSQL має здатність комбінувати множинні індекси (у тому числі множинні використання одного і тогож індексу) для обробки випадків, які не можуть здійснювати одиничні індексні скануванняі. Система може зформувати умови AND і OR через декілька індексних сканувань. Наприклад, запит типу WHERE x = 42 OR x = 47 OR x = 53 OR x = 99 міг би бути розбитим на чотири окремих сканування індексу на x, кожен з яких користувався б однією з умов запиту. Результати цих сканувань потім ORено докупи для виробництва результату. Інший приклад: якщо ми маємо окремі індекси на x і y, одна можлива реалізація запиту типу, WHERE x = 5 AND y = 6 - користування кожним індексом з відповідною умовою запиту і потім AND разом індексні результати, щоб визначити ряди результату.
Щоб комбінувати множинні індекси, система сканує кожен потрібний індекс і готує бітовий масив(бітмап) в пам'яті даючи розташування табличних рядів, які записані так, як, співпадають, до умови індексу. Бітові масиви - потім ANDeні і OReні разом, як вимагає запит. Нарешті, фактичні табличні ряди є перевіреними і поверненими . Табличні ряди перевіряються у фізичному порядку, тому що це - те, як батовий масив розміщений;
це означає, що будь-яке впорядкування оригінальних індексів є втраченим, тож окремий крок сортування буде потрібний, якщо запит має умову ORDER BY. Через це, і тому що кожне додаткове індексне сканування додає лишній час, планувальник іноді хотітиме користуватися простим індексним скануванням хоча додаткові індекси доступні, і могли б також бути використані.
Всюди крім найпростіших додатків, є різні комбінації індексів, які, можливо, були б корисні і розробник бази даних повинен зробити заборони на зміну, щоб вирішити, які індекси реалізувати. Іноді багатоколонні індекси кращі, але іноді краще створити окремі індекси і покладатися на індексно-комбінаційну функцію. Наприклад, якщо ваше об’єм робіт включає змішування запитів, що іноді включіть тільки стовпець x, іноді тільки стовпець y, і іноді обидва стовпці, ви, можливо, вибрали бстворення двох окремих індекси на x і y, покладаючись на індексну комбінацію, щоб обробляти запити що включаєть обидва стовпці. Ви змогли також створити багатоколонний індекс (x, y). Цей індекс зазвичай був би
ефективніше, ніж індексна комбінація для запитів, що включають обидва стовпці, але як обговорювалось, це було б майже даремно для запитів, що включають тільки y, тож це не має бути одним індексом.
Комбінація на y багатоколонного індексу і окремого індексу служила б добре. Для запитів, що включаєть лише x, багатоколонний індекс зміг би бути використаним, хоча це було б надлишковим і отже повільнішим, ніж індекс тільки на x. Остання альтернатива –створити усі три індекси, але це є доцільно лише тоді, якщо таблиця обшукується частіше, ніж модифікується, і всі три типи запитів є однаково часто використовуваними. Якщо один із видів запиту є менш використовуваним, ніж інші, то ймовірно доцільніше створити тільки два загальні запити.
Унікальні індекси. Індекси можуть також бути використаними для надання унікальності значенню стовпця, або унікальності значення комбінації більше, ніж одного стовпця.
CREATE UNIQUE INDEX name ON table (column [, ...]);
Зараз, тільки B-tree індекси можуть бути оголошеним як унікум. Коли індекс оголошений як унікум, то не дозволяється кілька табличних рядків з еквівалентними індексними значеннями.
Нульові значення не є еквівалентними. Багатоколонний унікальний індекс тільки відкине випадки, де всі індексовані стовпці є еквівалентними в кількох рядах.
PostgreSQL автоматично створює унікальний індекс, коли унікальне обмеження або первинний ключ визначені для таблиці. Індекс покриває стовпці, які використовують первинний ключ або унікальне обмеження (багатоколонний індекс, якщо можливий), і - механізм, який задає обмеження.
Відзначимо, що бажаний шлях додання унікального обмеження до таблиці, є ALTER TABLE ... ADD CONSTRAINT. Використання індексів, для надавання унікальних обмеженнь може розглядатися як деталь, до якої не має бути безпосереднього доступу. Він повинен, проте, знати, що немає ніякої необхідності вручну створювати індекси на унікальних стовпцях; така дія тільки дублювала б автоматично-створений індекс.
Індекси на виразах. Порядковому стовпцю не обов’язково бути тільки стовпцем основної таблиці, але може бути функцією або скалярним виразом, утвореним від одного або більше стовпців таблиці. Ця особливість корисна, щоб швидко отримати доступ до таблиць заснованих на результатах обчислень.
Наприклад, загальним методом для порівняння нечутливого до регістру є така функція:
SELECT * FROM test1 WHERE lower(col1) = ’value’;
Цей запит може користуватися індексом, якщо той був визначений на результаті функції lower(col1):
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
Якщо б ми мали оголосити цей індекс UNIQUE, це запобігло б створенню рядів, значення col1 яких відрізняються тільки регістром, і не рядках, чї значення col1 фактично ідентичні. Отже, індекси на виразах можуть бути використаними, щоб надати обмежень, які не визначені як прості унікальні обмеження.
Інший приклад показує, що запити такого типу є частими:
SELECT * FROM people WHERE (first_name || ’ ’ || last_name) = ’John Smith’;
тому варто створити індекс типу:
CREATE INDEX people_names ON people ((first_name || ’ ’ || last_name));
Синтаксис команди CREATE INDEX зазвичай вимагає запису круглих дужок навколо виразу індексу, як показано в другому прикладі. Круглі дужки можуть бути опущені, коли вираження є тільки зверненням до функції, як у першому прикладі.
Індексні вирази є відносно дорогими для підтримки, тому що отримані вирази мають бути вирахованими для кожного ряду при встаці і кожного разу при модифікації. Проте, індексні вирази не обчислюються повторно під час індексного пошуку, тому що вони вже зберігаються в індексі. В обох показаних вище прикладах система бачить запит, якщо лише WHERE indexedcolumn = ’constant’, тож швидкість пошуку еквівалентна до будь-якого іншого простого порядкового запиту. Отже, індекси на виразах корисні коли швидкість пошуку є важливішою, ніж швидкість вставки і модифікації.
Часткові індекси. Частковий індекс формується над підмножиною таблиці; підмножину визначає умовний вираз (названий предикатою часткового індексу). Індекс містить входи тільки для тих табличних рядів, які задовільняють предикат. Часткові індекси, – це специфічна функція, але є декілька ситуацій у яких вони корисні.
Одною із головних причин використання часткових індексів є уникнення індексації загальних значень. Оскільки пошук запиту для загального значення (той, що звітує про більш ніж декількох відсотків усіх табличних рядів) не буде користуватися індексом, то взагалі так чи інакше немає ніякої потреби у зберіганні тих рядів у індексі. Це скорочує розмір з індексу, який збільшить швидкість тих запитів, які користуються індексом. Це також збільшить швидкість багатьох операцій модифікації таблиць, тому що індекс не потрібно модифікувати в усіх випадках. Приклад нижче показує можливе застосування цієї ідеї.
Приклад. Встановлення часткового індексу, для виключення загальні значеь
Припустимо, що зберігаються логи входу на сервер у базі даних. Найбільше доступів походять від IP–адрес діапазону організації, але деякі є з якогось іншого місця (скажімо, працівники на комутованих підключеннях). Якщо пошуки IP є передусім для зовнішних входів, то ймовірно не треба індексувати діапазон IP, який відповідає підмережі фірми.
Таблиця типу така:
CREATE TABLE access_log(url varchar, client_ip inet, ... );
Для створення часткового індекса, який задовольняє наш приклад, користуються командою такого типу:
CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet ’192.168.100.0’ AND client_ip < inet ’192.168.100.255’);
Типовий запит, який може користуватися цим індексом, був би таким:
SELECT * FROM access_log
WHERE url = ’/index.html’ AND client_ip = inet ’212.78.10.32’;
Запит, який не може користуватися цим індексом, буде таким:
SELECT * FROM access_log
WHERE client_ip = inet ’192.168.100.23’;
Помітимо, що цей вид часткового індекса вимагає, щоб загальні значення бути наперед визначеними, тож такі часткові індекси краще усього використовуються для розповсюдження даних, які не змінюються. Індекси можуть бути оновлені для регулювання нових розповсюджень даних, але це додає надлишковість у підтримці.
Інше можливе використання для часткового індексу – виключення значення з індексу, в якому даний запит не зацікавлений (показано в прикладі нижче). Це призводить до тих же переваг, перелічених вище, але це закриває доступ через цей індекс до «нецікавих» значень, навіть якщо індексне сканування було б корисне у даному випадку. Очевидно, що встановлення часткових індексів для цього виду сценарію вимагає багато турботи і експериментування.
Приклад. Встановлення часткового індексу, для виключити нецікавих значень
Якщо маємо таблицю, яка містить як оголошені так і ні замовлення, і неоголошені замовлення займають маленьку частину від цілої таблиці, але ті її ряди до яких іде найбільше звернень, то можна поліпшити виконання створюючи індекс тільки на неоголошених рядах. Команда із створенням цього індекса є така:
CREATE INDEX orders_unbilled_index ON orders (order_nr)
WHERE billed is not true;
Для користування цього індекса можна утворити такий запит:
SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;
Проте, індекс може також бути використаний у запитах, які взагалі не включають order_nr, наприклад:
SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;
Це менш ефективно, ніж частковий індекс на стовпці amount, оскільки системі доведеться сканувати повний індекс. Все ж, якщо є відносно небагато неоголошених замовлень, доцільним може бути використання цього часткового індекса тільки для знаходження неоголошених замовлень.
Відмітимо, що даний запит не може користуватися цим індексом:
SELECT * FROM orders WHERE order_nr = 3501;
Замовлення 3501 може бути серед оголошених або неоголошених замовлень.
Цей приклад також ілюструє, що індексований стовпець і стовпець, використаний в предикаті, не мусить співпадати. PostgreSQL підтримує часткові індекси з довільними предикатами, доти, поки тільки стовпці із таблиці що індексується є залученими. Проте, зауважимо, що предикат повинен відповідати умовам використаним у запитах, які, як передбачається, отримують вигоду з індексу. Щоб бути точним, частковий індекс може бути використаний в запиті, тільки якщо система може розпізнати, що WHERE умова запиту
математично має на увазі предикату індексу. PostgreSQL не має витонченого доказу теореми, що може розпізнати математично еквівалентні вирази, які записуються в різних формах. (Не тільки тому що такий доказ дуже важко створити, але й тому що це так повільно, що навряд чи принесе якусь реальну користь.) Система може розпізнати прості значення нерівності, наприклад “x < 1" має на увазі "x < 2"; інакше умова предикати повинна точно відповідати частині запиту WHERE умові або індекс не буде розпізнаний як придатний для використання. Відповідність має місце під час планування запиту, і не під час його виконання. В результаті, параметризована умова запиту не працює з частковим індексом. Для прикладу готовий запит з параметром може конкретизувати "x < "? який ніколи не матиме на увазі "x < 2" для усіх можливих значень параметру.
Третє можливе використання для часткових індексів не вимагає, щоб індекс був використаний в запитах взагалі. Ідея тут полягає в створенні унікального індекса на підмножині таблиці, як у прикладі нижче. Це надає унікальність тим рядам, які задовольняють індексний предикат, без утримування тих, які не задовільняють.
Приклад. Установка часткового унікального індекса
Припустимо, що ми маємо таблицю, яка описує результати тесту. Ми бажаємо гарантувати, що є тільки один “успішний" вхід для даного предмету і комбінації цілі, але може бути будь яка кількість “невдалих" входів. Ось один шлях зробити це:
CREATE TABLE tests(subject text, target text, success boolean, ... );
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;
Це особливо ефективний підхід, коли є декілька успішних випробувань і багато невдалих.
Нарешті, частковий індекс може також бути використаний, щоб перевизначити план вибору запитів системою. Також, набори даних з нестандартним розповсюдженням, можуть змусити систему користуватися індексом, коли це не повитрібно. У цьому індекс можу бути визначений так що він не буде доступним для такого типу запиту. Нормально, PostgreSQL робить розсудливі вибори щодо індексного використання (наприклад, уникає їх, відшукуючи загальні значення, так що раніший приклад тільки зберігає індексний розмір, коли не вимагається уникати використання індексу), і грубо некоректні плани вибору - привід для повідомлення про баг.
Зауважимо, що установка часткового індексу вказує на щонайменше знання стільки, скільки планувальник запитів знає, зокрема відомо коли індекс може бути корисний. Формування цього знання вимагає досвіду і розуміння того, як індекси працюють в PostgreSQL. У більшості випадків, перевага часткового індекса над регулярним індексом буде мінімальною.
Більше інформації про часткові індекси може бути знайдена в описі часткових індексів.
Класи і сім’ї операторів. Індексне визначення може конкретизувати клас оператора для кожного стовпця індексу.
CREATE INDEX name ON table (column opclass [sort options] [, ...]);
Клас оператора ідентифікує операторів, які використані індексом для цього стовпця. Наприклад, B-tree індекс на type int4 користувався б класом int4_ops; цей клас оператора включає функції порівняння для значень типу int4. На практиці клас заданого за замовчуванням оператора для типу даних стовпця зазвичай є достатнім. Головна причина для наявності класів оператора є та, що для деяких типів даних, може бути більше ніж одна значуща поведінка. Наприклад, ми, можемо захотіти сортувати дані типу комплексних чисел за абсолютним значенням, або за реальною частиною. Ми змогли зробити це, визначивши два класи оператора для типу даних, а потім вибираючи належний клас під час створення індекса. Клас оператора визначає основний тип упорядкування (який може потім бути модифікований, додаючи опції виду ASC/DESC та/чи порожні NULLS
FIRST/NULLS LAST).
Є також такі вбудовані класи оператора окрім значення за замовчуванням: клас оператора text_pattern_ops, varchar_pattern_ops, і bpchar_pattern_ops що підтримують B-tree індекси на типах, text, varchar, і char відповідно. Різниця між ними і класами за замовчуванням є те, що значення порівнюються символ за символом, а не згідно з специфічними регональними правилами. Це робить ці класи оператора зручними для використання запитів, що включає зіставлення виразу із зразком (LIKE або POSIX регулярні вирази), коли база даних не користується стандартним регіоном "C". Як приклад, можна індексувати стовпець varchar подібно до цього:
CREATE INDEX test_index ON test_table (col varchar_pattern_ops);
Відмітимо, що слід також створити індекс з класом оператора, заданого за замовчуванням, якщо є потреба користуватися звичайним порівнянням <, <=, >, чи >= для індексів. Такі запити не можуть використовувати класи оператора xxx_pattern_ops. (Звичайні порівняння тотожності можуть користуватися цими класами операторів.) Можна створити множинні індекси на тому ж стовпці з іншим класом оператора. Якщо відбувається користування регіоном C, то не слід вживати класи оператора xxx_pattern_ops, тому що індекс з класом заданим за замовчанням оператора придатний для використання при виготовленні відповідності запитів регіону C.
Наступний запит показує усі визначені класи оператора :
SELECT am.amname AS index_method,
opc.opcname AS opclass_name
FROM pg_am am, pg_opclass opc
WHERE opc.opcmethod = am.oid
ORDER BY index_method, opclass_name;
Клас оператора є фактично тільки підмножиною більшої структури названої сім’єю оператора. У випадках, де декілька типів даних мають подібну поведінку, часто корисно визначити крос-типові (cross-data-type) оператори і дозволити їм працювати з індексами. Щоб зробити це, класи оператора для кожного з типів має бути групований у тій же сім’ї оператора. Крос-типові оператори є елементами сім’ї, але вони не асоційовані з будь-яким єдиним класом в межах сім’ї. Цей запит показує, що всі визначені сім’ї оператора і усі оператори включаються в кожну сім’ю:
SELECT am.amname AS index_method,
opf.opfname AS opfamily_name,
amop.amopopr::regoperator AS opfamily_operator
FROM pg_am am, pg_opfamily opf, pg_amop amop
WHERE opf.opfmethod = am.oid AND
amop.amopfamily = opf.oid
ORDER BY index_method, opfamily_name, opfamily_operator;
Тестування використання індексів. Хоча індексам в PostgreSQL не потрібна підтримка або налаштування, це все ще важливо для перевірки, яка індекси фактично використовує в роботі реального запиту. Тестування індексного використання для індивідуальних запитів робиться з командою EXPLAIN.
Сформулювати загальну процедуру для визначення, які індекси створити, порівняно складно. Є декілька типових випадків, які були показані в прикладах впродовж попередніх секцій. Інша частина цієї секції дає деякі такі попередження: