PostgreSQL supports single-column, multicolumn, partial index, unique index, expression index, implicit index, and concurrent indexes. While these are general indexing methods, there are also index types. Index types supported by PostgreSQL\u00a0B-tree<\/strong>, hash<\/strong>, GiST<\/strong>,\u00a0Gin<\/strong>\u00a0or BRIN<\/strong>\u2018Say.<\/p>\n The indexing type can be selected using the USING method during index creation. Different index types have different reasons for existence. For example, while B-tree indexes are efficient when queries contain a range and equality operator, hash indexes are used in the query if the equality operator is used.<\/p>\n B-tree index is used when a query contains the equality operator (=) and range operators (<, , >=, BETWEEN, and IN).<\/p>\n Hash indexes are indexes that come into play only when there is an equality search operator in the query.<\/p>\n GiST (Generalized Search Tree) indexes are the type you use when you want to create your own index method. Usually, since a custom data format is created, a custom method is also created to index it. GIN (Generalized Inverted Index) indexes are inverted indexes. They are used more frequently in search operations. These types of indexes keep an index entry for each word and, within this entry, a compressed list of the locations where the word appears. When searching with multiple words, these types of indexes first find the initial match and then perform the operation by using the index to filter out entries where the other words do not appear.<\/p>\n Both indexes are created more for, especially full-text searches. There is no obligation to create these indexes for full-text search; they are used depending on the situation, for performance, etc.<\/p>\n BRIN (Block Range Index) indexes were introduced in PostgreSQL 9.5 and operate depending on where and how the table being created is stored, a feature still absent in many major database providers. BRIN can be used in very large tables when there is a natural correlation between the data in a specific column and where it is written on disk, and it can provide massive performance gains by skipping large blocks of data during full table scans.<\/p>\n BRIN reduces a large block of data into a compact summary and, by testing it against the summary at the very beginning of the query, excludes the large block from the query, thereby minimizing the large data to be scanned in detail. Otherwise, the very expensive \u201crow-by-row full table scan\u201d (full table scan<\/em>) will be done.<\/p>\n BRIN indexes are used as a performance enhancer similar to horizontal partitioning or sharding. It would not be wrong to say that the BRIN index corresponds to the concept of \u201cStorage Index\u201d on the Oracle Exadata side.<\/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 Types<\/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 and GIN Index<\/h3>\n
BRIN Index<\/h3>\n