4.4.15
Intuition Badi picture (WHY yeh exist karta hai)Jab tum SELECT ... likhte ho, tum database ko batate ho ki tumhe KYA chahiye, KAISE milega yeh nahi. Query planner (a.k.a. optimizer) DB ka woh hissa hai jo decide karta hai kaise . Woh choose kar sakta hai — poora table scan karna, index ke zariye jump karna, kisi ek tarike se sort karna ya alag order mein join karna. ==EXPLAIN== us decision ki khidki hai — yeh tumhe woh plan tree dikhata hai jo optimizer ne choose ki, aur uska estimated cost . Tum ise padh ke ek sawaal ka jawab dhoondthe ho: "Kya DB kuch bakawaas kar raha hai, aur kyun?"
Query plan ek physical operators (nodes) ka tree hota hai jise database tumhara result produce karne ke liye execute karta hai. Har node apne children se rows consume karta hai aur apne parent ko rows emit karta hai. Leaves data padhte hain (table scans, index scans); inner nodes use transform karte hain (joins, sorts, aggregates, filters).
Definition EXPLAIN vs EXPLAIN ANALYZE
==EXPLAIN== — plan + estimated cost/rows dikhata hai. Query run nahi karta.
==EXPLAIN ANALYZE== — query ko actually run karta hai aur estimated vs actual rows & time dikhata hai. Yeh debugging ka gold standard hai, kyunki estimate aur reality ke beech ka gap batata hai ki planner kahan galat tha .
Ek typical line kuch aisi dikhti hai:
Seq Scan on orders (cost=0.00..18334.00 rows=1000000 width=33)
(actual time=0.01..95.2 rows=1000000 loops=1)
Filter: (amount > 500)
Rows Removed by Filter: 200000
Ise decode karo:
Field
Meaning
Seq Scan on ordersoperator + uska table
cost=0.00..18334.00startup cost .. total cost (abstract units)
rows=1000000is node se estimated rows emit honge
width=33estimated bytes per row
actual time=0.01..95.2real ms: startup..total (sirf ANALYZE ke saath)
loops=1node kitni baar run hua
inside-out, bottom-up padho
Sabse zyada indented node conceptually pehle run hota hai (woh apne parent ko feed karta hai). Paisa/time usually leaves (data access) aur joins/sorts par kharch hota hai. Pehle wahan dekho.
Definition Startup cost & total cost
Startup cost = pehli row emit hone se pehle kiya gaya kaam. Ek Seq Scan row 1 almost immediately emit kar sakta hai (≈ 0 Sort ko kuch bhi emit karne se pehle saara input padhna padta hai, isliye uska startup cost zyada hota hai.Total cost = aakhri row emit karne ka kaam.
LIMIT ke liye yeh important hai! LIMIT 1 ke saath, low startup cost wala plan jeet jaata hai — chahe uska total cost zyada ho — kyunki tum ek row ke baad ruk jaate ho.
Intuition "Cost" actually kya count kar raha hai?DB pehle se wall-clock time nahi jaanta (hardware, cache par depend karta hai). Isliye woh abstract operations count karta hai aur har ek ko ek tunable constant se weight karta hai. Reference unit hai "disk se ek sequential page padhna = 1.0" .
Postgres yeh planner constants expose karta hai (defaults dikhaye gaye hain):
Constant
Default
Meaning
seq_page_cost1.0 ek page sequentially padhne ka cost
random_page_cost4.0 ek page randomly padhne ka cost
cpu_tuple_cost0.01 ek row process karne ka cost
cpu_operator_cost0.0025 ek operator/filter evaluate karne ka cost
Seq Scan cost derive karo. Ek sequential scan har page ek baar padhta hai aur har tuple process karta hai, har ek par filter apply karta hai:
cost seqscan = read all pages P ⋅ seq_page_cost + emit each row T ⋅ cpu_tuple_cost + run filter once per row T ⋅ cpu_operator_cost jahan P T
Ab Index Scan. Ek index scan ek B-tree follow karta hai (height ≈ log random page hits karke fetch karta hai:
C idx ≈ tree descent log 2 ( T ) ⋅ c op + random heap fetches S ⋅ random_page_cost + S ⋅ c tuple jahan S selected rows ki sankhya.
Intuition Poora trade-off ek line meinIndex scan "saare P S sirf tab jeet ta hai jab S P Seq Scan choose karta hai, chahe index exist karta ho.
Definition Selectivity & statisticsSelectivity woh estimated fraction hai jo ek predicate rows mein se rakhta hai, 0 ≤ s ≤ 1 = s ⋅ T s statistics se jo ANALYZE collect karta hai aur pg_statistic mein store hoti hain: distinct values ki sankhya, most-common-values list, aur ek histogram.
Planner do key formulas use karta hai:
s = ( col = v ) ≈ n distinct 1 s < ( col < v ) ≈ max − min v − min (from histogram)
Intuition Estimates KYUN galat hote hain (aur ANALYZE kyun zaroori hai)Equality selectivity assume karta hai ki values uniformly distributed hain. Agar ek value 90% rows mein appear karti hai lekin stats stale hain, planner sochta hai s = 1/ n ANALYZE chalao; EXPLAIN ANALYZE mein rows=10 ... actual rows=900000 check karo — ek bada estimation error bure plans ka #1 cause hai.
Worked example Example 1 — Seq Scan ka costTable orders: P = 18 , 334 T = 1 , 000 , 000 amount > 500.
C = 18334 ( 1.0 ) + 1 0 6 ( 0.01 + 0.0025 ) = 18334 + 12500 = 30834 Yeh step kyun? Pages × seq_page_cost I/O deta hai; rows × (cpu_tuple_cost + cpu_operator_cost) har row emit aur filter karne ka CPU deta hai. Postgres cost=0.00..30834.00 print karta.
Worked example Example 2 — Index vs Seq, selectivity se decide karnaSame table. Query: WHERE customer_id = 42. Stats kehte hain customer_id mein n distinct = 50 , 000 s = 1/50000 = 2 × 1 0 − 5 S = 1 0 6 ⋅ s = 20 ≈ log 2 ( 1 0 6 ) ⋅ 0.0025 + 20 ⋅ 4.0 + 20 ⋅ 0.01 ≈ 0.05 + 80 + 0.2 ≈ 80 80 ≪ 30834 , toh planner Index Scan choose karta hai.
Yeh step kyun? Sirf 20 random page reads vs 18,334 sequential reads — randomness penalty (4×), 1000× kam pages padhne ke saamne dwarf ho jaati hai.
Worked example Example 3 — Jab index HAARTA haiQuery: WHERE status = 'active' jahan 80% rows active hain → S = 800 , 000 ≈ 800000 ⋅ 4.0 = 3 , 200 , 000 100× worse .
Yeh step kyun? 800k rows ko random page access se fetch karna catastrophic hai; poora table sequentially padhna kahin sasta hai. Planner sahi tarike se index ignore karta hai. (Isliye log galti se sochte hain "mera index use nahi ho raha" — yeh ek feature hai.)
Common mistake "Index hamesha queries faster banata hai — agar use nahi ho raha, kuch tuta hua hai."Kyun sahi lagta hai: indexes ko magic speed button ki tarah becha jaata hai; tumne ek add kiya, toh use hona chahiye .
Fix: indexes sirf low selectivity par help karte hain (kam matching rows). Aise predicates ke liye jo rows ka bada fraction match karte hain, sequential I/O ke saath ek sequential scan hazaaron random fetches se better hai (Example 3). Planner usually sahi hota hai — index force karne se pehle cost model par trust karo .
cost= matlab query slow hai."
Kyun sahi lagta hai: "cost" time jaisa lagta hai.
Fix: cost abstract units mein hai, sirf ek hi query ke alternative plans ke beech compare karo, same machine par. Real time ke liye EXPLAIN ANALYZE use karo. Cost 100 wali do queries bilkul alag wall-clock time le sakti hain.
Common mistake "Main plan ko top-to-bottom code jaisa padhunga."Kyun sahi lagta hai: hum sab kuch aise hi padhte hain.
Fix: execution bottom-up / inside-out flow karta hai. Children parents ko feed karte hain. Top node aakhri step hai (final output), sabse zyada indented leaves pehle run hote hain.
Common mistake "Estimated rows = actual rows, toh meri stats theek hain."Kyun sahi lagta hai: tumne query run ki aur sahi answer mila.
Fix: correctness aur good estimates alag cheezein hain. Ek query sahi ho sakti hai phir bhi rows=5 ... actual rows=500000 show kare. Woh gap planner ko ek bure plan ki taraf le jaata hai. ANALYZE / CREATE STATISTICS se fix karo.
Plain EXPLAIN kya dikhata hai vs EXPLAIN ANALYZE? EXPLAIN run kiye bina estimated cost/rows dikhata hai; EXPLAIN ANALYZE actually execute karta hai aur estimated vs actual rows aur real time dikhata hai.
cost=0.00..18334.00 mein do numbers kya hain?Startup cost (0.00 = pehli row se pehle kaam) aur total cost (18334 = aakhri row tak kaam).
Sort node ka startup cost zyada kyun hota hai? Pehli sorted row emit karne se pehle usse saari input rows consume karni padti hain.
Seq Scan cost formula likhو. C = P ⋅ c se q + T ( c t u pl e + c o p )
Default seq_page_cost aur random_page_cost? 1.0 aur 4.0 — random page reads sequential se 4× mahange hote hain.
Index scan seq scan ko kab beat karta hai? Jab selectivity low ho (kam matching rows S P
Equality selectivity kaise estimate hoti hai? s ≈ 1/ n d i s t in c t
Ek node ka estimated output rows = ? selectivity × input rows (s ⋅ T
Estimates ke liye use hone wali statistics refresh karne ka command kaunsa hai? ANALYZE (pg_statistic ko n_distinct, MCVs, histogram se populate karta hai).
Postgres plan tree kis direction mein padhte hain? Bottom-up / inside-out: sabse zyada indented leaf nodes pehle execute hote hain aur apne parents ko feed karte hain.
Ek node rows=10 ... actual rows=900000 dikhata hai. Diagnosis kya hai? Severe misestimation (stale/insufficient stats); bure plan ka likely cause — re-ANALYZE karo.
Kya cost= alag queries/machines mein compare kiya ja sakta hai? Nahi — sirf same query ke alternative plans ke beech same config par; yeh ek abstract unit hai, time nahi.
Recall Feynman: ek 12-saal ke bachche ko samjhaoTum ek giant library se "dragons ke baare mein saari kitaabein" maangte ho. Librarian ya toh har shelf ke paas se chalna choose kar sakta hai (slow par steady), ya card catalog use karke seedha dragon books par jump karna (fast agar sirf kuch hi hain). EXPLAIN tumhara woh plan dekhna hai jo librarian shuru karne se pehle banata hai: "Kya tum har shelf par chaloge? Tumhare khayal mein kitni kitaabein milegi?" Agar librarian guess karta hai "2 kitaabein" lekin actually 900,000 hain, unhone ek bakwaas plan banaya — aur tum unhe bolte ho ki shelves par kya hai phir se count karo (yahi hai ANALYZE). "Cost" bas librarian ka andaza hai ki trip kitni thaka dene wali hogi, minutes nahi.
Mnemonic Cost trade-off yaad karo"SIRS" — S equential I s R eliable, random is S pendy.
Aur reading order ke liye: "Leaves Lead" — sabse zyada indented leaf nodes pehle lead (execute) karte hain.
B-Tree Indexes — index scans ka cost kya hota hai (tree descent + heap fetch)Database Statistics & ANALYZE — selectivity aur row estimates kahan se aate hainJoin Algorithms — Nested Loop, Hash, Merge — inner nodes jinke costs EXPLAIN reveal karta haiSelectivity & Cardinality Estimation — rows= ke peeche ka mathQuery Optimizer — woh component jo woh plan produce karta hai jo EXPLAIN print karta haiSequential vs Random I/O — physical reason kyun random_page_cost > seq_page_costLIMIT and Pagination — kyun startup cost matter karta hai jab tum jaldi rok dete ho
reveals estimate vs actual
