05 - Indexing per Vector DB: HNSW e IVFFlat

-- Formula approssimata dimensione indice HNSW:
-- Dimensione ~= n_vectors * m * (2 + 4 * d / 8) bytes + overhead
-- dove:
--   n_vectors = numero di vettori
--   m = parametro m dell'indice (connessioni per nodo)
--   d = dimensioni del vettore (es. 1536)

-- Esempio pratico per 1M vettori, dim=1536, m=16:
-- 1_000_000 * 16 * (2 + 4 * 1536 / 8) = 1_000_000 * 16 * 770 = 12.3 GB
-- HNSW ocupa tipicamente 1.5-3x la dimensione dei dati grezzi

-- Dimensione dati grezzi (vettore float4):
-- 4 bytes * 1536 dim * 1_000_000 vettori = 6.1 GB

-- Controlla dimensioni reali:
SELECT
    pg_size_pretty(pg_relation_size('documents')) AS table_size,
    pg_size_pretty(pg_indexes_size('documents')) AS indexes_size,
    pg_size_pretty(pg_total_relation_size('documents')) AS total_size;

-- Dimensione specifica di ogni indice:
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    pg_size_pretty(pg_relation_size(indexrelid)) AS raw_bytes
FROM pg_stat_user_indexes
WHERE tablename = 'documents'
ORDER BY pg_relation_size(indexrelid) DESC;

-- Verifica se l'indice sta in shared_buffers:
-- Regola pratica: shared_buffers deve essere >= 1.5x la dimensione dell'indice HNSW
-- Se non ci sta, considera embedding a dimensione ridotta (768 o 384 dim)
SELECT
    current_setting('shared_buffers') AS shared_buffers,
    pg_size_pretty(pg_indexes_size('documents')) AS total_index_size;

-- m: numero massimo di connessioni bidirezionali per nodo in ogni livello
-- Valore default: 16
-- Range valido: 4-64 (pgvector max: 100)

-- Regole pratiche per m:
-- m=8:  Bassa memoria, bassa recall (uso: caching, suggerimenti veloci, dataset grandi)
-- m=16: Default bilanciato (uso: general purpose RAG, semantic search)
-- m=32: Alta recall, doppia memoria (uso: ricerca medica, legale, alta precisione)
-- m=64: Massima recall, 4x memoria (uso: casi estremi, dataset piccoli <100K)

-- Benchmark m vs recall e memoria (1M vettori, 1536 dim, ef_search=40):
-- m=8:  recall@10=84%, index=6GB,  p50=7ms,  p95=15ms
-- m=16: recall@10=93%, index=12GB, p50=10ms, p95=22ms
-- m=32: recall@10=97%, index=24GB, p50=18ms, p95=38ms
-- m=64: recall@10=99%, index=48GB, p50=35ms, p95=72ms

-- Crea indici con diversi valori di m (test comparativo):
CREATE INDEX idx_hnsw_m8  ON documents USING hnsw (embedding vector_cosine_ops) WITH (m=8,  ef_construction=64);
CREATE INDEX idx_hnsw_m16 ON documents USING hnsw (embedding vector_cosine_ops) WITH (m=16, ef_construction=64);
CREATE INDEX idx_hnsw_m32 ON documents USING hnsw (embedding vector_cosine_ops) WITH (m=32, ef_construction=64);

-- Testa quale usa PostgreSQL (usa il primo disponibile per nome)
-- Per forzare un indice specifico:
SELECT /*+ IndexScan(documents idx_hnsw_m32) */
    id, embedding <=> query_vec AS dist
FROM documents
ORDER BY embedding <=> query_vec
LIMIT 10;

-- Confronta le dimensioni effettive degli indici creati:
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan AS query_count
FROM pg_stat_user_indexes
WHERE tablename = 'documents'
  AND indexname LIKE 'idx_hnsw_m%'
ORDER BY indexname;

-- ef_construction: candidati considerati durante la costruzione dell'indice
-- Influisce sulla qualità dell'indice costruito (recall potenziale massima)
-- NON influisce sulle dimensioni dell'indice
-- Valore default: 64

