PostgreSQL e gli indici...

[Vax Headroom]

Salve a tutti...
Mi sto scontrando con uno strano comportamento di una grossa (diciamo pure
enorme) tabella di dati su cui sto utilizzando degli indici... In questa
tabella vengono contenuti dei dati acquisiti in pacchetti di alcuni secondi
l'uno, caratterizzati da uno starttime ed un endtime (conservati come dei
double rappresentanti l'epoch piu` i centesimi di secondo), oltre che da un
nome di canale, piu` un'altra serie di colonne di dati relativi a quel
pacchetto...
Per velocizzare la ricerca all'interno del database ho creato un indice
(btree) implicito creando una chiave primaria proprio sui tre campi in
questione (nome del canale, starttime ed endtime)... Normalmente devo fare
due tipi di quey, la prima e` del tipo:

SELECT starttime, endtime,... FROM rawdata WHERE ( ( channel = 'nome' ) AND
( starttime >= 1128340500 ) AND ( endtime <= 1128340800 ) ) ORDER BY
starttime;

(ho messo dei numeri veri per far capire il tipo di query)

Mentre la seconda e` del tipo:

SELECT starttime, endtime,... FROM rawdata WHERE ( ( channel = 'nome' ) AND
( starttime < 1128340800 ) AND ( endtime > 1128340500 ) ) ORDER BY
starttime;

Le due query, sono quasi identiche, con la differenza che la seconda, a
volte, causa il modo in cui sono impacchettati i dati, puo` restituire una
o due righe in piu`...

Ebbene, sebbene entrambe in un qualche modo usino gli indici (visto che sono
entrambe estremamente piu` veloci in presenza degli stessi), la seconda e`
piuttosto piu` lenta della prima... Ho provato un po' a controllare i piani
di esecuzione delle query, il risultato e` che, se non gliele faccio
esplicitamente eseguire, il planner mi dice che le due query hanno
praticamente lo stesso costo... Se invece forzo l'explain ad eseguirle ho
il risultato "vero" che vi riporto:

EXPLAIN ANALYZE SELECT starttime, endtime, ... FROM RawData WHERE
( ( channel = 'nome' ) AND (starttime >= 1128340500 ) AND ( endtime <=
1128340800 ) );
[cut]
Index Scan using chiaveprimaria on rawdata (cost=0.00..1432061.48
rows=355443 width=531) (actual time=21.370..13795.037 rows=149 loops=1)
Index Cond: (((channel)::text = 'nome'::text) AND (starttime >=
1128340500::double precision) AND (endtime <= 1128340800::double
precision))
Total runtime: 13795.226 ms
(3 righe)

EXPLAIN ANALYZE SELECT starttime, endtime, samplenumber, pinno, samplerate,
datatype, data FROM RawData WHERE ( ( channel = 'nome' ) AND (starttime <
1128340800 ) AND ( endtime > 1128340500 ) );
[cut]
Index Scan using chiaveprimaria on rawdata (cost=0.00..1432065.88
rows=355444 width=531) (actual time=219031.077..219999.047 rows=151
loops=1)
Index Cond: (((channel)::text = 'nome'::text) AND (starttime <
1128340800::double precision) AND (endtime > 1128340500::double precision))
Total runtime: 219999.395 ms
(3 righe)

220 secondi contro 13.8 !!! La differenza mi sembra troppo per non essere
un errore mio nella creazione (e/o configurazione) degli inici... Qualcuno
sa dirmi qualcosa in piu`, e, eventualmente, darmi consigli su come
ulteriormente ottimizzare la ricerca ???

Grazie mille...
Rosario

PS - Allego un po' di statistiche sulla tabella in questione, in questo
momento dovrebbe essere grande piu` o meno come lo sara` "a regime":
SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname =
'rawdata';
relname | relkind | reltuples | relpages
---------+---------+-------------+----------
rawdata | r | 4.25255e+07 | 2657844
(1 riga)

SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename =
'rawdata';
attname | n_distinct | most_common_vals
--------------+-------------+---------------------------------------
channel | 12 | {OV.OVO.V,OV.OVO.E,OV.SGG.V,OV.SGG.N}
starttime | 4.06829e+06 | {1125940541}
endtime | 4.06829e+06 | {1125940543}
(altri campi poco utili al discorso)

