"""05_brin_timeseries.py —— BRIN 索引在时序数据上的体积优势

基于 init.sql 的 ch6_logs（50 万行，按 created_at 顺序追加）：
  1) 对比 B-Tree 与 BRIN 索引的体积
  2) 同一范围查询下的执行计划和耗时
  3) 调 BRIN 的 pages_per_range 看对体积/速度影响
"""
from __future__ import annotations

from _common import connect, explain_analyze, section, summarize_plan, time_query


def sizes(cur) -> None:
    cur.execute(
        """
        SELECT indexname,
               pg_size_pretty(pg_relation_size(format('%I', indexname)::regclass)) AS size
        FROM pg_indexes
        WHERE tablename = 'ch6_logs'
        ORDER BY indexname
        """
    )
    rows = cur.fetchall()
    print("  当前 ch6_logs 索引：")
    for idx, sz in rows:
        print(f"    {idx:40s}  {sz}")


def bench(cur, label: str) -> None:
    SQL = """
        SELECT COUNT(*) FROM ch6_logs
        WHERE created_at >= %s AND created_at < %s
    """
    params = ("2024-03-01", "2024-03-07")
    t = time_query(cur, SQL, params, runs=3)
    p = summarize_plan(explain_analyze(cur, SQL, params))
    print(f"  [{label}]  node={p['node']:20s}  actual={p['actual_ms']:.3f}ms  "
          f"best={t:.2f}ms  buffers(hit={p['shared_hit']}, read={p['shared_read']})")


def main() -> None:
    with connect() as conn:
        with conn.cursor() as cur:
            cur.execute("DROP INDEX IF EXISTS idx_ch6_logs_time_btree")
            cur.execute("DROP INDEX IF EXISTS idx_ch6_logs_time_brin")
            cur.execute("DROP INDEX IF EXISTS idx_ch6_logs_time_brin_dense")

            section("1. 基准：无索引")
            bench(cur, "无索引(Seq Scan)")

            section("2. B-Tree 索引")
            cur.execute("CREATE INDEX idx_ch6_logs_time_btree ON ch6_logs (created_at)")
            cur.execute("ANALYZE ch6_logs")
            sizes(cur)
            bench(cur, "B-Tree")
            cur.execute("DROP INDEX idx_ch6_logs_time_btree")

            section("3. BRIN 索引（默认 pages_per_range=128）")
            cur.execute("CREATE INDEX idx_ch6_logs_time_brin ON ch6_logs USING BRIN (created_at)")
            cur.execute("ANALYZE ch6_logs")
            sizes(cur)
            bench(cur, "BRIN 默认")

            section("4. BRIN 调密集（pages_per_range=8）—— 更准但更大")
            cur.execute("DROP INDEX idx_ch6_logs_time_brin")
            cur.execute(
                "CREATE INDEX idx_ch6_logs_time_brin_dense ON ch6_logs "
                "USING BRIN (created_at) WITH (pages_per_range = 8)"
            )
            cur.execute("ANALYZE ch6_logs")
            sizes(cur)
            bench(cur, "BRIN pages=8")

            print("\n观察要点：")
            print("  * BRIN 索引可能只有 B-Tree 的 1/1000 大小。")
            print("  * 数据按 created_at 物理有序时，BRIN 查询速度接近 B-Tree。")
            print("  * 数据物理乱序时（比如频繁乱序 INSERT），BRIN 会退化成 Seq Scan。")

            cur.execute("DROP INDEX idx_ch6_logs_time_brin_dense")
            conn.commit()


if __name__ == "__main__":
    main()