-- Regole pratiche:
-- ef_construction=32:  Build veloce, recall potenziale ridotto. Solo per prototipi.
-- ef_construction=64:  Default. Ottimo per la maggior parte dei casi.
-- ef_construction=128: Build 2x più lenta, recall massima ~2% migliore.
-- ef_construction=200: Build molto lenta, miglioramento marginale.

-- Benchmark ef_construction (m=16, 1M vettori):
-- ef=32:  Build ~20min, max recall@10 ~89%
-- ef=64:  Build ~45min, max recall@10 ~95%
-- ef=128: Build ~90min, max recall@10 ~97%
-- ef=256: Build ~3h,    max recall@10 ~98.5%

-- Per massimizzare la qualità dell'indice (una tantum, non in produzione):
-- Usa maintenance_work_mem grande per la build
SET maintenance_work_mem = '4GB';  -- temporaneo per la build
CREATE INDEX idx_hnsw_highquality
ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m=24, ef_construction=128);
-- Dopo la build, la RAM viene rilasciata automaticamente

-- Verifica il progresso della build:
SELECT
    phase,
    blocks_done,
    blocks_total,
    ROUND(blocks_done::numeric / NULLIF(blocks_total, 0) * 100, 1) AS pct_done,
    tuples_done,
    tuples_total
FROM pg_stat_progress_create_index
WHERE relid = 'documents'::regclass;

-- ef_search: candidati esaminati durante la ricerca (beam search width)
-- E un parametro RUNTIME: puoi cambiarlo senza ricostruire l'indice
-- Valore default: 40
-- Range valido: 1 -> ef_construction (max della build)

-- Imposta ef_search per la sessione corrente:
SET hnsw.ef_search = 40;   -- default, buon equilibrio

-- Alta precisione (RAG enterprise, medico, legale):
SET hnsw.ef_search = 100;

-- Alta velocità (autocomplete, recommendation real-time):
SET hnsw.ef_search = 20;

-- Benchmark ef_search (1M vettori, 1536 dim, m=16, ef_construction=64):
-- ef_search=10:  ~3ms/query,  recall@10 ~75%
-- ef_search=20:  ~5ms/query,  recall@10 ~85%
-- ef_search=40:  ~10ms/query, recall@10 ~92%
-- ef_search=100: ~25ms/query, recall@10 ~97%
-- ef_search=200: ~50ms/query, recall@10 ~99%

-- Imposta a livello di transazione (più sicuro in produzione):
BEGIN;
SET LOCAL hnsw.ef_search = 80;
SELECT id, content, embedding <=> $1::vector AS dist
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 10;
COMMIT;

-- Imposta globalmente in postgresql.conf (persiste tra restart):
-- hnsw.ef_search = 60

-- Verifica configurazione corrente:
SHOW hnsw.ef_search;
SELECT current_setting('hnsw.ef_search');

-- lists: numero di cluster (centroidi) per IVFFlat
-- Regola pratica:
--   lists = sqrt(n_rows)  per dataset fino a 1M righe
--   lists = n_rows / 1000  per dataset sopra 1M righe

-- Calcolo automatico del valore ottimale:
WITH stats AS (
    SELECT COUNT(*) AS n FROM documents
)
SELECT
    n,
    CEIL(SQRT(n::float))::int AS recommended_lists,
    CEIL(SQRT(n::float))::int * 10 AS max_probes  -- max probes = 10% delle liste
FROM stats;

-- Esempi:
-- 10K righe:   lists=100  (sqrt=100, ma min raccomandato=100)
-- 100K righe:  lists=316  (sqrt(100000))
-- 1M righe:    lists=1000 (sqrt(1000000))
-- 10M righe:   lists=3162 (sqrt(10000000))
-- 100M righe:  lists=10000

-- Crea l'indice con il valore calcolato (procedura automatica):
DO $$
DECLARE
    n_rows INTEGER;
    n_lists INTEGER;
