{"id":239,"date":"2026-11-13T06:03:00","date_gmt":"2026-11-13T06:03:00","guid":{"rendered":"https:\/\/www.akifsen.com.tr\/blog\/?p=239"},"modified":"2026-11-13T06:03:00","modified_gmt":"2026-11-13T06:03:00","slug":"postgresql-explain-analyze-nasil-okunur","status":"publish","type":"post","link":"https:\/\/www.akifsen.com.tr\/blog\/2026\/11\/13\/postgresql-explain-analyze-nasil-okunur\/","title":{"rendered":"EXPLAIN ANALYZE Nas\u0131l Okunur? PostgreSQL Sorgu Plan\u0131n\u0131 Anlamak"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><strong>EXPLAIN ANALYZE<\/strong>, PostgreSQL&#8217;in bir sorguyu ger\u00e7ekten \u00e7al\u0131\u015ft\u0131r\u0131p hangi yolla \u00e7al\u0131\u015ft\u0131rd\u0131\u011f\u0131n\u0131 ve her ad\u0131mda ne kadar zaman harcad\u0131\u011f\u0131n\u0131 g\u00f6steren komuttur. Yava\u015f bir sorgunun neden yava\u015f oldu\u011funu tahmin etmek yerine \u00f6l\u00e7menin en do\u011frudan yolu budur. \u00c7\u0131kt\u0131 ilk bak\u0131\u015fta karma\u015f\u0131k g\u00f6r\u00fcnse de birka\u00e7 temel kavram\u0131 bildi\u011finizde, sorunun nerede oldu\u011funu \u00e7o\u011fu zaman birka\u00e7 dakikada bulabilirsiniz.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Bu yaz\u0131da bir plan\u0131 nas\u0131l okudu\u011fumu, hangi sat\u0131rlara \u00f6nce bakt\u0131\u011f\u0131m\u0131 ve en s\u0131k kar\u015f\u0131la\u015ft\u0131\u011f\u0131m yanl\u0131\u015f okumalar\u0131 anlataca\u011f\u0131m. \u00d6rnekler PostgreSQL 16 ve 17 \u00fczerinden ama mant\u0131k eski s\u00fcr\u00fcmlerde de ayn\u0131.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">EXPLAIN ile EXPLAIN ANALYZE aras\u0131ndaki fark nedir?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">D\u00fcz <code>EXPLAIN<\/code> sorguyu \u00e7al\u0131\u015ft\u0131rmaz. Planlay\u0131c\u0131n\u0131n se\u00e7ti\u011fi plan\u0131 ve tahmin etti\u011fi maliyetleri g\u00f6sterir. H\u0131zl\u0131d\u0131r ve production&#8217;da g\u00fcvenle \u00e7al\u0131\u015ft\u0131r\u0131labilir, ama sadece bir tahmindir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>EXPLAIN ANALYZE<\/code> ise sorguyu ger\u00e7ekten \u00e7al\u0131\u015ft\u0131r\u0131r. Her plan d\u00fc\u011f\u00fcm\u00fc i\u00e7in ger\u00e7ek s\u00fcreyi, ger\u00e7ek sat\u0131r say\u0131s\u0131n\u0131 ve ka\u00e7 kez \u00e7al\u0131\u015ft\u0131\u011f\u0131n\u0131 ekler. Tahmin ile ger\u00e7e\u011fin yan yana durmas\u0131, bu komutu de\u011ferli yapan \u015feydir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Bunun \u00f6nemli bir sonucu var: <code>EXPLAIN ANALYZE<\/code> bir <code>UPDATE<\/code> veya <code>DELETE<\/code> sorgusunu da ger\u00e7ekten \u00e7al\u0131\u015ft\u0131r\u0131r. Yazma sorgular\u0131n\u0131 incelerken transaction i\u00e7inde \u00e7al\u0131\u015ft\u0131r\u0131p geri almak gerekir:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>BEGIN;\nEXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'X' WHERE ...;\nROLLBACK;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Ben neredeyse her zaman <code>BUFFERS<\/code> se\u00e7ene\u011fini de ekliyorum. Sorgunun ka\u00e7 sayfa okudu\u011funu ve bunlar\u0131n bellekten mi diskten mi geldi\u011fini g\u00f6steriyor. S\u00fcre makineye ve o anki y\u00fcke g\u00f6re de\u011fi\u015fir, okunan sayfa say\u0131s\u0131 ise \u00e7ok daha tutarl\u0131 bir \u00f6l\u00e7\u00fcd\u00fcr.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Plan a\u011fac\u0131 nas\u0131l okunur?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Bir plan, i\u00e7 i\u00e7e ge\u00e7mi\u015f d\u00fc\u011f\u00fcmlerden olu\u015fan bir a\u011fa\u00e7t\u0131r. Her d\u00fc\u011f\u00fcm bir i\u015flemdir: tablo taramak, index&#8217;ten okumak, iki sonucu birle\u015ftirmek, s\u0131ralamak.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Limit  (cost=0.43..12.87 rows=20 width=64) (actual time=0.041..0.212 rows=20 loops=1)\n  -&gt;  Index Scan using idx_orders_customer_created on orders\n        (cost=0.43..1854.20 rows=2980 width=64)\n        (actual time=0.039..0.204 rows=20 loops=1)\n        Index Cond: (customer_id = 42)\nPlanning Time: 0.180 ms\nExecution Time: 0.240 ms<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Okuma y\u00f6n\u00fc i\u00e7eriden d\u0131\u015far\u0131ya do\u011frudur. En i\u00e7teki d\u00fc\u011f\u00fcm \u00f6nce \u00e7al\u0131\u015f\u0131r ve sonucunu bir \u00fcstteki d\u00fc\u011f\u00fcme verir. Yukar\u0131daki \u00f6rnekte index taramas\u0131 sat\u0131rlar\u0131 \u00fcretir, <code>Limit<\/code> ilk 20 sat\u0131r\u0131 ald\u0131ktan sonra durur.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Her d\u00fc\u011f\u00fcmde iki parantez grubu var. \u0130lki planlay\u0131c\u0131n\u0131n tahmini, ikincisi ger\u00e7ek \u00f6l\u00e7\u00fcm.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Alan<\/th><th>Anlam\u0131<\/th><\/tr><\/thead><tbody><tr><td>cost=0.43..12.87<\/td><td>Ba\u015flang\u0131\u00e7 ve toplam tahmini maliyet, birimsiz<\/td><\/tr><tr><td>rows=20 (ilk grup)<\/td><td>Planlay\u0131c\u0131n\u0131n bekledi\u011fi sat\u0131r say\u0131s\u0131<\/td><\/tr><tr><td>actual time=0.041..0.212<\/td><td>\u0130lk sat\u0131ra ve son sat\u0131ra kadar ge\u00e7en ger\u00e7ek s\u00fcre, ms<\/td><\/tr><tr><td>rows=20 (ikinci grup)<\/td><td>Ger\u00e7ekte \u00fcretilen sat\u0131r say\u0131s\u0131<\/td><\/tr><tr><td>loops=1<\/td><td>D\u00fc\u011f\u00fcm\u00fcn ka\u00e7 kez \u00e7al\u0131\u015ft\u0131\u011f\u0131<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><code>cost<\/code> de\u011feri milisaniye de\u011fildir. Planlay\u0131c\u0131n\u0131n farkl\u0131 planlar\u0131 kar\u015f\u0131la\u015ft\u0131rmak i\u00e7in kulland\u0131\u011f\u0131 g\u00f6reli bir say\u0131d\u0131r. \u0130ki farkl\u0131 sorgunun maliyetini birbiriyle kar\u015f\u0131la\u015ft\u0131rmak anlaml\u0131 de\u011fildir.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">loops neden \u00f6nemli?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><code>actual time<\/code> ve <code>rows<\/code> de\u011ferleri bir d\u00f6ng\u00fc ba\u015f\u0131nad\u0131r. Bir d\u00fc\u011f\u00fcm 5.000 kez \u00e7al\u0131\u015ft\u0131ysa, ger\u00e7ek toplam s\u00fcre g\u00f6sterilen s\u00fcrenin 5.000 kat\u0131d\u0131r. \u0130\u00e7 i\u00e7e d\u00f6ng\u00fc (nested loop) birle\u015fimlerinde bu detay atland\u0131\u011f\u0131nda, en pahal\u0131 d\u00fc\u011f\u00fcm masum g\u00f6r\u00fcnebilir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">0.05 ms s\u00fcren ama 40.000 kez \u00e7al\u0131\u015fan bir index taramas\u0131, toplamda 2 saniye eder. Plan\u0131n en yava\u015f k\u0131sm\u0131 odur, ama sat\u0131rdaki rakam bunu ilk bak\u0131\u015fta g\u00f6stermez.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\u00d6nce hangi sat\u0131rlara bakmal\u0131?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Uzun bir planda kaybolmamak i\u00e7in kendime s\u0131rayla \u015fu sorular\u0131 soruyorum.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Zaman nerede harcan\u0131yor?<\/strong> Her d\u00fc\u011f\u00fcm\u00fcn <code>actual time<\/code> de\u011ferinin biti\u015f k\u0131sm\u0131na bak\u0131p, alt d\u00fc\u011f\u00fcmlerin s\u00fcresini \u00e7\u0131kar\u0131nca o d\u00fc\u011f\u00fcm\u00fcn kendi s\u00fcresi bulunur. Toplam s\u00fcrenin b\u00fcy\u00fck k\u0131sm\u0131 genellikle bir veya iki d\u00fc\u011f\u00fcmde toplan\u0131r. Optimizasyonu oraya yapmak gerekir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Tahmin ile ger\u00e7ek ne kadar farkl\u0131?<\/strong> <code>rows=12<\/code> tahmin edilip <code>rows=184000<\/code> ger\u00e7ekle\u015ftiyse, planlay\u0131c\u0131 yanl\u0131\u015f bilgiyle karar vermi\u015f demektir. Yanl\u0131\u015f tahmin, yanl\u0131\u015f join y\u00f6ntemi ve yanl\u0131\u015f s\u0131ralama stratejisi se\u00e7ilmesinin en yayg\u0131n sebebidir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Gereksiz sat\u0131r okunuyor mu?<\/strong> <code>Rows Removed by Filter<\/code> de\u011feri y\u00fcksekse, sorgu ihtiyac\u0131ndan \u00e7ok daha fazla sat\u0131r okuyup sonra \u00e7o\u011funu at\u0131yor demektir. Bu genellikle eksik veya yanl\u0131\u015f s\u0131ralanm\u0131\u015f bir index i\u015faretidir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Bellek yetiyor mu?<\/strong> Sort ve hash d\u00fc\u011f\u00fcmlerinde <code>external merge<\/code> veya disk kullan\u0131m\u0131 g\u00f6r\u00fcyorsan\u0131z, i\u015flem <code>work_mem<\/code> s\u0131n\u0131r\u0131na s\u0131\u011fmam\u0131\u015f ve diske ta\u015fm\u0131\u015ft\u0131r.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Seq Scan her zaman k\u00f6t\u00fc m\u00fcd\u00fcr?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Hay\u0131r. Bu, en s\u0131k g\u00f6rd\u00fc\u011f\u00fcm yanl\u0131\u015f okumalardan biri.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">K\u00fc\u00e7\u00fck bir tabloda veya tablonun b\u00fcy\u00fck k\u0131sm\u0131n\u0131 d\u00f6nd\u00fcren bir sorguda s\u0131ral\u0131 tarama, index taramas\u0131ndan daha h\u0131zl\u0131d\u0131r. Index \u00fczerinden her sat\u0131ra ayr\u0131 ayr\u0131 gitmek, tabloyu ba\u015ftan sona okumaktan daha fazla rastgele disk eri\u015fimi gerektirir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Seq Scan&#8217;in problem oldu\u011fu durum \u015fudur: b\u00fcy\u00fck bir tabloda \u00e7ok az sat\u0131r d\u00f6nd\u00fcren bir sorgu. 10 milyon sat\u0131rl\u0131k bir tablodan 20 sat\u0131r almak i\u00e7in b\u00fct\u00fcn tabloyu okumak, neredeyse her zaman eksik bir index&#8217;e i\u015faret eder.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Hangi index&#8217;in do\u011fru oldu\u011fu ise ayr\u0131 bir konu. \u00c7ok kolonlu sorgularda kolon s\u0131ras\u0131n\u0131n neden belirleyici oldu\u011funu <a href=\"https:\/\/www.akifsen.com.tr\/blog\/2026\/09\/27\/composite-index-column-order\/\">composite index&#8217;te kolon s\u0131ras\u0131<\/a> yaz\u0131s\u0131nda ayr\u0131nt\u0131l\u0131 anlatm\u0131\u015ft\u0131m.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Sat\u0131r tahmini neden yanl\u0131\u015f \u00e7\u0131kar?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Planlay\u0131c\u0131 kararlar\u0131n\u0131 tablo istatistiklerine g\u00f6re verir. Bu istatistikler <code>ANALYZE<\/code> komutuyla, genellikle autovacuum taraf\u0131ndan otomatik olarak toplan\u0131r.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Tahminin bozulmas\u0131n\u0131n en yayg\u0131n sebepleri:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u0130statistikler g\u00fcncel de\u011fil. B\u00fcy\u00fck bir veri y\u00fcklemesinden sonra <code>ANALYZE<\/code> \u00e7al\u0131\u015fmad\u0131ysa planlay\u0131c\u0131 eski da\u011f\u0131l\u0131ma g\u00f6re karar verir.<\/li>\n\n\n<li>Kolonlar aras\u0131nda ili\u015fki var. <code>city = '\u0130stanbul' AND district = 'Kad\u0131k\u00f6y'<\/code> ko\u015fulunda planlay\u0131c\u0131 iki ko\u015fulu ba\u011f\u0131ms\u0131z sayar ve sonucu \u00e7ok d\u00fc\u015f\u00fck tahmin eder. <code>CREATE STATISTICS<\/code> ile \u00e7ok kolonlu istatistik tan\u0131mlamak bu durumu d\u00fczeltir.<\/li>\n\n\n<li>Fonksiyon veya ifade \u00fczerine ko\u015ful var. <code>lower(email) = ...<\/code> gibi ifadelerde ifade index&#8217;i yoksa planlay\u0131c\u0131n\u0131n da\u011f\u0131l\u0131m bilgisi zay\u0131ft\u0131r.<\/li>\n\n\n<li>\u00c7ok \u00e7arp\u0131k veri da\u011f\u0131l\u0131m\u0131 var. Bir m\u00fc\u015fterinin sipari\u015fleri tablonun yar\u0131s\u0131n\u0131 olu\u015fturuyorsa, ortalamaya g\u00f6re yap\u0131lan tahmin o m\u00fc\u015fteri i\u00e7in tamamen yanl\u0131\u015ft\u0131r.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Tahmin hatas\u0131 k\u00fc\u00e7\u00fck bir detay gibi g\u00f6r\u00fcn\u00fcr ama plan\u0131n geri kalan\u0131n\u0131 belirler. Planlay\u0131c\u0131 10 sat\u0131r bekliyorsa nested loop se\u00e7er. Ger\u00e7ekte 200.000 sat\u0131r gelirse, i\u00e7 d\u00f6ng\u00fc 200.000 kez \u00e7al\u0131\u015f\u0131r.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Join y\u00f6ntemleri ne anlat\u0131r?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL iki tabloyu \u00fc\u00e7 farkl\u0131 yolla birle\u015ftirebilir. Hangisinin se\u00e7ildi\u011fi, tahmini sat\u0131r say\u0131s\u0131na ba\u011fl\u0131d\u0131r.<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Y\u00f6ntem<\/th><th>Ne zaman iyi<\/th><th>Dikkat edilecek<\/th><\/tr><\/thead><tbody><tr><td>Nested Loop<\/td><td>D\u0131\u015f tarafta az sat\u0131r, i\u00e7 tarafta index varsa<\/td><td>D\u0131\u015f taraf beklenenden b\u00fcy\u00fckse \u00e7ok yava\u015flar<\/td><\/tr><tr><td>Hash Join<\/td><td>Orta ve b\u00fcy\u00fck tablolar, e\u015fitlik ko\u015fulu<\/td><td>Hash tablosu work_mem&#8217;e s\u0131\u011fmazsa diske ta\u015far<\/td><\/tr><tr><td>Merge Join<\/td><td>\u0130ki taraf da birle\u015fim kolonuna g\u00f6re s\u0131ral\u0131ysa<\/td><td>S\u0131ralama gerekiyorsa ek maliyet<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">ORM kullan\u0131lan projelerde s\u0131k g\u00f6rd\u00fc\u011f\u00fcm bir durum, tek bir sorgu yerine y\u00fczlerce k\u00fc\u00e7\u00fck sorgunun \u00e7al\u0131\u015fmas\u0131d\u0131r. Bu durumda <code>EXPLAIN<\/code> her sorguyu ayr\u0131 ayr\u0131 h\u0131zl\u0131 g\u00f6sterir, problem ise sorgu say\u0131s\u0131ndad\u0131r. Bu konuyu <a href=\"https:\/\/www.akifsen.com.tr\/blog\/2026\/10\/14\/orm-n-plus-one-query\/\">ORM&#8217;lerde N+1 sorgu problemi<\/a> yaz\u0131s\u0131nda anlatm\u0131\u015ft\u0131m.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">BUFFERS \u00e7\u0131kt\u0131s\u0131 neden s\u00fcreden daha g\u00fcvenilir?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><code>BUFFERS<\/code> se\u00e7ene\u011fi her d\u00fc\u011f\u00fcme \u015funa benzer bir sat\u0131r ekler:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Buffers: shared hit=1204 read=8811<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>hit<\/code>, bellekteki shared buffer&#8217;lardan okunan sayfa say\u0131s\u0131d\u0131r. <code>read<\/code>, diskten veya i\u015fletim sistemi \u00f6nbelle\u011finden okunan sayfa say\u0131s\u0131d\u0131r. Her sayfa 8 KB.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Ayn\u0131 sorguyu iki kez \u00e7al\u0131\u015ft\u0131rd\u0131\u011f\u0131n\u0131zda ikincisi \u00e7ok daha h\u0131zl\u0131 \u00e7\u0131kabilir, \u00e7\u00fcnk\u00fc sayfalar art\u0131k bellektedir. S\u00fcreye bakarak &#8220;sorunu \u00e7\u00f6zd\u00fcm&#8221; demek kolayd\u0131r. Okunan sayfa say\u0131s\u0131 ise \u00f6nbellekten ba\u011f\u0131ms\u0131z olarak sorgunun ne kadar i\u015f yapt\u0131\u011f\u0131n\u0131 g\u00f6sterir. 9.000 sayfa okuyan bir sorguyu 50 sayfa okuyan hale getirdiyseniz, ger\u00e7ekten bir \u015fey d\u00fczelmi\u015ftir.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Yo\u011fun g\u00fcncellenen tablolarda okunan sayfa say\u0131s\u0131, sat\u0131r say\u0131s\u0131na g\u00f6re beklenenden \u00e7ok y\u00fcksekse tablo \u015fi\u015fmi\u015f olabilir. \u00d6l\u00fc sat\u0131rlar da okunur. Bu durumun sebeplerini <a href=\"https:\/\/www.akifsen.com.tr\/blog\/2026\/10\/30\/postgresql-vacuum-table-bloat\/\">PostgreSQL&#8217;de tablo \u015fi\u015fmesi<\/a> yaz\u0131s\u0131nda anlatm\u0131\u015ft\u0131m.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Ger\u00e7ek bir \u00f6rnek: s\u0131ralama tuza\u011f\u0131<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Bir sipari\u015f listesi endpoint&#8217;i yava\u015ft\u0131. Sorgu basitti:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM orders\nWHERE status = 'PENDING'\nORDER BY created_at DESC\nLIMIT 50;<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Planda \u015fu vard\u0131:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Limit (actual time=1840.2..1840.3 rows=50 loops=1)\n  -&gt; Sort (actual time=1840.2..1840.2 rows=50 loops=1)\n       Sort Method: top-N heapsort  Memory: 41kB\n       -&gt; Seq Scan on orders (actual time=0.02..1702.1 rows=184220 loops=1)\n             Filter: (status = 'PENDING')\n             Rows Removed by Filter: 9815780<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Okumas\u0131 kolay: 10 milyon sat\u0131r okunmu\u015f, 9,8 milyonu at\u0131lm\u0131\u015f, kalan 184 bin sat\u0131r s\u0131ralanm\u0131\u015f ve ilk 50&#8217;si al\u0131nm\u0131\u015f. Toplam s\u00fcrenin neredeyse tamam\u0131 s\u0131ral\u0131 taramada.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>(status, created_at DESC)<\/code> \u00fczerine bir composite index eklendikten sonra plan \u015funa d\u00f6n\u00fc\u015ft\u00fc:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Limit (actual time=0.05..0.31 rows=50 loops=1)\n  -&gt; Index Scan using idx_orders_status_created on orders\n       (actual time=0.05..0.30 rows=50 loops=1)<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">S\u0131ralama ad\u0131m\u0131 kayboldu, \u00e7\u00fcnk\u00fc index sat\u0131rlar\u0131 zaten istenen s\u0131rada veriyor. Sorgu 50 sat\u0131r okuyup duruyor. 1,8 saniyeden 0,3 milisaniyeye.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Bu t\u00fcr liste sorgular\u0131nda sayfa ilerledik\u00e7e ayr\u0131 bir problem ba\u015flar. B\u00fcy\u00fck offset de\u011ferlerinin neden yava\u015flad\u0131\u011f\u0131n\u0131 <a href=\"https:\/\/www.akifsen.com.tr\/blog\/2026\/10\/12\/cursor-pagination-keyset-offset\/\">offset ve keyset pagination<\/a> yaz\u0131s\u0131nda ele alm\u0131\u015ft\u0131m.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Plan\u0131 okurken s\u0131k yap\u0131lan hatalar<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Y\u0131llar i\u00e7inde kendimin ve ekip arkada\u015flar\u0131m\u0131n yapt\u0131\u011f\u0131 birka\u00e7 hatay\u0131 not etmi\u015ftim:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Geli\u015ftirme ortam\u0131ndaki k\u00fc\u00e7\u00fck veriyle al\u0131nan plan\u0131 production&#8217;a genellemek. 1.000 sat\u0131rl\u0131k tabloda planlay\u0131c\u0131 bamba\u015fka bir yol se\u00e7er.<\/li>\n\n\n<li><code>cost<\/code> de\u011ferini milisaniye sanmak.<\/li>\n\n\n<li><code>loops<\/code> de\u011ferini g\u00f6rmeden d\u00fc\u011f\u00fcm s\u00fcresini toplam s\u00fcre sanmak.<\/li>\n\n\n<li>\u00d6nbellek etkisini hesaba katmadan, ikinci \u00e7al\u0131\u015ft\u0131rman\u0131n h\u0131z\u0131na g\u00fcvenmek.<\/li>\n\n\n<li>Parametreli sorgularda generic plan ile custom plan fark\u0131n\u0131 unutmak. Uygulaman\u0131n g\u00f6nderdi\u011fi prepared statement, elle yaz\u0131lan sorgudan farkl\u0131 bir plan kullanabilir.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Son madde \u00f6zellikle sinsi. Uygulamada yava\u015f olan sorgu, psql&#8217;de elle \u00e7al\u0131\u015ft\u0131r\u0131ld\u0131\u011f\u0131nda h\u0131zl\u0131 g\u00f6r\u00fcnebilir. B\u00f6yle bir durumda <code>auto_explain<\/code> eklentisini k\u0131sa s\u00fcreli\u011fine a\u00e7\u0131p uygulaman\u0131n ger\u00e7ekten kulland\u0131\u011f\u0131 plan\u0131 loglamak, tahmin y\u00fcr\u00fctmekten \u00e7ok daha h\u0131zl\u0131 sonu\u00e7 verir.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Plan okumay\u0131 kolayla\u015ft\u0131ran ara\u00e7lar<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Uzun planlar\u0131 metin olarak okumak yorucu olabilir. <code>EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)<\/code> \u00e7\u0131kt\u0131s\u0131n\u0131 g\u00f6rselle\u015ftiren ara\u00e7lar, en pahal\u0131 d\u00fc\u011f\u00fcmleri renklerle i\u015faretleyerek i\u015fi kolayla\u015ft\u0131r\u0131yor. explain.dalibo.com ve explain.depesz.com bu ama\u00e7la s\u0131k kullan\u0131lan iki ara\u00e7.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Hangi sorgular\u0131n incelenmeye de\u011fer oldu\u011funu bulmak ise ayr\u0131 bir ad\u0131m. B\u00fct\u00fcn sorgular\u0131n toplam s\u00fcresini ve \u00e7a\u011fr\u0131lma say\u0131s\u0131n\u0131 g\u00f6steren <code>pg_stat_statements<\/code> eklentisi, nereden ba\u015flanaca\u011f\u0131n\u0131 belirlemek i\u00e7in en iyi ba\u015flang\u0131\u00e7 noktas\u0131.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">PostgreSQL&#8217;in resmi dok\u00fcmantasyonundaki <a href=\"https:\/\/www.postgresql.org\/docs\/current\/using-explain.html\" target=\"_blank\" rel=\"noopener\">EXPLAIN kullan\u0131m\u0131 b\u00f6l\u00fcm\u00fc<\/a>, buradaki kavramlar\u0131n tamam\u0131n\u0131 \u00f6rneklerle anlat\u0131yor. Plan okumay\u0131 ilk kez \u00f6\u011frenecek birine \u00f6nerdi\u011fim ilk kaynak bu.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">\u00d6zet: EXPLAIN ANALYZE okurken kontrol listesi<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>ANALYZE<\/code> ve <code>BUFFERS<\/code> ile \u00e7al\u0131\u015ft\u0131r. Yazma sorgular\u0131nda transaction i\u00e7inde dene ve geri al.<\/li>\n\n\n<li>Zaman\u0131n topland\u0131\u011f\u0131 d\u00fc\u011f\u00fcm\u00fc bul. <code>loops<\/code> ile \u00e7arpmay\u0131 unutma.<\/li>\n\n\n<li>Tahmini ve ger\u00e7ek sat\u0131r say\u0131s\u0131n\u0131 kar\u015f\u0131la\u015ft\u0131r. B\u00fcy\u00fck fark varsa \u00f6nce istatistiklere bak.<\/li>\n\n\n<li><code>Rows Removed by Filter<\/code> y\u00fcksekse index&#8217;i sorguya g\u00f6re yeniden d\u00fc\u015f\u00fcn.<\/li>\n\n\n<li>Ayr\u0131 bir <code>Sort<\/code> d\u00fc\u011f\u00fcm\u00fc varsa, s\u0131ralamay\u0131 kar\u015f\u0131layan bir index&#8217;in m\u00fcmk\u00fcn olup olmad\u0131\u011f\u0131na bak.<\/li>\n\n\n<li>S\u00fcre yerine okunan sayfa say\u0131s\u0131yla kar\u015f\u0131la\u015ft\u0131rma yap.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Bir sorguyu h\u0131zland\u0131rmak \u00e7o\u011fu zaman yeni bir donan\u0131m veya daha b\u00fcy\u00fck bir <a href=\"https:\/\/www.akifsen.com.tr\/blog\/2026\/09\/28\/database-connection-pool-sizing\/\">connection pool<\/a> gerektirmez. Planlay\u0131c\u0131n\u0131n neden o yolu se\u00e7ti\u011fini anlamak, \u00e7\u00f6z\u00fcm\u00fcn yar\u0131s\u0131d\u0131r.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>EXPLAIN ANALYZE \u00e7\u0131kt\u0131s\u0131 ilk bak\u0131\u015fta karma\u015f\u0131k g\u00f6r\u00fcn\u00fcr. Plan d\u00fc\u011f\u00fcmleri, tahmini ve ger\u00e7ek sat\u0131r say\u0131s\u0131, buffers ve yava\u015f sorgunun as\u0131l sebebini bulma ad\u0131mlar\u0131.<\/p>\n","protected":false},"author":1,"featured_media":238,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-239","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\/239","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=239"}],"version-history":[{"count":1,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts\/239\/revisions"}],"predecessor-version":[{"id":278,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/posts\/239\/revisions\/278"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/media\/238"}],"wp:attachment":[{"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/media?parent=239"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/categories?post=239"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.akifsen.com.tr\/blog\/wp-json\/wp\/v2\/tags?post=239"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}