C'e` pero` da dire che e` ancora in test, a regime il numero di canali sara`
abbastanza piu` grande, circa una settantina...

[The man with two watches]

Post by Vax Headroom
Per velocizzare la ricerca all'interno del database ho creato un
indice (btree) implicito creando una chiave primaria proprio sui tre
campi in questione (nome del canale, starttime ed endtime)...

Dunque, non conosco PostgreSql, per cui terrò un po' sul generico.
Mi manca inoltre il dato essenziale di: numero di pagine dell'indice,
numero di pagine lette per ciascuna query (il numero di righe
effettivamente recuperate è fuorviante in questo caso).

Mi sembra di capire che tu hai creato un indice "cluster" usando come
chiave l'insieme dei tre campi in questione, nella sequenza: canale,
starttime, endtime.
In questo modo le ricerche su "canale" saranno favorite, le ricerche su
starttime ed endtime saranno penalizzate.
In entrambe i tuoi "query plan" si nota uno scan dell'index. Solo che
nel primo caso:

WHERE ( ( channel = 'nome' ) AND
( starttime >= 1128340500 ) AND
( endtime <= 1128340800 ) )
ORDER BY starttime;

i SARG sfruttano bene l'indice, probabilmente perché il valore di
starttime è prossimo al valore massimo, per cui le operazioni che il
motore del rdbms fà sono:
1) Identificazione della prima pagine dell'indice che ha al suo interno
il channel e starttime iniziali (operazione ultraveloce)
2) Scansionata (=lettura sequenziale) di tutte le pagine sino al valore
massimo di starttime (operazione già più onerosa, specie se
bisogna leggere anche il nodo foglia (e quindi la tabella ) di un
indice "cluster")
3) Ulteriore scarto delle righe di endtime fuori condizione e ordinamento
finale.

Può darsi che l'operazione sia comunque veloce perché non ci sono molti
valori starttime >= 1128340500.

Nella seconda query il procedimento è il medesimo index scan, solo che
la sarg starttime < 1128340800 comporta il recupero tutte le pagine
dell'indice PRIMA di quel valore, essendo impossibilitata a "sapere"
che la sarg endtime > 1128340500 restringerebbe parecchio la ricerca,
dato che è la l'ultima colonna (cenerentola) dell'indice.

ciao!

[Leonardo Serni]

On Thu, 10 Nov 2005 16:56:47 +0100, Vax Headroom
<***@ov.ingv.it> wrote:

Riassumendo, la WHERE:

( starttime < 1128340800 ) AND ( endtime > 1128340500 ) )
ORDER BY starttime;

e' lenta, mentre

( starttime >= 1128340500 ) AND ( endtime <= 1128340800 ) )
ORDER BY starttime;

e' rock.

Prova, senza impegno, a immaginare di riscriverla

( endtime > 1128340500 ) AND ( starttime < 1128340800 )

e a creare due indici:

chiavealfa = canale, starttime, endtime
chiavebeta = canale, endtime, starttime

e a vedere cosa succede.

Premetto che sto tirando al buio, e potrei prendere una cantonata che la
meta' basta.

Poi nella scelta di end > ..500 e start < ..800 c'e' qualcosa che non mi
torna, ma non saprei bene cosa... forse solo che non ho capito cosa stai
cercando di selezionare :-)

Leonardo

[Vax Headroom]

Interfacciandosi alla mia unita' neurale, Leonardo Serni disse:

Rispondo a Leonardo, ma vale anche come risposta all'uomo con due
orologi ;-)

Post by Vax Headroom
( starttime < 1128340800 ) AND ( endtime > 1128340500 ) )
ORDER BY starttime;
e' lenta, mentre
( starttime >= 1128340500 ) AND ( endtime <= 1128340800 ) )
ORDER BY starttime;
e' rock.
Prova, senza impegno, a immaginare di riscriverla
( endtime > 1128340500 ) AND ( starttime < 1128340800 )
chiavealfa = canale, starttime, endtime
chiavebeta = canale, endtime, starttime
e a vedere cosa succede.

Partendo da questo (e dalla cosa dell'altro post, cioe` della "gerarchia"
delle chiavi di un indice, che ignoravo), ho ulteriormente riflettuto su un
particolare della struttura del database: le chiavi "a tre" sono poco
efficienti in questo particolare caso perche` una volta fissato station e
starttime (o endtime), l'altro tempo e` univoco anch'esso (questo perche` i
dati non possono sovrapporsi per costruzione)... Alche`, poiche` avevo un
altro indice solo su (station, starttime), ho provato a fare una ricerca
usando solo station e starttime... Sorpresa delle sorprese, la velocita`
era di almeno uno (a volte due) ordini di grandezza maggiore che nel
migliore dei casi precedenti...
A questo punto ho creato un secondo indice (station, endtime) ed ho
definito quattro criteri di ricerca diversi:

1) (starttime >= start) AND (starttime < end)
2) (endtime > start) AND (endtime <= end)
3) (starttime > start) AND (starttime < end)
4) (endtime > start) AND (endtime < end)

Orbene, l'effetto della prima ricerca (quella lenta) e` ottenibile come
intersezione di 1 e 2, mentre l'effetto della seconda ricerca e` ottenibile
come unione di 3 e 4... Nonostante le query diventino due per ogni ricerca,
e ci siano gli ulteriori tempi di ordinamento (che mi serviva comunque) e
di controllo sui risultati, questo modo di effettuarle risulta essere
_mooooolto_ piu` veloce delle precedenti due...
(non nascondo che c'e` una vocina da qualche parte nella mia testa che mi
sta dicendo che, probabilmente, c'e` almeno un altro modo, ancora piu`
efficiente, per fare la stessa cosa)
Bisogna pero` dire che i tempi non sono "stabili", nel senso che, prima,
erano abbastanza indipendenti sia dalla posizione dei valori (ma non dalla
loro distanza, ovviamente) che dal carico della macchina, mentre il tempo
di quest'altro tipo di ricerca dipende molto sia dal carico che dallo stato
della cache che dalla "posizione" della ricerca... Sta di fatto che i tempi
misurati (EXPLAIN ANALYZE) si mantengono sempre al di sotto dei 10 secondi
(spesso anche al di sotto del secondo)...

Post by Vax Headroom
Premetto che sto tirando al buio, e potrei prendere una cantonata che la
meta' basta.
Poi nella scelta di end > ..500 e start < ..800 c'e' qualcosa che non mi
torna, ma non saprei bene cosa... forse solo che non ho capito cosa stai
cercando di selezionare :-)

Dunque, provo a spiegarmi meglio :-)...
I dati sono contenuti nel DB come "pacchetti" di uno o piu` secondi (fino a
5 o 6) e non e` detto che essi siano posizionati all'inizio "preciso" di un
secondo... Se voglio estrarre tutti i dati che sono compresi in un dato
intervallo di tempo possono accadere varie cose, ma la piu` probabile e` la
seguente:

<disegnino spero esplicativo>

-------tempo----->
...[1][2][3][4][5][6][7][8][9][10][11][12][13]...
s e

</disegnino spero esplicativo>

Qui ho indicato con "[n]" il singolo pacchetto e con 's' ed 'e' l'inizio e
la fine della mia query...
Ebbene, con una query del tipo:

(starttime >= s) AND (endtime <= e)

io sono in grado di estrarre solo i pacchetti "interni", cioe`,
nell'esempio, tutti quelli nell'intervallo [4, 9] e potrei non estrarre i
due pacchetti di "bordo" ([3] e [10]), perche` hanno limiti che non
coincidono con quelli della ricerca (a).
Invece, con la richiesta:

(endtime > s) AND (starttime < e)

io sono in grado di estrarre tutti i pacchetti contenenti i dati che mi
interessano, compresi, in questo esempio, [3] e [10]...

Sperando di essere stato piu` chiaro ;-) e "ringraziando indistintamente"...
Rosario

(a) Al limite, nel caso in cui capiti:
...[][][][s][e][][][]...
la prima ricerca mi restituirebbe un insieme vuoto, mentre esistono almeno
due pacchetti che contengono una parte dei dati che mi interessano...