EXPLAIN আর কোয়েরি অপ্টিমাইজেশন

CREATE TABLE events (
    event_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    event_type  TEXT    NOT NULL,
    product_id  INTEGER NOT NULL,
    event_day   TEXT    NOT NULL,
    amount      INTEGER NOT NULL
);

INSERT INTO events (event_id, customer_id, event_type, product_id, event_day, amount)
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 200000)
SELECT i,
       (i * 7919) % 5000 + 1,
       CASE WHEN i % 20 = 0 THEN 'purchase' WHEN i % 5 = 0 THEN 'cart' ELSE 'view' END,
       (i * 31) % 11 + 1,
       date('2026-01-01', '+' || (i % 181) || ' days'),
       (i * 37) % 5000 + 100
FROM n;

import sqlite3

from sqlhelp import con, show


def plan(sql, params=()):
    """SQLite-এর প্ল্যান ইনডেন্ট করা গাছ হিসেবে প্রিন্ট করে।"""
    fresh = sqlite3.connect("shop.db")      # নতুন কানেকশন সবসময় সর্বশেষ ইনডেক্সগুলো দেখে
    rows = fresh.execute("EXPLAIN QUERY PLAN " + sql, params).fetchall()
    fresh.close()
    depth = {0: -1}
    for step_id, parent, _, detail in rows:
        depth[step_id] = depth[parent] + 1
        print("  " * depth[step_id] + detail)


def count_steps(sql, params=()):
    """একটা স্টেটমেন্ট চালায়; ফেরত দেয় (কয়টা সারি এল, SQLite কয়টা বাইটকোড ধাপ চালাল)।"""
    steps = 0

    def tick():
        nonlocal steps
        steps += 1
        return 0

    con.set_progress_handler(tick, 1)
    rows = con.execute(sql, params).fetchall()
    con.set_progress_handler(None, 1)
    return len(rows), steps


def work(sql, params=()):
    rows, steps = count_steps(sql, params)
    print(f"{rows} rows, {steps:,} steps")

plan("""
SELECT c.name, COUNT(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id
ORDER BY orders DESC
""")

SCAN o
SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
USE TEMP B-TREE FOR GROUP BY
USE TEMP B-TREE FOR ORDER BY

-- PostgreSQL
CREATE TABLE events AS
SELECT i AS event_id,
       (i * 7919) % 5000 + 1 AS customer_id,
       CASE WHEN i % 20 = 0 THEN 'purchase' WHEN i % 5 = 0 THEN 'cart' ELSE 'view' END AS event_type,
       (i * 31) % 11 + 1 AS product_id,
       DATE '2026-01-01' + (i % 181) AS event_day,
       (i * 37) % 5000 + 100 AS amount
FROM generate_series(1, 200000) AS i;
ALTER TABLE events ADD PRIMARY KEY (event_id);
ANALYZE events;
SET max_parallel_workers_per_gather = 0;

EXPLAIN SELECT * FROM events WHERE customer_id = 42;

+-----------------------------------------------------------+
| QUERY PLAN                                                |
+-----------------------------------------------------------+
| Seq Scan on events  (cost=0.00..3972.00 rows=40 width=25) |
|   Filter: (customer_id = 42)                              |
+-----------------------------------------------------------+

-- PostgreSQL
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM events WHERE customer_id = 42;

CREATE INDEX idx_events_customer ON events (customer_id);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM events WHERE customer_id = 42;

+---------------------------------------------+
| QUERY PLAN                                  |
+---------------------------------------------+
| Seq Scan on events (actual rows=40 loops=1) |
|   Filter: (customer_id = 42)                |
|   Rows Removed by Filter: 199960            |
+---------------------------------------------+
+-------------------------------------------------------------------------+
| QUERY PLAN                                                              |
+-------------------------------------------------------------------------+
| Bitmap Heap Scan on events (actual rows=40 loops=1)                     |
|   Recheck Cond: (customer_id = 42)                                      |
|   Heap Blocks: exact=40                                                 |
|   ->  Bitmap Index Scan on idx_events_customer (actual rows=40 loops=1) |
|         Index Cond: (customer_id = 42)                                  |
+-------------------------------------------------------------------------+

