graph: harden Postgres hot paths with benchmark-backed indexes

[msaad00]

Goal

Use the measured graph benchmark artifacts to harden Postgres hot paths.

Scope

• Review plans for search, node detail, paths, diff, and bounded traversal.
• Add safe indexes/query changes where evidence shows avoidable scans.
• Keep SQLite behavior unchanged.

Acceptance

• Updated plans show improved hot path behavior or document why no change is needed.
• Bench artifacts remain reproducible.

Verification

• uv run pytest -q targeted Postgres/graph tests
• Run docs/perf graph benchmark Postgres commands from Add measured graph benchmark evidence #2544

[msaad00]

Closing as complete after validation against current main. Evidence: the Postgres graph hot-path indexes and migration are present in src/agent_bom/api/postgres_graph.py and deploy/supabase/postgres/alembic/versions/20260513_01_graph_hot_path_indexes.py, with coverage in tests/test_postgres_schema.py, tests/test_postgres_migrations.py, and tests/test_postgres_store.py.