full text search | speech by matteo durighetto | pgday.it 2013

Full Text Search Matteo Durighetto

Italian PostgreSQL Users Group www.itpug.org

www.postgresql.org

PGDay.IT 2013Monash University Prato Centre

Venerdì 25 Ottobre 2013

Copyright 2012 Miriade S.p.a. Matteo Durighetto - [email protected] - ITPUG.org

http://www.itpug.org/

http://www.itpug.org/

http://www.postgresql.org/

http://www.postgresql.org/

http://www.miriade.it

mailto:[email protected]

Chi sono?

● Speaker/Author:○ Matteo Durighetto○ DBA @ Miriade S.p.A.○ tecnologie db: Oracle, PostgreSQL, MySQL, MSSQL ○ tecnologie os/virtual/cloud: AWS, Vmware,XEN, Linux, *NIX,

Windows○ Membro e Vice Presidente ITPUG

Copyright 2012 Miriade S.p.a.




WHOAMI;

http://www.linkedin.com/in/mdotdurighettoatmiriadedotit





Chi sono?





INDEX

● Full Text Search: a cosa serve● Perchè non il b-tree ?● Dizionario, Token, Lemma● Configurazione FTS ● Full Text Search Acceleration: Index● Full Text Search Query● Trigram




Chi sono?

Il full text search nasce dall’esigenza della ricerca documentale:

Trovare tutti i testi che contengono una determinata parola e le sue flessioni.

Se un testo contiene “casa” o “case” sarà equivalente.





Full Text Search : a cosa serve?




Chi sono?

Esempi nell’uso reale di tutti i giorni di Full Text Search :

● DuckDuckGo, Google, Yahoo, Bing...● Ricerca dei testi che contengono una certa parola in un

Documentale ( Alfresco, Documentum, SharePoint..)● Ricerca di una mail● etc..





Full Text Search : a cosa serve?




Chi sono?

Perchè:● Indicizzare con un b-tree un campo è ottimo per la ricerca tramite

predicati dove si compara un “documento” con il suo valore, non per la ricerca all’interno di un documento di un possibile valore in una delle sue declinazioni.

● Cercare documento like ‘%testo%’ comporterà un full scan!

