EXPLAIN: query plan পড়ে ধীর query খোঁজা

Query ধীর হলে আন্দাজ করা সময় নষ্ট। EXPLAIN দেখায় query plan: আপনার SQL চালাতে database যে ধাপগুলো বেছে নিয়েছে। এটি পড়লে বোঝা যায় সময় কোথায় যাচ্ছে।

চালান

EXPLAIN SELECT * FROM articles WHERE author_id = 5 ORDER BY created_at DESC LIMIT 10;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;   -- PostgreSQL: সত্যিই চালায় এবং আসল সময় দেখায়

অন্য database: MySQL-এ EXPLAIN (type column, যেখানে ALL মানে full scan), SQLite-এ EXPLAIN QUERY PLAN।

সাবধান: EXPLAIN ANALYZE query-টি সত্যিই চালায়। UPDATE বা DELETE-এর ক্ষেত্রে transaction-এ মুড়ে rollback করুন।

প্রধান scan-এর ধরন

• Sequential (full) scan: পুরো table পড়ে। ছোট table বা বেশিরভাগ row দরকার হলে ঠিক, বড় table-এ নির্দিষ্ট filter-এ খারাপ।
• Index scan: index দিয়ে row খুঁজে তারপর table পড়ে।
• Index-only scan: শুধু index থেকে উত্তর দেয় (covering index), সবচেয়ে দ্রুত।
• Bitmap scan: কয়েকটি index-এর ফল মেলায়, অনেক row মিললে প্রচলিত।

Join-এর পদ্ধতি

• Nested loop: এক পাশের প্রতিটি row-র জন্য অন্য পাশে মিল খোঁজে। Index ও কম row-তে চমৎকার।
• Hash join: একপাশের hash table বানায়। বড়, না-সাজানো input-এ ভালো।
• Merge join: দুটি সাজানো input মেলায়।

Index ছাড়া বিশাল table-এ nested loop ধীরতার ক্লাসিক কারণ।

কী দেখবেন

1. Cost (আনুমানিক, ইচ্ছামতো একক)। পরম সংখ্যা নয়, plan-গুলোর তুলনা করুন।
2. আনুমানিক বনাম আসল row। বড় পার্থক্য মানে planner ভুল statistics থেকে কাজ করছে। ANALYZE চালান।
3. প্রতিটি node-এর আসল সময়: সবচেয়ে ধীরটি খুঁজুন।
4. Filter-এ বাদ যাওয়া row: কোনো node ১০ লাখ row পড়ে ১০টি রাখলে আপনার ভালো index দরকার।
5. Sort ও temp file: disk-এ "external merge" মানে sort memory-তে ধরেনি।

সাধারণ সমাধান

Plan-এ articles-এ Filter: author_id = 5 সহ sequential scan, ৯,৯৯,০০০ row বাদ। Index দিন, আবার চালিয়ে index scan নিশ্চিত করুন:

CREATE INDEX idx_articles_author_created ON articles (author_id, created_at DESC);

এখন filter ও sort দুটোই index ব্যবহার করে।

অভ্যাস

• বাস্তব data পরিমাণে plan দেখুন। ছোট test database সমস্যা লুকায়।
• সবচেয়ে খারাপ query মোট সময় ধরে খুঁজতে slow query log (বা pg_stat_statements) চালু করুন।
• একবারে একটি জিনিস বদলান এবং আবার মাপুন।
• মনে রাখুন data বাড়লে সেরা plan বদলাতে পারে।

মূল কথা: EXPLAIN দিয়ে মাপুন, আনুমানিক ও আসল row তুলনা করুন, full scan ও বড় sort খুঁজুন, তারপর index দিয়ে বা query নতুন করে লিখে ঠিক করুন।

---

Backend ও Database সিরিজ, পর্ব ২ (performance ও operations): 1 নম্বর (মোট 6)।