PostgreSQL veritaban\u0131 tek-s\u00fctun (single-column), \u00e7ok-s\u00fctun (multicolumn), k\u0131smi-index (partial index), tekil-index (unique index), ifade indexi (expression index), dahili index (implicit index), ve e\u015f zamanl\u0131 indexleri (concurrent index) destekler. Bunlar genel indeksleme (dizin olu\u015fturma) y\u00f6ntemleri iken, bir de index t\u00fcrleri vard\u0131r. PostgreSQL’in destekledi\u011fi index t\u00fcrleri\u00a0B-tree<\/strong>, hash<\/strong>, GiST<\/strong>,\u00a0GIN<\/strong>\u00a0ve BRIN<\/strong>‘dir.<\/p>\n \u0130ndeksleme t\u00fcr\u00fc index olu\u015fturma esnas\u0131nda USING y\u00f6ntemi ile se\u00e7ilebilir. Farkl\u0131 index t\u00fcrlerinin farkl\u0131 varolu\u015f sebepleri vard\u0131r. \u00d6rne\u011fin, B-tree index’ler sorgular bir aral\u0131k (range) ve e\u015fitlik operat\u00f6r\u00fc i\u00e7eriyorsa verimli iken, hash index’ler e\u015fitlik operat\u00f6r\u00fc kullan\u0131l\u0131yorsa sorguda kullan\u0131l\u0131rlar.<\/p>\n B-tree index sorguda e\u015fitlik operat\u00f6r\u00fc (=) ve aral\u0131k (range) operat\u00f6rleri varsa (<, <=, >, >=, BETWEEN, ve IN) kullan\u0131l\u0131r.<\/p>\n Hash indeksler\u00a0 sorguda sadece e\u015fitlik arama operat\u00f6r\u00fc varsa devreye giren index’lerdir.<\/p>\n GiST (Generalized Search Tree) indexler kendiniz bir index metodu olu\u015fturmak istedi\u011finizde kulland\u0131\u011f\u0131n\u0131z t\u00fcrd\u00fcr. Genellikle \u00f6zel bir veri format\u0131 olu\u015fturuldu\u011fundan bunun indexlenmesi i\u00e7in de \u00f6zel bir metod olu\u015fturulur. GIN (Generalized Inverted Index) index ise,\u00a0tersy\u00fcz (inverted) edilmi\u015f indexlerdir. Arama i\u015flemlerinde daha \u00e7ok kullan\u0131l\u0131r. Bu tip indexler, her kelime i\u00e7in bir index girdisi ve bu girdinin i\u00e7inde de kelimenin ge\u00e7ti\u011fi yerlerin listesini s\u0131k\u0131\u015ft\u0131r\u0131lm\u0131\u015f olarak tutarlar. Birden fazla kelime ile arama yap\u0131ld\u0131\u011f\u0131nda bu tip indexler \u00f6nce ilk uyu\u015fmay\u0131 bulur ve sonra indexi kullanarak di\u011fer kelimelerin ge\u00e7medi\u011fi girdileri \u00e7\u0131kartarak i\u015flem yaparlar.<\/p>\n \u0130ki index de daha \u00e7ok, \u00f6zellikle full-text aramalar i\u00e7in olu\u015fturulur. Full-text arama i\u00e7in bu indexleri olu\u015fturma zorunlulu\u011fu yoktur, duruma \u00f6zel, performans i\u00e7in vs kullan\u0131l\u0131r.<\/p>\n BRIN (Block Range Index) indexler PostgreSQL 9.5 s\u00fcr\u00fcm\u00fcnde geli\u015ftirilmi\u015f, ve bir \u00e7ok b\u00fcy\u00fck veritaban\u0131 sa\u011flay\u0131c\u0131s\u0131nda halen daha olmayan, yap\u0131lmaya \u00e7al\u0131\u015f\u0131lan tablonun nerede ve nas\u0131l tutuldu\u011funa ba\u011fl\u0131 olarak \u00e7al\u0131\u015fan indexlerdir.\u00a0BRIN, \u00e7ok b\u00fcy\u00fck tablolarda, belirli bir s\u00fctundaki verinin disk \u00fczerinde nereye yaz\u0131ld\u0131\u011f\u0131 ile do\u011fal bir ili\u015fkisi oldu\u011funda kullan\u0131labilir ve t\u00fcm tablo taramalar\u0131nda b\u00fcy\u00fck bir blok veriyi es ge\u00e7erek b\u00fcy\u00fck performans art\u0131\u015flar\u0131 sa\u011flayabilir.<\/p>\n BRIN, b\u00fcy\u00fck bir blok veriyi kompakt bir halde\u00a0 \u00f6zete indirger ve sorgunun daha ilk a\u015famas\u0131nda \u00f6zet \u00fczerinden test ederek b\u00fcy\u00fck bir blo\u011fu sorgu harici b\u0131rakarak detayl\u0131 bir \u015fekilde tarama yapaca\u011f\u0131 b\u00fcy\u00fck veriyi en aza indirmi\u015f olur. Aksi durumda \u00e7ok pahal\u0131 olan “sat\u0131r sat\u0131r t\u00fcm tablo taramas\u0131” (full table scan<\/em>) yap\u0131lacakt\u0131r.<\/p>\n BRIN index’ler yatay b\u00f6l\u00fcmlendirme (horizontal partitioning) veya sharding yap\u0131s\u0131na benzer bir performans art\u0131r\u0131c\u0131 olarak kullan\u0131lmaktad\u0131r. BRIN index, Oracle Exadata taraf\u0131ndaki “Storage Index” kavram\u0131na denk d\u00fc\u015fer dersek yanl\u0131\u015f olmaz.<\/strong><\/p>","protected":false},"excerpt":{"rendered":" PostgreSQL’in destekledi\u011fi index t\u00fcrleri\u00a0B-tree, hash, GiST,\u00a0GIN\u00a0ve BRIN’dir. PostgreSQL veritaban\u0131 tek-s\u00fctun (single-column), \u00e7ok-s\u00fctun (multicolumn), k\u0131smi-index (partial index), tekil-index (unique index), ifade indexi (expression index), dahili index (implicit index), ve e\u015f zamanl\u0131 indexleri (concurrent index) destekler.<\/p>","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75,45],"tags":[48,60],"class_list":["post-2024","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-yazi","tag-enterprisedb","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/2024","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/comments?post=2024"}],"version-history":[{"count":0,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/2024\/revisions"}],"wp:attachment":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/media?parent=2024"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/categories?post=2024"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/tags?post=2024"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}PostgreSQL Index T\u00fcrleri<\/h2>\n
B-tree Index<\/h3>\n
Hash Index<\/h3>\n
my_db=# EXPLAIN SELECT COUNT(*) FROM my_table WHERE item_id = 99;\nQUERY PLAN\n------------------------------------------------------------------ Aggregate (cost=8.02..8.03 rows=1 width=0) \n -> Index Scan using item_hash_index on item (cost=0.00..8.02 rows=1 width=0) \n Index Cond: (item_id = 99) (3 rows)<\/code><\/pre>\nGiST ve GIN Index<\/h3>\n
BRIN Index<\/h3>\n