-- MySQL
SET SESSION cte_max_recursion_depth = 200000;
CREATE TABLE events (
    event_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    event_type  VARCHAR(10) NOT NULL,
    product_id  INT NOT NULL,
    event_day   DATE NOT NULL,
    amount      INT NOT NULL
);
INSERT INTO events
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 200000)
SELECT i,
       (i * 7919) % 5000 + 1,
       CASE WHEN i % 20 = 0 THEN 'purchase' WHEN i % 5 = 0 THEN 'cart' ELSE 'view' END,
       (i * 31) % 11 + 1,
       DATE '2026-01-01' + INTERVAL (i % 181) DAY,
       (i * 37) % 5000 + 100
FROM n;

import os

import pymysql

mysql = pymysql.connect(
    host=os.environ["MYSQL_HOST"], port=int(os.environ["MYSQL_PORT"]),
    user=os.environ["MYSQL_USER"], password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"], autocommit=True,
)
cur = mysql.cursor(pymysql.cursors.DictCursor)
cur.execute("ANALYZE TABLE events")


def mysql_plan(sql):
    cur.execute("EXPLAIN " + sql)
    for row in cur.fetchall():
        info = {k: row[k] for k in ("table", "type", "key", "rows", "Extra")}
        info["rows"] = round(info["rows"], 1 - len(str(info["rows"])))   # কিছু পেজ নমুনা হিসেবে পড়ে করা অনুমান: একটা সার্থক অঙ্ক রাখুন
        print(info)


mysql_plan("SELECT * FROM events WHERE customer_id = 42")
cur.execute("CREATE INDEX idx_events_customer ON events (customer_id)")
mysql_plan("SELECT * FROM events WHERE customer_id = 42")

{'table': 'events', 'type': 'ALL', 'key': None, 'rows': 200000, 'Extra': 'Using where'}
{'table': 'events', 'type': 'ref', 'key': 'idx_events_customer', 'rows': 40, 'Extra': None}

CREATE INDEX idx_events_day ON events (event_day);

pairs = [
    ("SELECT COUNT(*) FROM events WHERE substr(event_day, 1, 7) = '2026-03'",
     "SELECT COUNT(*) FROM events WHERE event_day >= '2026-03-01' AND event_day < '2026-04-01'"),
    ("SELECT COUNT(*) FROM events WHERE date(event_day, '+7 days') > '2026-07-01'",
     "SELECT COUNT(*) FROM events WHERE event_day > date('2026-07-01', '-7 days')"),
]
for slow, fast in pairs:
    for sql in (slow, fast):
        plan(sql)
        work(sql)
    print()

SCAN events USING COVERING INDEX idx_events_day
1 rows, 834,361 steps
SEARCH events USING COVERING INDEX idx_events_day (event_day>? AND event_day<?)
1 rows, 102,784 steps

SCAN events USING COVERING INDEX idx_events_day
1 rows, 806,640 steps
SEARCH events USING COVERING INDEX idx_events_day (event_day>?)
1 rows, 13,271 steps

-- PostgreSQL
CREATE TABLE app_users AS
SELECT i AS user_id, 'user' || i || '@example.com' AS email
FROM generate_series(1, 100000) AS i;
CREATE INDEX idx_app_users_email ON app_users (email text_pattern_ops);
CREATE INDEX idx_app_users_email_lower ON app_users (lower(email));
ANALYZE app_users;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM app_users WHERE email LIKE 'user4242@%';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM app_users WHERE email LIKE '%4242@example.com';
EXPLAIN (COSTS OFF)
SELECT * FROM app_users WHERE lower(email) = 'user4242@example.com';

+----------------------------------------------------------------------------------+
| QUERY PLAN                                                                       |
+----------------------------------------------------------------------------------+
| Index Scan using idx_app_users_email on app_users (actual rows=1 loops=1)        |
|   Index Cond: ((email ~>=~ 'user4242@'::text) AND (email ~<~ 'user4242A'::text)) |
|   Filter: (email ~~ 'user4242@%'::text)                                          |
+----------------------------------------------------------------------------------+
+------------------------------------------------+
| QUERY PLAN                                     |
+------------------------------------------------+
| Seq Scan on app_users (actual rows=10 loops=1) |
|   Filter: (email ~~ '%4242@example.com'::text) |
|   Rows Removed by Filter: 99990                |
+------------------------------------------------+
+-------------------------------------------------------------+
| QUERY PLAN                                                  |
+-------------------------------------------------------------+
| Index Scan using idx_app_users_email_lower on app_users     |
|   Index Cond: (lower(email) = 'user4242@example.com'::text) |
+-------------------------------------------------------------+

