A default B-tree mellett a PostgreSQL több specializált indextípust kínál — GIN, GiST, BRIN, Hash — mindegyik különböző adatokra és lekérdezési mintákra alkalmas, plusz partial és expression indexek a rugalmassághoz. A megfelelő indextípus kiválasztása drámai módon javíthatja a teljesítményt speciális lekérdezések esetén.
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
A B-tree az alapértelmezett, és az esetek többségét kezeli — egyenlőség, tartomány és rendezés közönséges oszlopokon.
-- GIN (Generalized Inverted Index) — for values CONTAINING multiple items
CREATE INDEX idx_attrs ON products USING GIN (attributes); -- JSONB
CREATE INDEX idx_tags ON posts USING GIN (tags); -- arrays
CREATE INDEX idx_search ON docs USING GIN (to_tsvector('english', body)); -- full-text
-- great for: JSONB containment, array membership, full-text search
A GIN indexek nélkülözhetetlen a JSONB, tömbök és teljes szöveges kereséshez — a tartalmazást vagy tagságot ellenőrző lekérdezésekhez.
-- GiST — geometric, range, nearest-neighbor (e.g. PostGIS, range types)
CREATE INDEX idx_geo ON places USING GiST (location);
-- BRIN (Block Range INdex) — tiny index for LARGE tables with naturally-ordered data
CREATE INDEX idx_time ON logs USING BRIN (created_at); -- great for huge time-series
-- tiny size, efficient for large append-only/ordered data
-- Hash — equality only (=); rarely needed (B-tree handles equality too)
CREATE INDEX idx_hash ON t USING HASH (col);
-- PARTIAL index — index only SOME rows (smaller, targeted)
CREATE INDEX idx_active ON users(email) WHERE active = true; -- only active users
-- EXPRESSION index — index a computed value
CREATE INDEX idx_lower_email ON users(LOWER(email)); -- for case-insensitive lookups
SELECT * FROM users WHERE LOWER(email) = '[email protected]'; -- uses the index
Partial indexek (csak a releváns sorokat indexelik — kisebbek, gyorsabbak) és expression indexek (egy számított értéket indexelnek — lehetővé téve az indexelt függvénypapíralapú lekérdezéseket) hatékony, rugalmas eszközök.
A PostgreSQL indextípusainak sokfélesége értékes tudás a teljesítmény-optimalizáláshoz, mert a megfelelő indextípus az adott adatra és lekérdezési mintára drámai módon felgyorsíthatja azokat a lekérdezéseket, amelyeket az alapértelmezett B-tree rosszul vagy egyáltalán nem kezel.
Bár a B-tree (az alapértelmezett) az esetek többségét lefedi (egyenlőség, tartomány, rendezés), a specializált típusok felszabadítják a teljesítményt speciális adatokhoz: a GIN nélkülözhetetlen a JSONB, tömbök és teljes szöveges kereséshez (tartalmazás/tagság lekérdezések — a Postgres erőteljes JSONB és tömb funkcióinak teljesítménnyé tétele); a GiST geometriai, tartomány és legközelebbi szomszéd lekérdezéseket kezel (PostGIS térinformatikai, tartománytípusok); és a BRIN apró, hatékony indexeket biztosít a nagy, természetesen rendezett táblákhoz (például idősor/append-only adatok — értékes lehetőség hatalmas táblákhoz, ahol a teljes B-tree költséges lenne).
Tudomása arról, hogy mely indextípus melyik forgatókönyvre illik (GIN a JSONB/tömbök/teljes szöveges kereséshez, GiST geometriai/tartományokhoz, BRIN nagy rendezett adatokhoz), kulcsfontosságú a Postgres fejlett funkcióinak optimalizálásához.
Éppen olyan értékesek a partial indexek (csak a releváns sorok indexelése — kisebbek és hatékonyabbak, amikor egy részhalmazt kérdez le, például aktív felhasználók) és az expression indexek (számított értékek indexelése — gyors függvénypapíralapú kereséseket tesznek lehetővé, például nagybetű-független e-mail kereséseket, amelyeket egy egyszerű index nem támogat).
Mivel az indextípus választása jelentősen befolyásolja a lekérdezés teljesítményét, és mivel a Postgres specializált indextípusai (különösen a GIN a JSONB/tömbök/teljes szöveges kereséshez) azok, amelyek teljesítménnyé teszik a fejlett adattípusait, az indextípusok és az egyes típusok használatának időpontjának ismerete — beleértve a partial és expression indexeket — értékes, gyakran releváns tudás a PostgreSQL adatbázisok optimalizálásához, megkülönböztet azokat a fejlesztőket, akik gyorssá tudják tenni a Postgreset sokféle lekérdezési mintához, és az adatbázis teljes teljesítményi képességeinek kihasználásának kulcsfontosságú aspektusa.