Bayan ainihi B-tree, PostgreSQL yana ba da nau'ikan indexes da yawa masu kiyaye — GIN, GiST, BRIN, Hash — kowannensu daidai ga nau'ikan data da patterns na bubugi, da kuma partial da expression indexes don sauya-sauya. Zaɓi nau'i na index mai dace zai iya bada hoto sosai ga aiki na bubugi na musamman.
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
B-tree shi ne ainihi kuma yana gudanar da yawancin lokuta — daidai, jeri, da tsari a kan ginshikai na jiya.
-- 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
GIN indexes mahimmanci ne ga JSONB, arrays, da bubugi-samarwa — bubugi da ke bincike ajiyawa ko jira.
-- 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 indexes (index kawai jerin da suka dace — karami, sauri) da expression indexes (index ƙimar da aka lissafi — aiki na gaujaye masu index) sune kayan aiki mai ƙarfi, mai sauya-sauya.
Ganewa bambancin nau'ikan indexes na PostgreSQL wani ilimi mai ƙarfi ne don inganta aiki, saboda nau'i na index da ya dace ga nau'i na data da pattern na bubugi zai iya bugi bubugi da sauri sosai waɗanda ainihi B-tree ba ya gudana masu ko kuma ba shi da shi gida. Yayin da B-tree (ainihi) ya rufar da yawancin lokuta (daidai, jeri, tsari), nau'ikan musamman sun bukai aiki ga data na musamman: GIN mahimmanci ne ga JSONB, arrays, da bubugi-samarwa (ajiyawa/jiran bubugi — saɗa aiki na ƙarfi na Postgres JSONB da array); GiST yana daidai da geometric, jeri, da kisan abokan bubugi (PostGIS samaniya, nau'ikan jeri); da BRIN yana ba da kananan, aiki mai ma'ana ga indexes na manyan teburin da tsari (kamar lokaci-jeri/append-only data — zabbar mai ƙarfi ga manyan teburin inda cikakken B-tree zai kasua).
Janawa wane nau'i na index da ke daidai da wace yanayi (GIN don JSONB/arrays/full-text, GiST don geometric/range, BRIN don manyan jerin data) babbar mahimmi ne ga inganta aikin aiki na Postgres na musamman.
Daidai mahimmanci ne partial indexes (indexing jerin da suka dace — karami da ma'ana idan ka bincika jeri, kamar masu jira) da expression indexes (indexing ƙimar da aka lissafi — ba sauri ga samarwa masu gaujaye kamar search mai gajerou na imel, wanda index na jiya ba zai iya sa ba).
Tun da zaɓi na index yana tasiri sosai ga aiki na bubugi, kuma tun da nau'ikan musamman na Postgres (lallai GIN don JSONB/arrays/full-text) su ne abin da ya sa nau'ikan data na musamman ya yi aiki, ganewa nau'ikan indexes da na'ura waɗa da kowane — da kuma partial da expression indexes — ilimi mai mahimmanci, mafi yawancin lokuta na inganta databases na PostgreSQL, bambanta masu shirye-shirye wanda zai iya sa Postgres ya sauri don nau'ikan bubugi da bambancin patterns, da muhimmin sashi na amfani da cikakken aiki na bases iska ya kasancewa.