correlated = """
SELECT c.customer_id,
       (SELECT COUNT(*) FROM events e
        WHERE e.customer_id = c.customer_id AND e.event_type = 'purchase') AS purchases
FROM customers c ORDER BY c.customer_id"""

grouped_first = """
SELECT c.customer_id, COALESCE(p.purchases, 0) AS purchases
FROM customers c
LEFT JOIN (SELECT customer_id, COUNT(*) AS purchases
           FROM events WHERE event_type = 'purchase'
           GROUP BY customer_id) p ON p.customer_id = c.customer_id
ORDER BY c.customer_id"""

for sql in (correlated, grouped_first):
    plan(sql)
    work(sql)
    print()

SCAN c
CORRELATED SCALAR SUBQUERY 1
  SCAN e
8 rows, 6,400,802 steps

MATERIALIZE p
  SCAN events
  USE TEMP B-TREE FOR GROUP BY
SCAN c
BLOOM FILTER ON p (customer_id=?)
SEARCH p USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN
8 rows, 715,919 steps

CREATE INDEX idx_events_customer ON events (customer_id, event_type);

plan(correlated)
work(correlated)

SCAN c
CORRELATED SCALAR SUBQUERY 1
  SEARCH e USING COVERING INDEX idx_events_customer (customer_id=? AND event_type=?)
8 rows, 360 steps

offset_page = "SELECT * FROM events ORDER BY event_id LIMIT 10 OFFSET 150000"
keyset_page = "SELECT * FROM events WHERE event_id > ? ORDER BY event_id LIMIT 10"

plan(offset_page)
work(offset_page)
plan(keyset_page, (150000,))
work(keyset_page, (150000,))

SCAN events
10 rows, 300,110 steps
SEARCH events USING INTEGER PRIMARY KEY (rowid>?)
10 rows, 101 steps

queries = []
con.set_trace_callback(queries.append)

# N+1: অর্ডারগুলোর জন্য একটা কোয়েরি, তারপর প্রতিটা অর্ডারের কাস্টমারের জন্য একটা করে
orders = con.execute("SELECT order_id, customer_id FROM orders ORDER BY order_id").fetchall()
names = {}
for order_id, customer_id in orders:
    names[order_id] = con.execute(
        "SELECT name FROM customers WHERE customer_id = ?", (customer_id,)).fetchone()[0]
print("N+1 version:", len(queries), "queries")

queries.clear()
rows = con.execute("""
    SELECT o.order_id, c.name
    FROM orders o JOIN customers c ON c.customer_id = o.customer_id
    ORDER BY o.order_id""").fetchall()
print("join version:", len(queries), "query")
print(dict(rows) == names)
con.set_trace_callback(None)

N+1 version: 15 queries
join version: 1 query
True

sql = "SELECT COUNT(*) FROM events WHERE event_type = 'view' AND event_day = '2026-03-15'"
con.execute("CREATE INDEX idx_events_type ON events (event_type)")
plan(sql)
work(sql)

con.execute("ANALYZE")
show("SELECT tbl, idx, stat FROM sqlite_stat1 WHERE tbl = 'events' ORDER BY idx")
plan(sql)
work(sql)

SEARCH events USING INDEX idx_events_type (event_type=?)
1 rows, 800,900 steps
+--------+---------------------+--------------+
| tbl    | idx                 | stat         |
+--------+---------------------+--------------+
| events | idx_events_customer | 200000 40 40 |
| events | idx_events_day      | 200000 1105  |
| events | idx_events_type     | 200000 66667 |
+--------+---------------------+--------------+
SEARCH events USING INDEX idx_events_day (event_day=?)
1 rows, 6,427 steps