BEGIN
    SELECT COUNT(*) INTO n_rows FROM documents;
    n_lists := GREATEST(100, CEIL(SQRT(n_rows::float))::int);
    EXECUTE format(
        'CREATE INDEX idx_ivfflat ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = %s)',
        n_lists
    );
    RAISE NOTICE 'Indice IVFFlat creato con % liste per % righe', n_lists, n_rows;
END $$;

-- IMPORTANTE: IVFFlat richiede dati esistenti per fare K-means!
-- Crea l'indice DOPO aver caricato almeno il 70-80% dei dati.
-- Se aggiungi molti dati dopo la build, l'indice degrada: ricostruiscilo periodicamente.

-- Verifica bilanciamento dei cluster (uniformita delle liste):
-- In produzione, ogni lista dovrebbe contenere circa n_rows / lists vettori
-- Lista con molto più vettori delle altre = distribuzione sbilanciata
SELECT
    count(*) AS cluster_size,
    avg(count(*)) OVER () AS avg_size
FROM (
    -- Questa e una query interna che usa l'indice IVFFlat
    -- Non e disponibile direttamente via SQL, ma puoi stimarla
    SELECT id FROM documents LIMIT 1000
) sub;

-- probes: quante liste cercare durante una query
-- Deve essere <= lists
-- Default: 1 (cerca solo la lista più vicina - molto veloce ma bassa recall!)

-- ATTENZIONE: il default di probes=1 da una recall molto bassa!
-- Imposta sempre probes appropriato per il tuo use case.

-- Formula per target di recall:
-- probes_needed ~= lists * target_recall^2
-- Per recall 90% con lists=1000: probes ~= 1000 * 0.81 = 810 (!!)
-- Per recall 85% con lists=1000: probes ~= 1000 * 0.72 = 720
-- Per recall 80% con lists=1000: probes ~= 1000 * 0.64 = 640

-- In pratica, con clustering ben distribuito (K-means converge):
-- probes = lists * 0.05  ->  recall ~= 85%   (buon bilanciamento)
-- probes = lists * 0.10  ->  recall ~= 90%
-- probes = lists * 0.20  ->  recall ~= 95%

-- Benchmark IVFFlat (1M vettori, lists=1000, 1536 dim):
-- probes=5:   ~3ms/query,  recall@10 ~72%
-- probes=10:  ~6ms/query,  recall@10 ~82%
-- probes=50:  ~28ms/query, recall@10 ~92%
-- probes=100: ~55ms/query, recall@10 ~96%
-- probes=200: ~110ms/query, recall@10 ~98%

-- Impostazione in postgresql.conf (persiste tra sessioni):
-- ivfflat.probes = 10  (default globale)

-- Override per sessione/transazione:
BEGIN;
SET LOCAL ivfflat.probes = 50;  -- solo per questa transazione
SELECT id, content FROM documents ORDER BY embedding <=> query_vec LIMIT 5;
COMMIT;

-- Per application-level tuning in Python con psycopg2:
with conn.cursor() as cur:
    cur.execute("SET ivfflat.probes = %s", (probes,))
    cur.execute("""
        SELECT id, content, 1 - (embedding <=> %s::vector) AS similarity
        FROM documents
        ORDER BY embedding <=> %s::vector
        LIMIT %s
    """, (query_vec, query_vec, top_k))
    results = cur.fetchall()

-- Dashboard monitoring completo per indici vettoriali
SELECT
    schemaname,
    tablename,
    indexname,
    -- Utilizzo
    idx_scan           AS "Query che usano l'indice",
    idx_tup_read       AS "Tuple lette dall'indice",
    idx_tup_fetch      AS "Tuple effettivamente restituite",
    -- Efficienza
    CASE
        WHEN idx_scan > 0 THEN ROUND(idx_tup_fetch::numeric / idx_scan, 1)
        ELSE 0
    END AS "Tuple/query media",
    -- Dimensioni
    pg_size_pretty(pg_relation_size(indexrelid)) AS "Dimensione indice"