● l’altezza del b-tree nel caso peggiore va come :log (d , (n+1)/2 , dove d è il minimo numero di keys per blocco e n è il numero di chiavi indicizzate. Se quindi le nostre chiavi sono tutti i lemmi indicizzati o solo quelli necessari le cose cambiano di molto!





Perchè non un indice normale ?




Chi sono?

Lemma : è la forma canonica di una parola [priva di suffissi].

Token: una parola di senso compiuto non necessariamente in forma canonica, in genere separata da spazi, va fatta attenzione alle lingue agglutinate come il filandese e il tedesco in cui una parola può essere composta da più morfemi !

Dizionario: un programma che data una parola, restituisce se possibile uno o più lemmi.





Dizionario Token Lemma

Documento Token Lemmapre-processing conversione

eliminazione stop word




Chi sono?






Documento

Parser

Configurazione FTS

Token SI Stop word ? tsvectorNO

NOSI

lunghezza>2047Byte

N.B. tsvector < 1Mb

Loop per ogni parola

DIZIONARIO

Chi sono?

Esempio :

A. “ Questa sera andiamo a teatro “ == DOCUMENTO

B. “questa” “sera” “andiamo” “a” “teatro” == TOKEN

C. “a” “questa” == STOP WORD [ eliminata in automatico ]

D. “ser” “andiam” “teatr” == LEMMI

E. “andiam” “ser” “teatr” == LEMMI ORDINATI









Chi sono?





Dizionario Token LemmaCome fa il matching Postgres tra Documento e la nostra ricerca?

Introduce tre concetti:● tsvector : un insieme ben ordinato di lemmi● tsquery : un insieme di lemmi in formato query con operatori

booleani ( | & ! )● un operatore FTS : @@

La rappresentazione segue la forma Bakus Naus:

<tsvector> = <lemma>[:<posizione>]<spazio><tsquery> = <lemma><spazio>[booleano]

La posizione è opzionale e può essere usata per calcolare il rank in base alla prossimità [ max 2^14-1 ]

Chi sono?

Esempio di con i type tsvector & tsquery senza trasformazione in lemmi dei token ma usando il casting:

select 'sto andando a casa, questa sera torno tardi'::tsvector; ---------------------------------------------------------------------------- 'andando' 'casa,' 'questa' 'sera' 'sto' 'tardi' 'torno'

select 'tardi & casa'::tsquery ;------------------- 'tardi' & 'casa'

select 'sto andando a casa, questa sera torno tardi'::tsvector@@'tardi & casa'::tsquery ;

true






stop word




Chi sono?

Esempio con le funzioni to_tsvector e to_tsquery sfruttando il dizionario :

select to_tsvector('italian','sto andando a casa, questa sera torno a casa tardi'); ------------------------------------------------- 'andand':2 'cas':4,9 'ser':6 'tard':10 'torn':7

select to_tsquery('italian','tardi & casa'); ----------------- 'tard' & 'cas'

select to_tsvector('italian','sto andando a casa questa sera torno a casa tardi')@@to_tsquery('italian','tardi & casa');

TRUE









Chi sono?

Operatori tra lemmi all’interno di una ts_query:

& = AND ‘cat & dog’::ts_query == 'cat' & 'dog'

cerco tutti i documenti che contengono la parola cat e dog

| = OR ‘cat | dog’::ts_query == 'cat' | 'dog'

cerco tutti i documenti che contengono la parola cat o dog

! = NOT ‘cat & !dog’::ts_query == 'cat' & !'dog'

cerco tutti i documenti che contengono la parola cat MA non dog









Chi sono?

TIPS: Per verificare come vengono trasformati i token in lemmi possiamo usare una funzione di debug :

select ts_leximize(‘italian_stem’,‘testo’);------------------------------test

TIPS: In genere non viene usato un vero e proprio dizionario, ma un algoritmo chiamato Snowball e un elenco di stop word che trovate nella directory /usr/share/postgresql/*/tsearch_data. In genere viene suddiviso in tag a seconda delle tipologia di lemmi da indicizzare









Chi sono?






Dizionario

Il dizionario è la parte principale con la configurazione FTS sia delle performance sia della esattezza delle ricerche.

Un dizionario è un programma che data una parola restituisce se possibile uno o più lemmi.

Vi sono 4 tipologie di dizionario che andremo ad esplorare.




Chi sono?






Dizionario Simple

Converte le parole in minuscolo, le confronta con un elenco di stop word [ deve essere creato un dizionario per questo ]. Esempio:

CREATE TEXT SEARCH DICTIONARY public.dict_italiano_semp ( TEMPLATE = pg_catalog.simple, STOPWORDS = italian);

testfulltext=# select ts_lexize('dict_italiano_semp','questa'); ts_lexize----------- {}




Chi sono?






Dizionario Simple

select ts_lexize('dict_italiano_semp','radar'); ts_lexize----------- {radar}

TIPS: Il dizionario semplice è molto buono, se nel documento italiano vi sono termini stranieri che lo stemmer dictionary probabilmente convertirebbe in lemmi errati, si paga tuttavia una maggior dimensione del tsvector!




Chi sono?






Dizionario Stem o Stemmer

Di default i dizionari usati da PostgreSQL sono stemmer. Ovvero usano un algoritmo a seconda della tipologia di lingua per suddividere le parole in più morfemi e ridurle ad una radice (stem , da cui il nome ). L’algoritmo usato è lo snow ball .Va fatta attenzione che l’algoritmo usato può estrarre lemmi errati se usato verso una lingua con un dizionario non corretto :

select ts_debug('italian','radar'); (asciiword,"Word, all ASCII",radar,{italian_stem},italian_stem,{rad})

select ts_debug('english','radar'); (asciiword,"Word, all ASCII",radar,{english_stem},english_stem,{radar})

TIPS : NON E’ APPLICABILE A LINGUE ISOLANTI COME IL CINESE O IL VIETNAMITA




Chi sono?







select to_tsvector('italian','radar')@@to_tsquery('italian','rado');

TRUE

select to_tsvector('english','radar')@@to_tsquery('italian','rado');

FALSE

TIPS: risolvo il problema creando una colonna che cataloga la lingua del documento o creando un dizionario di sinonimi




Chi sono?







CREATE TEXT SEARCH DICTIONARY italian_stem ( TEMPLATE = snowball, Language = italian, StopWords = italian);

N.B. il dizionario è già presente di default.

testfulltext=# \dFd+ italian_stem List of text search dictionaries Schema | Name | Template | Init options | Description ------------+--------------+---------------------+---------------------------------------------+--------------------------------------- pg_catalog | italian_stem | pg_catalog.snowball | language = 'italian', stopwords = 'italian' | snowball stemmer for italian language




Chi sono?





Dizionario Token LemmaAlgoritmo Snow Ball

L’algoritmo Snow Ball si basa su una serie di concetti:

1. Vi sono 3 tipologie di suffissi :a. tipo A: additivi ( attached )

“spedire”+”gli”+”lo”=”spedirglielo”b. tipo I: flessivi ( infletional ) , ovvero definiti dalla

grammatica, come per i passivi inglesi o i plurali : “have”+”ing” = “having”

“fit”+”ed” = “fitted”c. tipo D: derivativi ( derivation ) ovvero come gli additivi, ma

danno origine ad un moferma di senso compiuto il cui significato è diverso dall’originale, spesso in antitesi. “lancia”+”fiamme” = “lanciafiamme”




Chi sono?






Algoritmo Snow Ball

● I prefissi non vengono considerati :○ poichè possono incidere profondamente sul significato di

una parola○ perchè vi sono casi di parole con suffissi e prefissi e per

semplificare l’algoritmo si considerano solo i suffissi● Zona R1 : Data una parola P[1,n] è la parte di P[k+1,n] , dove k

è la posizione della prima consonante che segue una vocale o la parte nulla di P se non esiste tale consonante :P[1,n].R1 = { se esiste k : P[k+1,n] , altrimenti [] }

● Zona R2 : Data una parola P[1,n], se Q=P[1,n].R1 , allora P[1,n].R2 => Q[1,n-k+1].R1




Chi sono?






Algoritmo Snow Ball

● Zona RV : data una parola P[1,n] :○ se la seconda lettera è consonante e k è la posizione della

prima vocale in P[3,n]P[1,n].ZV => P[k+1,n]

○ se P[1,2] sono entrambi vocali e k è la posizione della prima consonante P[1,n].ZV => P[k+1,n]

○ in tutti gli altri casiP[1,n].ZV => P[4,n]




Chi sono?






Snow Ball Algorithm (per lingua italiana)

sostituzione [ á , é , í , ó , ú , à , è , ì , ò , ù ] con [ a , e , i , o , u , à , è , ì , ò , ù ]

Individuazione zone R1 e R2 e RV

Fase 0: eliminazione pronomi se seguono [e|a]ndo e altrimenti [e|a|i]r sostituiti con “e”

Fase 1: rimozione suffissi standard o sostituzione degli stessi in regione R2 o R1

Fase 2: rimozione suffissi dei verbi o sostituzione degli stessi in regione RV

Fase 3: eliminazione a, e, i, o, à, è, ì o ò in coda RV e della ì se precede RV.

Fase 4: sostituzione ch con c e gh con g nella coda della parola




Chi sono?






Dizionario Inspell

E’ un dizionario morfologico, ovvero con i morfemi e ad ogni morfema è associato il rispettivo lemma. Inotre contiene gli affissi ( == [suf|pre|in]fissi ) e le stop words. I dizionari si possono trovare nel extension (.osx) exchange di LibreOffice in genere in licenza GPL 3

CREATE TEXT SEARCH DICTIONARY italian_hunspell (TEMPLATE = ispell,DictFile = italian_hunspell,AffFile = italian_hunspell,StopWords = italian

);TIPS: in genere è più performante dello SnowBall




Chi sono?






Dizionario Inspell

Questo è possibile se si sono aggiunti:

● dizionario :○ /usr/share/postgresql/9.3/tsearch_data/italian_hunspell.dict

● affissi :○ /usr/share/postgresql/9.3/tsearch_data/italian_hunspell.affix

● N.B. le stop word sono già presenti




Chi sono?






Dizionario Sinonimi

Il dizionario dei sinonimi è utile nel caso dello SnowBall e in genere dove vi sono documenti di origine diversa.

CREATE TEXT SEARCH DICTIONARY my_synonym ( TEMPLATE = synonym, SYNONYMS = my_synonyms);




Chi sono?






Dizionario Sinonimi

Il file del dizionario dei sinonimi (*.syn) è così composto :

parola lemma..parola lemma

Si possono usare “*” per identificare più desinenze




Chi sono?






Dizionario Thesaurus

A differenza del dizionario ispell o huspell, il dizionario Thesaurus associa termini/parole [chiamati synonyms] ad una parola definita (in genere rappresenta una categoria) [chiamata preferred term ] e permette l’analisi di frasi.Condizione necessaria: tutte le parole devono essere presenti nel sottodizionarioSe si cambia il thesaurus, vanno reindicizzati i documenti, visto che dipende dalla relatione tra preferred term e synonym .

http://www.sai.msu.su/~megera/oddmuse/index.cgi/Thesaurus_dictionary




Chi sono?





Configurazione FTSConfigurazione FTS

Una configurazione FTS è composta da :● Un parser● Un insieme di categorie di token● Una mappatura tra le categorie di token e i dizionari

N.B. una categoria di token può essere associata a più dizionari in base ad un ordine di precedenza




Chi sono?






Rimozione di categoria di token che non necessitano di indicizzazione

ALTER TEXT SEARCH CONFIGURATION public.italian_hunspell DROP MAPPING FOR email, url, url_path, sfloat, float,file,host,int,version ;

testfulltext=# \dF+ public.italian_huspellText search configuration "public.italian_huspell"Parser: "pg_catalog.default" Token | Dictionaries-----------------+-------------- asciihword | italian_stem asciiword | italian_stem hword | italian_stem hword_asciipart | italian_stem hword_numpart | simple hword_part | italian_stem numhword | simple numword | simple uint | simple word | italian_stem




Chi sono?






Rimozione di categoria di token che non necessitano di indicizzazione

ALTER TEXT SEARCH CONFIGURATION public.italian_huspell ALTER MAPPING FOR asciihword,asciiword,hword,hword_asciipart,hword_part,word with italian_hunspell,italian_stem;

In questo modo usiamo due dizionari quello hunspell e quello stemmer basato su snow ball.

Parser: "pg_catalog.default" Token | Dictionaries -----------------+------------------------------- asciihword | italian_hunspell,italian_stem asciiword | italian_hunspell,italian_stem hword | italian_hunspell,italian_stem hword_asciipart | italian_hunspell,italian_stem hword_numpart | simple hword_part | italian_hunspell,italian_stem numhword | simple numword | simple uint | simple word | italian_hunspell,italian_stem




Chi sono?





Configurazione FTSConfigurazione FTSTest :SELECT * FROM ts_debug('public.italian_hunspell','Sono andato a Roma tempo fa, ho vistola fontana di Trevi e i Musei Vaticani'); alias | description | token | dictionaries | dictionary | lexemes -----------+-----------------+----------+---------------------------------+------------------+------------ asciiword | Word, all ASCII | Sono | {italian_hunspell,italian_stem} | italian_hunspell | {} blank | Space symbols | | {} | | asciiword | Word, all ASCII | andato | {italian_hunspell,italian_stem} | italian_hunspell | {andato} blank | Space symbols | | {} | | asciiword | Word, all ASCII | a | {italian_hunspell,italian_stem} | italian_hunspell | {} blank | Space symbols | | {} | | asciiword | Word, all ASCII | Roma | {italian_hunspell,italian_stem} | italian_hunspell | {roma} blank | Space symbols | | {} | | asciiword | Word, all ASCII | tempo | {italian_hunspell,italian_stem} | italian_hunspell | {tempo} blank | Space symbols | | {} | | asciiword | Word, all ASCII | fa | {italian_hunspell,italian_stem} | italian_hunspell | {fare} blank | Space symbols | , | {} | | asciiword | Word, all ASCII | ho | {italian_hunspell,italian_stem} | italian_hunspell | {} blank | Space symbols | | {} | | asciiword | Word, all ASCII | visto | {italian_hunspell,italian_stem} | italian_hunspell | {visto} blank | Space symbols | | {} | | asciiword | Word, all ASCII | la | {italian_hunspell,italian_stem} | italian_hunspell | {} blank | Space symbols | | {} | | asciiword | Word, all ASCII | fontana | {italian_hunspell,italian_stem} | italian_hunspell | {fontana} blank | Space symbols | | {} | | asciiword | Word, all ASCII | di | {italian_hunspell,italian_stem} | italian_stem | {} blank | Space symbols | | {} | | asciiword | Word, all ASCII | Trevi | {italian_hunspell,italian_stem} | italian_hunspell | {trevo} blank | Space symbols | | {} | | asciiword | Word, all ASCII | e | {italian_hunspell,italian_stem} | italian_hunspell | {} blank | Space symbols | | {} | | asciiword | Word, all ASCII | i | {italian_hunspell,italian_stem} | italian_hunspell | {} blank | Space symbols | | {} | | asciiword | Word, all ASCII | Musei | {italian_hunspell,italian_stem} | italian_hunspell | {museo} blank | Space symbols | | {} | | asciiword | Word, all ASCII | Vaticani | {italian_hunspell,italian_stem} | italian_hunspell | {vaticano}




Chi sono?





Speed Up Search: IndexVi sono due tipi di indici per le ricerche FTS:

● GIN Index = Generalized Inverted Index, indici esatti , tempo di build molto alto rispetto ai GIST (3X) e occupano molto più spazion (3x) . Non salvano i “pesi” dei lemmi, ergo non sono adatti per ricerche di rank. Le performance vanno come log( Numero di lemmi univoci )

● GIST Index = Generalized Search Tree, Lossy. Data la loro natura necessitanodi un fetching della riga per scartare i false match.

Gli indici GIN e GIST necessitano un campo ts_vector da indicizzare, i GIN sono in grado di indicizzare anche un campo ts_query.




Chi sono?





Speed Up Search: IndexTips per velocizzare la ricerca :

● Partizionare: il tempo per creare un indice su una tabella di enormi dimensioni <> somma del tempo delle tabelle partizionate. Se avete 32 core, e avete 20 thread per reindicizzare, sarete almeno 20x più veloci [ NB: a meno di bottleneck di disco!]

● Indicizzare “quello che serve!“● Per gli indici GIST: creare colonna con datatype tsvector in

modo da evitare che il check automatico applichi il ts_vector che equivale ad un parsing del documento in meno. Tale colonna può essere popolata tramite trigger o in delay [es. update a fine giornata o con code etc]. Si può porla su altra tabella.

● Per gli indici GIN aumentare la maintenance_work_mem , evitare il sort a disco.

● NON USARE RANK SUI GIN

Chi sono?





Speed Up Search: IndexTips per velocizzare la ricerca :

ID documento ID to_tsvector(estrazione[documento])

ID info1 infon

Se ts_vector << documento il clustering factor dell’indice migliora , riducendo i random i/o sul sistema!

Chi sono?





Speed Up Search: Index

Esempio con GIST :

testfulltext=# \d textmaster Table "public.textmaster" Column | Type | Modifiers----------------+----------+----------- id | bigint | testo | text | testo_tsvector | tsvector |Triggers:

textmaster_trigger BEFORE INSERT ON textmaster FOR EACH ROW EXECUTE PROCEDURE textmaster_insert_trigger()Number of child tables: 701 (Use \d+ to list them.)

2.2 milioni circa di articoli di wikipedia

testfulltext=# select count(*) from textmaster ; count --------- 2184547(1 row)

Time: 656.115 ms




Chi sono?






Esempio con GIST :

testfulltext=# select id from textmaster where testo_tsvector::tsvector@@to_tsquery('italian_hunspell','Adige') ;

Time: 1198.207 ms

Append (cost=0.00..33561.94 rows=9217 width=8) -> Seq Scan on textmaster (cost=0.00..0.00 rows=1 width=8) Filter: (testo_tsvector @@ '''adige'''::tsquery) -> Bitmap Heap Scan on textslave_000 (cost=4.17..12.63 rows=4 width=8) Recheck Cond: (testo_tsvector @@ '''adige'''::tsquery) -> Bitmap Index Scan on textslave_000_idx01 (cost=0.00..4.17 rows=4 width=0)




Chi sono?






Esempio con GIST :

testfulltext=# select id,ts_rank_cd(testo_tsvector, query ) AS rank from textmaster , to_tsquery('italian_hunspell','Adige') querywhere query @@ testo_tsvectororder by rank DESClimit 10 ;

id | rank----------+------ 60879424 | 20.5 61789700 | 11.4 61917225 | 11.1 61925407 | 10.4 61725534 | 8.1 61893871 | 8 43858831 | 7.1 51809299 | 6.6 61921488 | 6.5 39893606 | 6

Time: 1195.045 ms




Chi sono?






Esempio con GIN :

select id from textmastergin where to_tsvector('italian_hunspell',testo)@@to_tsquery('italian_hunspell','Adige') ;

Time: 100.330 ms

Append (cost=0.00..42022.61 rows=11374 width=8) -> Seq Scan on textmastergin (cost=0.00..0.00 rows=1 width=8) Filter: (to_tsvector('italian_hunspell'::regconfig, testo) @@ '''adige'''::tsquery) -> Bitmap Heap Scan on textginslave_000 (cost=8.05..17.53 rows=6 width=8) Recheck Cond: (to_tsvector('italian_hunspell'::regconfig, testo) @@ '''adige'''::tsquery) -> Bitmap Index Scan on textginslave_000_idx01 (cost=0.00..8.05 rows=6 width=0) Index Cond: (to_tsvector('italian_hunspell'::regconfig, testo) @@ '''adige'''::tsquery) -> Bitmap Heap Scan on textginslave_001 (cost=8.05..17.53 rows=6 width=8) Recheck Cond: (to_tsvector('italian_hunspell'::regconfig, testo) @@ '''adige'''::tsquery) -> Bitmap Index Scan on textginslave_001_idx01 (cost=0.00..8.05 rows=6 width=0)




Chi sono?






Esempio con GIN : (bitmap scan on)

explain select id,ts_rank_cd(to_tsvector('italian_hunspell',testo), query ) AS rank from textmastergin , to_tsquery('italian_hunspell','Adige') querywhere query @@ to_tsvector('italian_hunspell',testo)

Limit (cost=42605.06..42605.08 rows=10 width=466) -> Sort (cost=42605.06..42633.64 rows=11433 width=466) Sort Key: (ts_rank_cd(to_tsvector('italian_hunspell'::regconfig, textmastergin.testo), query.query)) -> Nested Loop (cost=0.00..42358.00 rows=11433 width=466) -> Function Scan on query (cost=0.00..0.01 rows=1 width=32) -> Append (cost=0.00..42186.43 rows=11439 width=434) -> Seq Scan on textmastergin (cost=0.00..0.00 rows=1 width=40) Filter: (query.query @@ to_tsvector('italian_hunspell'::regconfig, testo)) -> Bitmap Heap Scan on textginslave_000 (cost=8.05..17.53 rows=6 width=40) Recheck Cond: (query.query @@ to_tsvector('italian_hunspell'::regconfig, testo)) -> Bitmap Index Scan on textginslave_000_idx01 (cost=0.00..8.05 rows=6 width=0) Index Cond: (query.query @@ to_tsvector('italian_hunspell'::regconfig, testo)) -> Bitmap Heap Scan on textginslave_001 (cost=8.05..17.53 rows=6 width=40) Recheck Cond: (query.query @@ to_tsvector('italian_hunspell'::regconfig, testo))




Chi sono?





Speed Up Search: IndexGIN => bitmapscan ON è positivo :

testfulltext=# set enable_bitmapscan=off;SETTime: 0.288 mstestfulltext=# select id,ts_rank_cd(to_tsvector('italian_hunspell',testo), query ) AS rank from textmastergin , to_tsquery('italian_hunspell','Adige') query where query @@ to_tsvector('italian_hunspell',testo) and id < 5000000;NOTICE: word is too long to be indexedDETAIL: Words longer than 2047 characters are ignored.Time: 9223.952 ms

testfulltext=# set enable_bitmapscan=on;SETTime: 0.219 mstestfulltext=# select id,ts_rank_cd(to_tsvector('italian_hunspell',testo), query ) AS rank from textmastergin , to_tsquery('italian_hunspell','Adige') query where query @@ to_tsvector('italian_hunspell',testo) and id < 5000000;Time: 99.553 ms

Chi sono?





Query FTS

Query

TIPS: Prima di applicare un predicato, verificare che il predicato abbia senso ! Usare numnode ( query ) > 0 !

all’interno della tsquery : | = OR ; & = AND ; ! = NOT

tsvector || tsvector == uniamo i vettori tsquery || tsquery == OR tra i predicatitsquery && tsquery == AND tra i predicati!! tsquery == NOT tsquery




Chi sono?





Query FTS

Weight

Abbiamo visto gli operatori booleani nel to_tsquery , oltre a questa forma di predicato vi è la possibilità di associare dei “pesi”:

‘lemma’[:posizione[peso]] , ‘lemma’[:posizione[peso]]

I pesi sono 4 : ‘A’,’B’,’C’,’D’ , di default il peso è ‘D’ = 1.0 ed è il più alto. Le altre categorie pesano rispettivamente 0.1,0.2,0.4.




Chi sono?





Query FTS

Funzioni di RANK

ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ])

ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ])

Si basa sul CDR ( http://www.sai.msu.su/~megera/wiki/NewExtentsBasedRanking )1. Calcola un peso per ogni extent ( parte del documento in cui vi

sono lemmi sufficienti a soddisfare il predicato ) che è inversamente proporzionale ai lemmi che non soddisfano il predicato presenti nel extent.

2. Fa una assunzione e assume tutti gli extent uguali.




http://www.sai.msu.su/~megera/wiki/NewExtentsBasedRanking



Chi sono?





Query FTS

Funzioni di RANK

metodologie di normalizzazione:

● 1 - 1+log (lunghezza documento)● 2 - lunghezza documento● 4 - distanza armonica tra gli extent ( solo CDR )● 8 - numero di lemmi univoci nel documento● 16 - 1+log (lunghezza documento)● 32 rank/(rank+1)




Chi sono?





Query FTS

Funzioni di Headline

ts_headline('configurazione',testo, ts_query) : permette l’estrazione di una parte del testo del documento significativo.

Headline + RANK = modalità di ricerca con presentazione del risultato più attinente




Chi sono?





Query FTS

Esempio

select id,ts_headline('italian_hunspell',testo, query) , ts_rank(to_tsvector('italian_hunspell',testo), query,4 ) AS rank from textmastergin , to_tsquery('italian_hunspell','Laghi & Veneto & !spam') query where query @@ to_tsvector('italian_hunspell',testo) and id < 30000000 order by rank DESC;

id | substr | rank ----------+----------------------------------------------------+------------- 28432994 | #REDIRECT [[Lago (Revine Lago)]] +| 0.160671 6452799 | lago omonimo e collega il Veneto con | 0.111233 25370226 | Lago di Santa Croce. L'immagine Ã¨ ottenuta | 0.000212121

Proviamo a velocizzare..

Chi sono?





TrigramIl trigram è una extension presente in Postgres per velocizzare le ricerche similari come i like e si basa sempre su indici Gin o Gist.

Tuttavia l’idea di fondo è di dividere le parole in triplette e sotto triplette e contare le similari per capire quanto una stringa sia simile ad un’altra.

testfulltext=# select show_trgm('Prato'); show_trgm --------------------------------- {" p"," pr",ato,pra,rat,"to "}




Chi sono?





Trigram

CREATE INDEX trgm_idx ON test_trgm USING gin (testo gin_trgm_ops);

SELECT * FROM test_trgm WHERE testo LIKE '%prato%pgday;

SELECT * FROM test_trgm WHERE testo ~ '(prato|pgday)';

tSELECT * FROM test_trgm WHERE testo LIKE '%Pgday%Prato%'; testo --------------------------- Il Pgday si tiene a Prato(1 row)




Chi sono?





Trigramtabella indice standard :

testfulltext=# EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM test_trgm2 WHERE testo LIKE '%Pgday%Prato%'; QUERY PLAN

----------------------------------------------------------------------------------------------------------- Seq Scan on test_trgm2 (cost=0.00..19786.21 rows=1 width=22) (actual time=0.010..200.559 rows=1 loops=1) Filter: (testo ~~ '%Pgday%Prato%'::text) Rows Removed by Filter: 1048576 Buffers: shared hit=6679 Total runtime: 200.582 ms(5 rows)




Chi sono?





Trigramtabella indice trigram :

testfulltext=# EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM test_trgm WHERE testo LIKE '%Pgday%Prato%'; QUERY PLAN

------------------------------------------------------------------------------------------- Bitmap Heap Scan on test_trgm (cost=3811.20..3812.81 rows=1 width=22) (actual time=0.024..0.024 rows=1 loops=1) Recheck Cond: (testo ~~ '%Pgday%Prato%'::text) Buffers: shared hit=8 -> Bitmap Index Scan on test_trgm_idx01 (cost=0.00..3811.20 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=1) Index Cond: (testo ~~ '%Pgday%Prato%'::text) Buffers: shared hit=7 Total runtime: 0.048 ms




Chi sono?





TrigramATTENZIONE il trigram NON va bene per i predicati = :

testfulltext=# EXPLAIN (ANALYZE,BUFFERS) SELECT * FROM test_trgm WHERE testo='Il Pgday si tiene a Prato'; QUERY PLAN

--------------------------------------------------------------------------------------------------------- Seq Scan on test_trgm (cost=0.00..19786.21 rows=1 width=22) (actual time=0.010..96.214 rows=1 loops=1) Filter: (testo = 'Il Pgday si tiene a Prato'::text) Rows Removed by Filter: 1048576 Buffers: shared hit=6679 Total runtime: 96.233 ms(5 rows)




●E-Mail: [email protected]

●URL: http://www.miriade.it

●BLOG : http://blogdba.miriade.it/

●SLIDE :

Domande?

Copyright 2012 Miriade S.p.a.Copyright 2012 Miriade S.p.a. Matteo Durighetto - [email protected] - ITPUG.org



Contacts



http://blogdba.miriade.it/




AttribuzioneNon commercialeCondividi allo stesso modo2.5 Italia

http://creativecommons.org/licenses/by-nc-sa/2.5/it/Copyright 2013 Miriade S.p.A. - http://www.miriade.it


Grazie per l'attenzione




License





full text search | speech by matteo durighetto | pgday.it 2013

Documents