-- PostgreSQL
SELECT attname, n_distinct
FROM pg_stats
WHERE tablename = 'events' AND attname IN ('event_type', 'product_id', 'event_day')
ORDER BY attname;

CREATE INDEX idx_events_type ON events (event_type);
EXPLAIN (COSTS OFF) SELECT SUM(amount) FROM events WHERE event_type = 'purchase';
EXPLAIN (COSTS OFF) SELECT SUM(amount) FROM events WHERE event_type = 'view';

+------------+------------+
| attname    | n_distinct |
+------------+------------+
| event_day  |      181.0 |
| event_type |        3.0 |
| product_id |       11.0 |
+------------+------------+
+-----------------------------------------------------------+
| QUERY PLAN                                                |
+-----------------------------------------------------------+
| Aggregate                                                 |
|   ->  Bitmap Heap Scan on events                          |
|         Recheck Cond: (event_type = 'purchase'::text)     |
|         ->  Bitmap Index Scan on idx_events_type          |
|               Index Cond: (event_type = 'purchase'::text) |
+-----------------------------------------------------------+
+---------------------------------------------+
| QUERY PLAN                                  |
+---------------------------------------------+
| Aggregate                                   |
|   ->  Seq Scan on events                    |
|         Filter: (event_type = 'view'::text) |
+---------------------------------------------+

-- MySQL
SELECT @@slow_query_log, @@long_query_time;

+------------------+-------------------+
| @@slow_query_log | @@long_query_time |
+------------------+-------------------+
|                0 |              10.0 |
+------------------+-------------------+

-- PostgreSQL
SHOW log_min_duration_statement;

+----------------------------+
| log_min_duration_statement |
+----------------------------+
| -1                         |
+----------------------------+

# MySQL: my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 0.5          # সেকেন্ড
log_queries_not_using_indexes = 1

# PostgreSQL: postgresql.conf
log_min_duration_statement = 500   # মিলিসেকেন্ড
shared_preload_libraries = 'pg_stat_statements'

-- PostgreSQL (সার্ভার চালুর সময় pg_stat_statements লোড থাকতে হবে)
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 2) AS mean_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

import statistics
import time


def benchmark(sql, params=(), repeat=20):
    con.execute(sql, params).fetchall()                  # ওয়ার্ম-আপ, গোনা হয় না
    times = []
    for _ in range(repeat):
        start = time.perf_counter()
        con.execute(sql, params).fetchall()
        times.append((time.perf_counter() - start) * 1000)
    return statistics.median(times)


print(f"OFFSET page: {benchmark(offset_page):.3f} ms (median of 20)")
print(f"keyset page: {benchmark(keyset_page, (150000,)):.3f} ms (median of 20)")

OFFSET page: 1.513 ms (median of 20)
keyset page: 0.011 ms (median of 20)

# ১. সহজ: পাটিগণিতটা ধ্রুবকের দিকে সরিয়ে দিন
con.execute("CREATE INDEX idx_events_amount ON events (amount)")
plan("SELECT COUNT(*) FROM events WHERE amount * 2 > 9000")
plan("SELECT COUNT(*) FROM events WHERE amount > 4500")

# ২. মাঝারি: (event_day, event_id)-এর ওপর row-value তুলনা।
# SQLite-এর প্রতিটা ইনডেক্সের শেষে rowid থাকে, তাই idx_events_day আসলে (event_day, event_id);
# PostgreSQL বা MySQL-এ বানাতে হবে: CREATE INDEX idx_events_day_id ON events (event_day, event_id)
page = """SELECT event_id, event_day, event_type FROM events
          WHERE (event_day, event_id) > (?, ?)
          ORDER BY event_day, event_id LIMIT 5"""
plan(page, ("2026-03-01", 100))
show(page, ("2026-03-01", 100))

# ৩. কঠিন
queries = []
con.set_trace_callback(queries.append)
rows = con.execute("""
    SELECT p.product_id, p.name, COALESCE(SUM(oi.quantity), 0) AS sold
    FROM products p
    LEFT JOIN order_items oi ON oi.product_id = p.product_id
    GROUP BY p.product_id, p.name
    ORDER BY p.product_id""").fetchall()
con.set_trace_callback(None)
print(len(queries), "query,", len(rows), "products")