FROM pg_stat_user_indexes
WHERE tablename = 'documents'
ORDER BY idx_scan DESC;

-- Verifica se l'indice e in cache (shared_buffers)
-- Richiede pg_buffercache extension:
CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid)) AS "Dimensione",
    ROUND(
        (SELECT COUNT(*) FROM pg_buffercache WHERE relfilenode = pg_relation_filenode(oid))::numeric
        / NULLIF(pg_relation_size(oid) / 8192, 0) * 100, 2
    ) AS "% in shared_buffers"
FROM pg_class
WHERE relname LIKE '%hnsw%' OR relname LIKE '%ivfflat%';

-- Se l'indice e <50% in cache, le query saranno molto più lente (I/O bound)
-- Soluzione: aumentare shared_buffers o usare pg_prewarm

-- Query lente recenti che coinvolgono vector search (richiede pg_stat_statements):
SELECT
    LEFT(query, 100) AS query_short,
    calls,
    ROUND(mean_exec_time::numeric, 2) AS mean_ms,
    ROUND(max_exec_time::numeric, 2) AS max_ms,
    ROUND(total_exec_time::numeric / 1000, 2) AS total_sec
FROM pg_stat_statements
WHERE query ILIKE '%<=%>%'  -- query con vector distance operator
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Estensione pg_prewarm: carica indici in shared_buffers all'avvio
CREATE EXTENSION IF NOT EXISTS pg_prewarm;

-- Carica l'indice HNSW in cache immediatamente
SELECT pg_prewarm('documents_hnsw_idx');
-- Restituisce: numero di blocchi caricati

-- Verifica quanta memoria e stata usata
SELECT
    pg_size_pretty(pg_relation_size('documents_hnsw_idx')) AS indice_size,
    pg_size_pretty(current_setting('shared_buffers')::bigint) AS shared_buffers,
    ROUND(
        pg_relation_size('documents_hnsw_idx')::numeric /
        current_setting('shared_buffers')::bigint * 100, 1
    ) AS pct_of_shared_buffers;

-- Configura il prewarming automatico all'avvio di PostgreSQL
-- in postgresql.conf:
-- shared_preload_libraries = 'pg_prewarm'
-- pg_prewarm.autoprewarm = on
-- pg_prewarm.autoprewarm_interval = 300  -- salva stato ogni 5 minuti

-- Questo garantisce che dopo un restart, l'indice venga ricaricato in cache
-- automaticamente usando lo stato salvato prima dello shutdown.

-- Lista degli oggetti prioritari da precaricare:
SELECT pg_prewarm(indexrelid::regclass)
FROM pg_stat_user_indexes
WHERE tablename = 'documents'
  AND indexname LIKE '%hnsw%' OR indexname LIKE '%ivfflat%';

-- Monitora la recall nel tempo dopo insert
-- Salva recall periodicamente in una tabella di monitoraggio

CREATE TABLE IF NOT EXISTS index_quality_log (
    measured_at   TIMESTAMPTZ DEFAULT NOW(),
    index_name    TEXT,
    n_rows        BIGINT,
    recall_at_10  FLOAT,
    p50_ms        FLOAT,
    p95_ms        FLOAT,
    pct_cache     FLOAT  -- % dell'indice in shared_buffers
);

-- Funzione di misurazione automatica
CREATE OR REPLACE FUNCTION measure_index_quality(
    p_index_name TEXT,
    p_table_name TEXT
) RETURNS void AS $$
DECLARE
    v_n_rows BIGINT;
    v_cache_pct FLOAT;
BEGIN
    -- Conta righe correnti
    EXECUTE format('SELECT COUNT(*) FROM %I', p_table_name) INTO v_n_rows;

    -- Calcola % in cache (approssimazione)
    SELECT ROUND(
        (SELECT COUNT(*) FROM pg_buffercache
         WHERE relfilenode = pg_relation_filenode(p_index_name::regclass))::numeric
        / NULLIF(pg_relation_size(p_index_name::regclass) / 8192, 0) * 100, 2
    ) INTO v_cache_pct;

    -- Inserisci log (recall misurata externamente con set di test)
    INSERT INTO index_quality_log (index_name, n_rows, pct_cache)
    VALUES (p_index_name, v_n_rows, v_cache_pct);

    RAISE NOTICE 'Quality log: index=%, rows=%, cache=%\%', p_index_name, v_n_rows, v_cache_pct;
