Newsgroup: it.comp.software.database
Titolo: PostgreSQL e gli indici...

   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...
-- 
                          | Almeno tu che puoi fuggi via canto
\|/ ____ \|/   \ ||_|     | nomade questa cella e` piena della
"@'/ ,. \`@"    \|| |     | mia disperazione tu che puoi non
/_| \__/ |_\ Vax Headroom | farti prendere.
   \__U_/                 |                    BMS

 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!

 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
-- 
Nach doiligh domhsa mo chailin a mholadh
'S ni he amhain mar bhi si or
Bhi si mar gath greine a dhul in eadan na gloinne
Is bhi sceimh mhna na finne le mo cailin or

 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...
-- 
                          |
\|/ ____ \|/   \ ||_|     |   Ogni viaggio, anche il piu` lungo
"@'/ ,. \`@"    \|| |     |   inizia con il primo passo...
/_| \__/ |_\ Vax Headroom |
   \__U_/                 |