The benchmark tests a diagnostic query, not a seventh business report: for one immutable asset ID, find each visible finding's latest event before a fixed cutoff. Filtering on findings.asset_id before event selection preserves meaning because asset association is fixed. Filtering old events by owner, status, or severity before choosing latest state would change the answer.
The single change is idx_finding_events_latest on (finding_id, occurred_at DESC, event_id DESC) in sql/003_indexes.sql. Load a fresh portfolio dataset without this index. The command refuses to run if it already exists. It runs ANALYZE, hashes the complete query result, captures a baseline JSON EXPLAIN (ANALYZE, BUFFERS) plan, performs three warmups and ten timed runs, creates the index, checks result equivalence, then repeats the same measurements. Output includes every execution and planning time, median and min/max spread, table counts, database content hash, server configuration, hardware, and index size.
python -m security_analytics.cli generate --profile portfolio --seed 42 \
--start 2025-10-01T00:00:00Z --end 2026-02-01T00:00:00Z \
--out data/generated/portfolio-seed-42
python -m security_analytics.cli reset-demo --confirm security_analytics
python -m security_analytics.cli load --input data/generated/portfolio-seed-42
python -m security_analytics.cli benchmark \
--cutoff 2026-01-16T00:00:00Z --asset-id 1 \
--dataset-manifest data/generated/portfolio-seed-42/manifest.json \
--out artifacts/benchmarks/portfolioCompare measured execution milliseconds, not planner cost. A positive median change is not automatically reliable when run spreads overlap; inspect both plans. The index consumes storage and adds write maintenance. A broad report scan may not benefit even if the selective diagnostic query does.
CI run 41809088427 measured commit 8bd60f60df04f89d866b3987ff50dd8b63b66284 on November 4, 2026. The portfolio dataset contained 2,000 assets, 25,000 findings, and 77,119 finding events. The exact seed, range, row counts, file hashes, SQL, and database content hash are preserved in runs.json.
| Metric | Before finding-event index | After index |
|---|---|---|
| Median execution | 55.5995 ms | 0.058 ms |
| Min–max execution | 54.061–64.688 ms | 0.057–0.060 ms |
| Query shared-buffer hits | 11,151 | 63 |
| Returned rows | 12 | 12 |
The median reduction was 99.8957% for this diagnostic query. Three warmups preceded ten measured runs in each condition. Execution times below exclude planning, network/client latency, and index construction; planning times are separately retained in the raw artifact.
| Run | Before, ms | After, ms |
|---|---|---|
| 1 | 54.843 | 0.060 |
| 2 | 55.420 | 0.060 |
| 3 | 55.381 | 0.058 |
| 4 | 57.354 | 0.058 |
| 5 | 56.902 | 0.057 |
| 6 | 54.061 | 0.059 |
| 7 | 55.297 | 0.057 |
| 8 | 55.779 | 0.058 |
| 9 | 64.688 | 0.057 |
| 10 | 59.458 | 0.059 |
The baseline plan scans the event table and performs a top-N sort once for each of 12 visible findings on asset 1. The indexed plan uses idx_finding_events_latest to find each latest eligible event in order, eliminating the repeated inner sort. Both conditions already use the unique asset/external-key index on findings. Shared-buffer hit counts include repeated accesses; they are not distinct page counts. Both saved plans recorded zero shared-buffer reads.
The complete ordered results had the same SHA-256 before and after:
bcfa22bcfd69fb3a6da4e05a978bec0b8c08f3d668be64539fb9be9791c5f022
The new index occupies 3,145,728 bytes (3 MiB). Its write-maintenance cost was not measured.
PostgreSQL reported 17.11 (Debian 17.11-1.pgdg13+2). The Python client used an x86_64 GitHub-hosted Linux runner with 16,760,340,480 bytes of visible memory (about 15.61 GiB). That memory value describes the client runner, not a verified database-container limit. Server settings were shared_buffers=128MB, work_mem=4MB, effective_cache_size=4GB, and max_parallel_workers_per_gather=2.
The workflow configured postgres:17@sha256:3b0cda93be7c8dba2ed484d19393d93845dc3dafb88973e248ba8e6d99d784e1; the benchmark process did not independently inspect the runtime image identity. All artifacts retain their original provenance in verification.json.
This is one warm-cache synthetic query on a shared CI runner. Sequential measurement phases and submillisecond indexed durations limit precision and generalization. The result does not establish an improvement for all six business reports, end-to-end application latency, or production workloads. The protocol can be rerun on a controlled local machine; those results should be recorded as a separate experiment.