END;
$$ LANGUAGE plpgsql;

-- Chiama periodicamente (es. ogni giorno):
SELECT measure_index_quality('documents_hnsw_idx', 'documents');

-- Query per vedere la degradazione nel tempo
SELECT
    measured_at::date AS "Data",
    n_rows AS "Righe",
    recall_at_10 AS "Recall@10",
    p95_ms AS "P95 latency (ms)",
    pct_cache AS "% In Cache"
FROM index_quality_log
WHERE index_name = 'documents_hnsw_idx'
ORDER BY measured_at;

-- Soglie di allerta (imposta alert se superate):
-- recall_at_10 < 0.85  -> considera REINDEX urgente
-- recall_at_10 < 0.90  -> pianifica REINDEX entro 1 settimana
-- p95_ms > 100         -> verifica se l'indice e in cache
-- pct_cache < 50%      -> aumenta shared_buffers o usa pg_prewarm

-- REINDEX CONCURRENTLY ricostruisce l'indice senza bloccare le query in lettura
-- Nota: richiede PostgreSQL 12+ e più tempo del REINDEX normale
-- Durante il rebuild, le query continuano a usare il vecchio indice

-- METODO 1: REINDEX diretto (più semplice, PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY documents_hnsw_idx;
-- Pro: semplice
-- Con: non puoi cambiare parametri durante il rebuild

-- METODO 2: Swap con indice temporaneo (più flessibile)
-- Step 1: Crea un nuovo indice con parametri ottimizzati
CREATE INDEX CONCURRENTLY documents_hnsw_new
ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m=16, ef_construction=128);  -- ef_construction migliorato!

-- Step 2: Verifica che il nuovo indice sia stato costruito correttamente
SELECT
    indexname,
    indisvalid AS is_valid,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_indexes
JOIN pg_index ON pg_index.indexrelid = pg_class.oid
JOIN pg_class ON pg_class.relname = pg_indexes.indexname
WHERE tablename = 'documents'
  AND indexname IN ('documents_hnsw_idx', 'documents_hnsw_new');

-- Step 3: Swap atomico (solo un breve lock esclusivo)
BEGIN;
DROP INDEX documents_hnsw_idx;          -- lock esclusivo brevissimo
ALTER INDEX documents_hnsw_new RENAME TO documents_hnsw_idx;
COMMIT;

-- Quanto spesso fare il rebuild?
-- Dopo >20% di insert/update rispetto alla dimensione originale
-- Se recall < 0.85 (misurata con test set)
-- Dopo cancellazioni massive (>30% delle righe)
-- Schedule raccomandato: ogni settimana per dataset molto dinamici,
-- ogni mese per dataset stabili

-- Automatizza il rebuild con pg_cron (se disponibile):
-- SELECT cron.schedule('weekly-hnsw-rebuild', '0 2 * * 0',
--   'REINDEX INDEX CONCURRENTLY documents_hnsw_idx');

-- Strategia 1: Indici parziali per tipo di documento
-- Vantaggi: ogni indice e più piccolo, più veloce, occupa meno RAM
CREATE INDEX idx_hnsw_docs_pdf
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m=16, ef_construction=64)
WHERE source_type = 'pdf';

CREATE INDEX idx_hnsw_docs_web
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m=16, ef_construction=64)
WHERE source_type IN ('html', 'md');

-- Query che attivano automaticamente l'indice parziale:
EXPLAIN SELECT id, content
FROM documents
WHERE source_type = 'pdf'           -- questa condizione attiva idx_hnsw_docs_pdf
ORDER BY embedding <=> '[...]'::vector
LIMIT 5;
-- Output: Index Scan using idx_hnsw_docs_pdf

