{"id":79,"date":"2026-09-27T18:05:00","date_gmt":"2026-09-27T18:05:00","guid":{"rendered":"https:\/\/www.akifsen.com.tr\/blog\/2026\/09\/27\/composite-index-column-order\/"},"modified":"2026-09-27T18:05:00","modified_gmt":"2026-09-27T18:05:00","slug":"composite-index-column-order","status":"publish","type":"post","link":"https:\/\/www.akifsen.com.tr\/blog\/2026\/09\/27\/composite-index-column-order\/","title":{"rendered":"Index Eklemek Yetmez: Composite Index&#8217;te Kolon S\u0131ras\u0131 Neden \u00d6nemli?"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Yava\u015f bir sorgu fark edildi\u011finde ilk yap\u0131lan \u015fey genellikle \u015fudur:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Sorgudaki kolonlara bak\u0131l\u0131r.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Her birine index eklenir.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE INDEX idx_orders_tenant ON orders (tenant_id);\nCREATE INDEX idx_orders_status ON orders (status);\nCREATE INDEX idx_orders_created ON orders (created_at);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u00dc\u00e7 index.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Sorgu yine yava\u015f.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Veritaban\u0131 genellikle tek index se\u00e7er<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u015eu sorguyu d\u00fc\u015f\u00fcnelim:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM orders\nWHERE tenant_id = 42\n  AND status = 'PENDING'\nORDER BY created_at DESC\nLIMIT 20;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u00dc\u00e7 ayr\u0131 index oldu\u011funda planner \u00e7o\u011fu zaman bunlardan birini se\u00e7er. Diyelim ki <code>tenant_id<\/code> index&#8217;i. O tenant&#8217;\u0131n b\u00fct\u00fcn sipari\u015flerini bulur, sonra <code>status<\/code> ko\u015fulunu sat\u0131r sat\u0131r kontrol eder, sonra sonucu s\u0131ralar.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Tenant&#8217;\u0131n 2 milyon sipari\u015fi varsa ve bunlar\u0131n 30 tanesi <code>PENDING<\/code> ise, veritaban\u0131 30 sat\u0131r i\u00e7in 2 milyon sat\u0131r\u0131 gezer.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL baz\u0131 durumlarda bitmap scan ile birden fazla index&#8217;i birle\u015ftirebilir. Ama bu her zaman verimli de\u011fildir ve s\u0131ralama ihtiyac\u0131n\u0131 kar\u015f\u0131lamaz.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Tek bir composite index<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Ayn\u0131 sorgu i\u00e7in daha do\u011fru yap\u0131 \u015fudur:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE INDEX idx_orders_tenant_status_created\nON orders (tenant_id, status, created_at DESC);<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Veritaban\u0131 index&#8217;te do\u011frudan <code>tenant_id = 42<\/code> ve <code>status = 'PENDING'<\/code> olan b\u00f6l\u00fcme gider. O b\u00f6l\u00fcmdeki kay\u0131tlar zaten <code>created_at<\/code> s\u0131ras\u0131ndad\u0131r. \u0130lk 20 sat\u0131r\u0131 okur ve durur.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ayr\u0131 bir s\u0131ralama ad\u0131m\u0131 yok. Gereksiz sat\u0131r okuma yok.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Soldan sa\u011fa kural\u0131<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Composite index bir telefon rehberi gibi \u00e7al\u0131\u015f\u0131r. \u00d6nce soyad\u0131na, sonra ada g\u00f6re s\u0131ral\u0131d\u0131r.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Soyad\u0131 bilinirse arama h\u0131zl\u0131d\u0131r. Soyad\u0131 ve ad bilinirse daha da h\u0131zl\u0131d\u0131r. Ama sadece ad biliniyorsa rehberin tamam\u0131n\u0131 taramak gerekir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Index&#8217;te de ayn\u0131 \u015fey ge\u00e7erli. <code>(tenant_id, status, created_at)<\/code> index&#8217;i \u015fu sorgularda kullan\u0131labilir:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>WHERE tenant_id = ?\nWHERE tenant_id = ? AND status = ?\nWHERE tenant_id = ? AND status = ? ORDER BY created_at<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Ama sadece <code>status<\/code> ile filtreleyen bir sorgu bu index&#8217;ten verimli \u015fekilde faydalanamaz.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Kolon s\u0131ras\u0131 nas\u0131l se\u00e7ilir?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Benim kulland\u0131\u011f\u0131m basit bir s\u0131ra var:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u00d6nce e\u015fitlik ko\u015fullar\u0131.<\/strong> <code>=<\/code> ile filtrelenen kolonlar ba\u015fa gelir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Sonra s\u0131ralama veya aral\u0131k.<\/strong> <code>ORDER BY<\/code> ya da <code>&gt;<\/code>, <code>&lt;<\/code>, <code>BETWEEN<\/code> ile kullan\u0131lan kolon sona gelir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Aral\u0131k ko\u015fulundan sonra gelen kolonlar index \u00fczerinde ayn\u0131 verimlilikle kullan\u0131lamaz. Bu y\u00fczden <code>created_at &gt; ?<\/code> gibi bir ko\u015ful genellikle index&#8217;in son kolonu olmal\u0131d\u0131r.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">E\u015fitlik kolonlar\u0131 aras\u0131nda s\u0131ralama yaparken s\u0131k \u00f6nerilen kural &#8220;en se\u00e7ici kolon ba\u015fa&#8221; kural\u0131d\u0131r. Ama multi-tenant sistemlerde <code>tenant_id<\/code> neredeyse her sorguda bulundu\u011fu i\u00e7in, se\u00e7icili\u011fi d\u00fc\u015f\u00fck olsa bile ba\u015fa koymak \u00e7o\u011fu zaman daha mant\u0131kl\u0131d\u0131r. Bir index&#8217;in ka\u00e7 farkl\u0131 sorguya hizmet edebildi\u011fi de en az se\u00e7icilik kadar \u00f6nemli.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">EXPLAIN okumak<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Tahmin etmek yerine \u00f6l\u00e7mek gerekir:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>EXPLAIN (ANALYZE, BUFFERS)\nSELECT ...<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Bak\u0131lacak birka\u00e7 \u015fey:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>Seq Scan<\/code> b\u00fcy\u00fck bir tabloda g\u00f6r\u00fcn\u00fcyorsa index kullan\u0131lm\u0131yor demektir.<\/li>\n\n\n<li><code>Rows Removed by Filter<\/code> de\u011feri y\u00fcksekse index do\u011fru b\u00f6l\u00fcme gitmiyor, gereksiz sat\u0131rlar okunuyor demektir.<\/li>\n\n\n<li>Plan&#8217;da ayr\u0131 bir <code>Sort<\/code> ad\u0131m\u0131 varsa index s\u0131ralama ihtiyac\u0131n\u0131 kar\u015f\u0131lam\u0131yor demektir.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Planner&#8217;\u0131n tahmin etti\u011fi sat\u0131r say\u0131s\u0131 ile ger\u00e7ek sat\u0131r say\u0131s\u0131 aras\u0131nda b\u00fcy\u00fck fark varsa, istatistikler g\u00fcncel olmayabilir. <code>ANALYZE<\/code> \u00e7al\u0131\u015ft\u0131rmak bazen tek ba\u015f\u0131na plan\u0131 de\u011fi\u015ftirir.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Her index bir maliyettir<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Index okumay\u0131 h\u0131zland\u0131r\u0131r ama yazmay\u0131 yava\u015flat\u0131r. Her <code>INSERT<\/code> ve <code>UPDATE<\/code>, ilgili b\u00fct\u00fcn index&#8217;lerin de g\u00fcncellenmesi demektir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Yukar\u0131daki \u00fc\u00e7 tekli index&#8217;i tek bir composite index ile de\u011fi\u015ftirmek hem okumay\u0131 h\u0131zland\u0131r\u0131r hem de yazma maliyetini d\u00fc\u015f\u00fcr\u00fcr.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL&#8217;de <code>pg_stat_user_indexes<\/code> g\u00f6r\u00fcn\u00fcm\u00fc hangi index&#8217;lerin hi\u00e7 kullan\u0131lmad\u0131\u011f\u0131n\u0131 g\u00f6sterir. Y\u0131llar i\u00e7inde biriken ve hi\u00e7bir sorgunun kullanmad\u0131\u011f\u0131 index&#8217;leri temizlemek, yazma a\u011f\u0131rl\u0131kl\u0131 tablolarda fark edilir bir iyile\u015fme sa\u011flayabilir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Index tasar\u0131m\u0131 kolonlardan de\u011fil, sorgulardan ba\u015flar.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Do\u011fru soru &#8220;hangi kolona index ekleyelim?&#8221; de\u011fil, &#8220;bu sorgu veriye hangi yoldan ula\u015fmal\u0131?&#8221; sorusu.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sorguda kullan\u0131lan her kolon i\u00e7in index var ama sorgu h\u00e2l\u00e2 yava\u015f. Composite index&#8217;te kolon s\u0131ras\u0131, se\u00e7icilik ve EXPLAIN \u00e7\u0131kt\u0131s\u0131n\u0131 do\u011fru okumak \u00fczerine.<\/p>\n","protected":false},"author":1,"featured_media":78,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-79","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-veritabani"],"_links":{"self":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts\/79","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/comments?post=79"}],"version-history":[{"count":0,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts\/79\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/media\/78"}],"wp:attachment":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/media?parent=79"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/categories?post=79"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/tags?post=79"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}