"""
04_fillfactor_hot.py
--------------------
对比 fillfactor=100 与 fillfactor=70 时 HOT 更新的比例。

实验思路：
  1. ch9_hot_full / ch9_hot_70 两表结构相同，仅 fillfactor 不同（init.sql 创建）。
  2. 各跑同样次数的 UPDATE（不修改索引列）。
  3. 通过 pg_stat_user_tables 中 n_tup_upd / n_tup_hot_upd 看 HOT 命中率。
  4. 通过 pg_relation_size 看物理体积差异。
"""

import time

import psycopg

CONN_STR = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
N_UPDATES = 10  # 每行更新多少次


def reset(cur: psycopg.Cursor, table: str, fill: int) -> None:
    cur.execute(f"DROP TABLE IF EXISTS {table};")
    cur.execute(
        f"CREATE TABLE {table} (id INT PRIMARY KEY, val INT) "
        f"WITH (fillfactor = {fill});"
    )
    cur.execute(
        f"INSERT INTO {table} SELECT g, 0 FROM generate_series(1, 5000) g;"
    )
    cur.execute(f"VACUUM ANALYZE {table};")


def stats(cur: psycopg.Cursor, table: str) -> dict:
    cur.execute(
        """
        SELECT n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup
        FROM pg_stat_user_tables
        WHERE relname = %s;
        """,
        (table,),
    )
    upd, hot, live, dead = cur.fetchone()
    cur.execute(f"SELECT pg_relation_size(%s);", (table,))
    size = cur.fetchone()[0]
    return {
        "upd": upd or 0,
        "hot": hot or 0,
        "live": live or 0,
        "dead": dead or 0,
        "size_kb": size // 1024,
    }


def run_updates(cur: psycopg.Cursor, table: str) -> None:
    for i in range(N_UPDATES):
        cur.execute(f"UPDATE {table} SET val = val + 1;")


def main() -> None:
    with psycopg.connect(CONN_STR, autocommit=True) as conn, conn.cursor() as cur:
        print("=" * 70)
        print("  fillfactor 与 HOT 更新比例对比实验")
        print("=" * 70)

        for table, fill in (("ch9_hot_full", 100), ("ch9_hot_70", 70)):
            reset(cur, table, fill)

        # 等 stats collector 写入
        time.sleep(1)
        before = {t: stats(cur, t) for t in ("ch9_hot_full", "ch9_hot_70")}

        for table in ("ch9_hot_full", "ch9_hot_70"):
            print(f"\n>>> 对 {table}（fillfactor={'100' if table.endswith('full') else '70'}） 运行 {N_UPDATES} 次全表 UPDATE ...")
            t0 = time.perf_counter()
            run_updates(cur, table)
            print(f"    耗时 {time.perf_counter()-t0:.2f}s")

        time.sleep(2)  # 等待统计信息刷新
        after = {t: stats(cur, t) for t in ("ch9_hot_full", "ch9_hot_70")}

        print("\n" + "─" * 70)
        print(
            f"  {'table':<14}{'fillfactor':>11}{'updates':>10}"
            f"{'hot_upd':>10}{'hot %':>10}{'dead':>8}{'size(KB)':>10}"
        )
        print("─" * 70)
        for table, fill in (("ch9_hot_full", 100), ("ch9_hot_70", 70)):
            d_upd = after[table]["upd"] - before[table]["upd"]
            d_hot = after[table]["hot"] - before[table]["hot"]
            ratio = (d_hot / d_upd * 100) if d_upd else 0
            print(
                f"  {table:<14}{fill:>11}{d_upd:>10}{d_hot:>10}"
                f"{ratio:>9.1f}%{after[table]['dead']:>8}{after[table]['size_kb']:>10}"
            )

        print(
            "\n解读：\n"
            "  · fillfactor=70 留了 30% 空闲，UPDATE 时新版本能放在同页 →\n"
            "    HOT 命中率明显升高，索引几乎不用动，写放大降低。\n"
            "  · fillfactor=100 把页塞满，UPDATE 不得不把新版本放到其它页 →\n"
            "    HOT 比例低，索引更新频繁，物理体积增长更快。\n"
        )


if __name__ == "__main__":
    main()