-- Strategia 2: Indici per dimensione diversa (Matryoshka embeddings / MRL)
-- text-embedding-3-small supporta 512 e 1536 dimensioni
ALTER TABLE documents ADD COLUMN IF NOT EXISTS embedding_512 vector(512);
ALTER TABLE documents ADD COLUMN IF NOT EXISTS embedding_1536 vector(1536);

CREATE INDEX idx_hnsw_512
ON documents USING hnsw (embedding_512 vector_cosine_ops)
WITH (m=16, ef_construction=64);

CREATE INDEX idx_hnsw_1536
ON documents USING hnsw (embedding_1536 vector_cosine_ops)
WITH (m=32, ef_construction=128);  -- più qualità per la versione full

-- Query con la versione appropriata:
-- Ricerca veloce (autocomplete, 3x più veloce, ~95% della qualità):
SELECT id, content, embedding_512 <=> query_512 AS dist
FROM documents
ORDER BY embedding_512 <=> query_512 LIMIT 20;

-- Ricerca precisa (RAG):
SELECT id, content, embedding_1536 <=> query_1536 AS dist
FROM documents
WHERE id IN (
    SELECT id FROM documents
    ORDER BY embedding_512 <=> query_512 LIMIT 100  -- coarse filter
)
ORDER BY embedding_1536 <=> query_1536 LIMIT 5;

-- Strategia 3: Indice per timestamp (solo documenti recenti)
-- Ottimo per applicazioni news, chat history, documenti freschi
CREATE INDEX idx_hnsw_recent
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m=16, ef_construction=64)
WHERE created_at > NOW() - INTERVAL '7 days';

-- L'indice si popola automaticamente con i nuovi insert
-- I documenti vecchi cadono fuori condizione automaticamente
-- REINDEX periodico per rimuovere i "dead links" ai documenti scaduti

# postgresql.conf - Configurazione ottimale per vector search
# Applica dopo aver determinato la quantità di RAM del server

# ========================================
# MEMORIA - La parte più critica
# ========================================
shared_buffers = '8GB'          # 25% della RAM totale
                                 # L'indice HNSW DEVE stare qui
                                 # Con 32GB RAM: shared_buffers = 8GB
                                 # Con 64GB RAM: shared_buffers = 16GB

effective_cache_size = '24GB'   # 75% della RAM totale
                                 # Stima per il query planner
                                 # NON alloca memoria, solo un suggerimento

work_mem = '64MB'               # Per sort e hash operations
                                 # Influenza le query con ORDER BY + LIMIT
                                 # Attenzione: ogni connessione può usarlo più volte

maintenance_work_mem = '2GB'    # Per CREATE INDEX (usa MOLTO più del normale)
                                 # Imposta a 25-50% della RAM prima di un rebuild
                                 # Dopo la build, rimette il valore originale

# ========================================
# PARALLELISMO
# ========================================
max_parallel_workers_per_gather = 4  # Worker per singola query
max_parallel_workers = 8             # Worker totali per tutto il sistema
max_parallel_maintenance_workers = 7 # Per CREATE INDEX parallelo (PG16+)
parallel_tuple_cost = 0.1            # Incentiva l'uso del parallelismo
parallel_setup_cost = 100            # Overhead setup per parallelismo

# ========================================
# pgvector SETTINGS
# ========================================
# Questi si impostano a runtime o in postgresql.conf:
hnsw.ef_search = 60             # Default per il sistema (override per sessione)
ivfflat.probes = 10             # Default per il sistema

# ========================================
# WAL (Write-Ahead Log) per INSERT intensivi
# ========================================
wal_buffers = '64MB'
max_wal_size = '4GB'
checkpoint_completion_target = 0.9
wal_compression = on            # Riduce I/O WAL (utile per ingestion intensiva)

# ========================================
# AUTOVACUUM - Critico per tabelle vector
# ========================================
autovacuum = on
autovacuum_max_workers = 5
# Le tabelle vector con molti insert/delete necessitano autovacuum aggressivo:
# (imposta per-tabella con ALTER TABLE, non qui)

# ========================================
# MONITORING
# ========================================
log_min_duration_statement = 100   # Loga query più lente di 100ms
track_io_timing = on               # Misura I/O time (utile per diagnosi cache miss)
track_activity_query_size = 2048   # Tronca query nel log a 2048 chars
shared_preload_libraries = 'pg_stat_statements,pg_prewarm'
pg_stat_statements.max = 10000    # Traccia le ultime 10K query uniche

-- PostgreSQL 16+ supporta il parallel index build per HNSW
-- Riduce drasticamente i tempi di build su sistemi multi-core

-- Imposta worker per la build (più worker = build più veloce)
-- max = max_parallel_maintenance_workers
SET max_parallel_maintenance_workers = 7;  -- usa 8 CPU totali (1 leader + 7 worker)

-- Imposta maintenance_work_mem alto per la build (più = più veloce)
SET maintenance_work_mem = '4GB';

-- Build con parallelismo:
CREATE INDEX idx_hnsw_parallel
ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m=16, ef_construction=64);

-- Monitora il progresso della build (PostgreSQL 12+):
SELECT
    phase,
    blocks_done,
    blocks_total,
    ROUND(blocks_done::numeric / NULLIF(blocks_total, 0) * 100, 1) AS "% completato",
    tuples_done,
    tuples_total,
    ROUND(tuples_done::numeric / NULLIF(tuples_total, 0) * 100, 1) AS "% tuple completate"
FROM pg_stat_progress_create_index
WHERE relid = 'documents'::regclass;

-- Esempio output durante la build:
-- phase: "building index"
-- blocks_done: 15234
-- blocks_total: 61000
-- % completato: 25.0
-- tuples_done: 250000
-- tuples_total: 1000000

-- Confronto velocità build (1M vettori, 1536 dim, m=16, ef=64):
-- 1 worker:  ~90 minuti
-- 4 worker:  ~25 minuti
-- 8 worker:  ~15 minuti  (rendimento decrescente oltre 8)
-- 16 worker: ~12 minuti  (miglioramento minimo)

-- Stima automatica del tempo di build in base ai dati:
WITH stats AS (SELECT COUNT(*) AS n FROM documents)
SELECT
    n AS num_vectors,
    ROUND(n / 1000000.0, 2) AS millions,
    -- Stima con 8 worker, m=16, ef_construction=64
    ROUND(n / 1000000.0 * 15, 0) || ' min' AS estimated_build_8workers
FROM stats;

-- Two-phase retrieval per massima efficienza
-- Phase 1: Fast coarse search with 512-dim embeddings (3x faster)
-- Phase 2: Precise re-ranking with 1536-dim embeddings (only on top-50)

WITH coarse_candidates AS (
    -- Phase 1: top-50 candidates with fast 512-dim search
    SELECT
        id,
        embedding_512 <=> %s::vector(512) AS coarse_dist
    FROM documents
    ORDER BY embedding_512 <=> %s::vector(512)
    LIMIT 50
),
precise_ranking AS (
    -- Phase 2: re-rank top-50 with precise 1536-dim embeddings
    SELECT
        d.id,
        d.content,
        d.source_path,
        d.embedding_1536 <=> %s::vector(1536) AS precise_dist,
        1 - (d.embedding_1536 <=> %s::vector(1536)) AS similarity
    FROM documents d
    INNER JOIN coarse_candidates c ON c.id = d.id
    ORDER BY d.embedding_1536 <=> %s::vector(1536)
)
SELECT id, content, source_path, similarity
FROM precise_ranking
LIMIT 5;

-- Latenza tipica vs qualità (1M vettori):
-- Direct 1536-dim HNSW (ef_search=40): ~10ms,  Recall@5 ~94%
-- Direct 1536-dim HNSW (ef_search=100): ~25ms, Recall@5 ~98%
-- Two-phase (512 coarse + 1536 rerank): ~4ms,  Recall@5 ~96%
--   -> 2.5x più veloce con recall ancora migliore!

-- Variante con ef_search ridotto per la fase coarse:
WITH coarse_fast AS (
    SELECT id
    FROM documents,
    LATERAL (SELECT 'SET hnsw.ef_search = 20') AS _  -- ef basso per coarse
    ORDER BY embedding <=> %s::vector
    LIMIT 100
)
SELECT d.id, d.content, 1 - (d.embedding <=> %s::vector) AS similarity
FROM documents d
JOIN coarse_fast c ON c.id = d.id
ORDER BY d.embedding <=> %s::vector
LIMIT 5;

-- Le cancellazioni in PostgreSQL sono "soft delete" (tuple marcate dead)
-- L'indice HNSW mantiene riferimenti a queste tuple morte
-- VACUUM rimuove le tuple morte e aggiorna l'indice

-- Verifica tuple morte (dead tuples) - indicatore di necessità VACUUM
SELECT
    relname AS "Tabella",
    n_live_tup AS "Righe vive",
    n_dead_tup AS "Righe morte",
    ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS "% morte",
    last_vacuum,
    last_autovacuum,
    -- Stima quante modifiche da ultimo analyze
    n_mod_since_analyze AS "Modifiche da analyze"
FROM pg_stat_user_tables
WHERE relname = 'documents';

-- Se "% morte" > 10-20%, e il momento di fare VACUUM
VACUUM ANALYZE documents;  -- vacuum + aggiorna statistiche

-- VACUUM FULL: ricostruisce la tabella (blocca le scritture, libera più spazio)
-- Usa solo in finestre di manutenzione programmate:
VACUUM FULL documents;

-- Configurazione autovacuum aggressivo per tabelle vector
-- (molti update/delete tipici di pipeline RAG con aggiornamenti frequenti):
ALTER TABLE documents SET (
    autovacuum_vacuum_scale_factor = 0.01,   -- vacuum dopo 1% di righe modificate (default 20%)
    autovacuum_analyze_scale_factor = 0.005, -- analyze dopo 0.5% (default 10%)
    autovacuum_vacuum_cost_delay = 2,        -- più aggressivo (default 20ms)
    autovacuum_vacuum_threshold = 50         -- almeno 50 righe modificate (default 50)
);

-- Verifica che autovacuum stia girando:
SELECT
    schemaname,
    relname,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    autoanalyze_count
FROM pg_stat_user_tables
WHERE relname = 'documents';

-- Analisi completa di una query vector search
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT TEXT)
SELECT id, content, embedding <=> '[0.1, 0.2, ...]'::vector(1536) AS dist
FROM documents
WHERE source_type = 'pdf'
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector(1536)
LIMIT 10;

-- Output desiderato (usa l'indice HNSW):
-- Index Scan using idx_hnsw_docs_pdf on documents  (cost=0.00..8.54 rows=10)
--   Index Cond: (embedding <=> '[...]'::vector <=> '[...]'::vector)
--   Filter: (source_type = 'pdf')
--   Buffers: shared hit=247    <-- tutto da cache!
--   -> Planning Time: 0.3 ms
--   -> Execution Time: 8.7 ms

-- Output indesiderato (brute force - da evitare):
-- Seq Scan on documents  (cost=0.00..1234.56 rows=10)
--   Filter: (source_type = 'pdf')
--   Sort Key: (embedding <=> '...'::vector)
--   Buffers: shared hit=1234 read=5678  <-- molti read da disco!
--   -> Execution Time: 3450 ms

-- Se vedi Seq Scan invece di Index Scan, verifica:
-- 1. L'indice esiste?
SELECT indexname FROM pg_indexes WHERE tablename = 'documents';
-- 2. Il LIMIT e abbastanza piccolo?
--    PostgreSQL usa l'indice solo per LIMIT piccoli
-- 3. Le statistiche sono aggiornate?
ANALYZE documents;
-- 4. enable_indexscan e attivo?
SHOW enable_indexscan;  -- deve essere 'on'
-- 5. ef_search e appropriato?
SHOW hnsw.ef_search;