Potrivirea modelelor cu LIKE, SIMILAR TO sau expresii regulate în PostgreSQL

Interogarea dvs. este aproape optimă. Sintaxa nu va fi mult mai scurtă, interogarea nu va fi mult mai rapidă:

SELECT name FROM spelers WHERE name LIKE "B%" OR name LIKE "D%" ORDER BY 1; 

Dacă y chiar doriți să scurtați sintaxa , să folosiți o expresie regulată cu ramuri :

... WHERE name ~ "^(B|D).*" 

Sau ușor mai rapid, cu o clasă de caractere :

... WHERE name ~ "^[BD].*" 

Un test rapid fără index dă rezultate mai rapide decât pentru SIMILAR TO în ambele cazuri pentru mine.
Cu un index B-Tree adecvat, LIKE câștigă această cursă în ordine de mărime.

Citiți elementele de bază despre potrivirea modelului din manual .

Index pentru performanțe superioare

Dacă sunteți îngrijorat cu performanță, creați un astfel de index pentru tabele mai mari:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Face acest tip de interogare mai rapid în funcție de ordinele de mărime. Se aplică considerații speciale pentru ordinea de sortare specifică localizării. Citiți mai multe despre clase de operator în manual . Dacă utilizați setările locale ” C ” standard (majoritatea oamenilor nu au), va apărea un index simplu (cu clasa de operator implicită) do.

Un astfel de index este bun numai pentru modelele ancorate la stânga (potrivite de la începutul șirului).

SIMILAR TO sau expresiile regulate cu expresii de bază ancorate la stânga pot utiliza și acest index. Dar nu cu ramuri (B|D) sau clase de caractere [BD] (cel puțin în testele mele pe PostgreSQL 9.0).

Potrivirile trigramelor sau căutarea textului folosesc indexuri GIN sau GiST speciale.

Prezentare generală a operatorilor de potrivire a modelelor

• LIKE ( ~~ ) este simplu și rapid, dar limitat în capacitățile sale.
  ILIKE ( ~~* ) varianta insensibilă la majuscule.
  pg_trgm extinde suportul indexului pentru ambele.

• ~ (potrivirea expresiei regulate) este puternic, dar mai complex și poate fi lent pentru orice altceva decât expresiile de bază.

• SIMILAR TO este doar inutil . O jumătate de rasă specifică LIKE și expresii regulate. Nu-l folosesc niciodată. Vedeți mai jos.

• % este operatorul ” similaritate „, furnizat de modulul suplimentar pg_trgm. Vedeți mai jos.

• @@ este operatorul de căutare text. Vedeți mai jos.

pg_trgm – potrivirea trigramelor

Începând cu PostgreSQL 9.1 puteți facilita extensia pg_trgm pentru a oferi suport pentru index pentru orice LIKE / ILIKE model (și modele simple de regexp cu ~) utilizând un Index GIN sau GiST.

Detalii, exemplu și linkuri:

• Cum se implementează LIKE?

pg_trgm furnizează și acești operatori :

• % – ” similaritate ” operator
• <% (comutator: %>) – ” word_similarity ” operator în Postgres 9.6 sau o versiune ulterioară
• <<% (comutator: %>>) – ” strict_word_similarity ” operator în Postgres 11 sau o versiune ulterioară

Căutare text

Este un tip special de potrivire de tipare cu infrastructură separată și tipuri de index. Folosește dicționare și stemming și este un instrument excelent pentru a găsi cuvinte în documente, în special pentru limbile naturale.

Potrivirea prefixului este, de asemenea, acceptat:

• Obțineți o potrivire parțială din coloana TSVECTOR indexată GIN

La fel ca și căutare expresie de la Postgres 9.6:

• Cum să căutați cuvinte cu cratime în căutarea textului complet PostgreSQL?

Luați în considerare introducere în manual și prezentare generală a operatorilor și funcțiilor .

Instrumente suplimentare pentru potrivirea șirurilor fuzzy

Modulul suplimentar fuzzystrmatch oferă câteva opțiuni, dar performanța este, în general, inferioară tuturor celor de mai sus.

În special, diverse implementările funcției levenshtein() pot fi instrumentale.

De ce sunt expresiile regulate (~) întotdeauna mai rapide decât SIMILAR TO?

Răspunsul este simplu. SIMILAR TO expresiile sunt rescrise intern în expresii regulate. Deci, pentru fiecare SIMILAR TO expresie, există cel puțin o expresie regulată mai rapidă (care salvează cheltuielile generale pentru rescrierea expresiei). Nu există niciun câștig de performanță în utilizarea SIMILAR TO vreodată .

Și expresiile simple care pot fi făcute cu LIKE (~~) sunt mai rapide cu LIKE oricum.

SIMILAR TO este acceptat numai în PostgreSQL, deoarece a ajuns în primele proiecte ale standardului SQL. Încă nu au scăpat de el. Dar există planuri de a-l elimina și de a include în schimb potriviri regexp – sau așa am auzit.

EXPLAIN ANALYZE îl dezvăluie. Încercați singur cu orice tabel!

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name SIMILAR TO "B%"; 

Prezintă:

... Seq Scan on spelers (cost= ... Filter: (name ~ "^(?:B.*)$"::text) 

SIMILAR TO a fost rescris cu o expresie regulată (~).

Performanță finală pentru acest caz particular

Dar EXPLAIN ANALYZE dezvăluie mai multe. Încercați, cu indexul menționat anterior la locul său:

EXPLAIN ANALYZE SELECT * FROM spelers WHERE name ~ "^B.*; 

Revelează:

... -> Bitmap Heap Scan on spelers (cost= ... Filter: (name ~ "^B.*"::text) -> Bitmap Index Scan on spelers_name_text_pattern_ops_idx (cost= ... Index Cond: ((prod ~>=~ "B"::text) AND (prod ~<~ "C"::text)) 

Intern, cu un index care nu este conștient de localizare (text_pattern_ops sau folosind localizarea C) expresiile simple ancorate la stânga sunt rescrise cu acești operatori de tipare de text: ~>=~, ~<=~, ~>~, ~<~. Acesta este cazul pentru ~, ~~ sau SIMILAR TO deopotrivă.

Același lucru este valabil și pentru indici pe tipurile varchar cu varchar_pattern_ops sau char cu bpchar_pattern_ops.

Deci, aplicat la întrebarea inițială, acesta este cel mai rapid mod posibil :

SELECT name FROM spelers WHERE name ~>=~ "B" AND name ~<~ "C" OR name ~>=~ "D" AND name ~<~ "E" ORDER BY 1; 

Desigur, dacă ar trebui să căutați inițiale adiacente , puteți simplifica în continuare:

WHERE name ~>=~ "B" AND name ~<~ "D" -- strings starting with B or C 

Câștigul față de utilizarea simplă a ~ sau ~~ este mic. Dacă performanța nu este cerința dvs. principală, ar trebui să rămâneți cu operatorii standard – ajungând la ceea ce aveți deja în întrebare.

Comentarii

• OP nu are ‘ un index pe nume, dar știi, dacă ar avea, interogarea lor inițială ar implica 2 căutări de gamă și similar o scanare?
• @MartinSmith: Un test rapid cu EXPLAIN ANALYZE afișează 2 scanări ale indexului bitmap.Scanările multiple ale indexului bitmap pot fi combinate destul de repede.
• Mulțumesc. Așa că ar exista vreun milaj cu înlocuirea OR cu UNION ALL sau înlocuirea name LIKE 'B%' cu name >= 'B' AND name <'C' în Postgres?
• @MartinSmith: UNION a câștigat ‘ t dar, da, combinarea intervalelor într-o clauză WHERE va accelera interogarea. Am adăugat mai multe la răspunsul meu. Desigur, trebuie să țineți cont de localizare. Căutarea locală este întotdeauna mai lentă.
• @a_horse_with_no_name: mă aștept să nu. Noile capacități ale pg_tgrm cu indexuri GIN sunt un tratament pentru căutarea generică a textului. O căutare ancorată la început este deja mai rapidă decât atât.

Ce zici de adăugarea unei coloane la masa. În funcție de cerințele dvs. reale:

person_name_start_with_B_or_D (Boolean) person_name_start_with_char CHAR(1) person_name_start_with VARCHAR(30) 

PostgreSQL nu acceptă coloane calculate în tabelele de bază la SQL Server , dar noua coloană poate fi menținută prin declanșator. Evident, această nouă coloană ar fi indexată.

Alternativ, un index pe o expresie ți-ar oferi același lucru, mai ieftin. De exemplu:

CREATE INDEX spelers_name_initial_idx ON spelers (left(name, 1)); 

Interogările care se potrivesc cu expresia în condițiile lor pot utiliza acest index.

În acest fel, accesarea performanței este luată atunci când datele sunt create sau modificate, deci poate fi adecvată numai pentru un mediu cu activitate redusă (adică mult mai puține scrieri decât citiri).

Ai putea încerca

SELECT s.name FROM spelers s WHERE s.name SIMILAR TO "(B|D)%" ORDER BY s.name 

Nu am nici o idee dacă cele de mai sus sau expresia dvs. originală sunt sau nu înscrise în Postgres.

Dacă creați indexul sugerat, ar fi, de asemenea, interesat să aflați cum acest lucru se compară cu celelalte opțiuni.

SELECT name FROM spelers WHERE name >= "B" AND name < "C" UNION ALL SELECT name FROM spelers WHERE name >= "D" AND name < "E" ORDER BY name 

Comentarii

• A funcționat și am obținut un cost de 1,19 unde am avut 1,25. Mulțumesc!

Ceea ce am făcut în trecut, confruntat cu o problemă similară de performanță, este să creșteți caracterul ASCII al ultimei litere și faceți un BETWEEN. Obțineți apoi cele mai bune performanțe, pentru un subset al funcționalității LIKE. Desigur, funcționează doar în anumite situații, dar pentru seturile de date ultra-mari în care căutați un nume, de exemplu, face ca performanța să treacă de la abis la acceptabilă.

Întrebare foarte veche, dar am găsit o altă soluție rapidă la această problemă:

SELECT s.name FROM spelers s WHERE ascii(s.name) in (ascii("B"),ascii("D")) ORDER BY 1 

Întrucât funcția ascii ( ) se uită doar la primul caracter al șirului.

Comentarii

• Utilizează acest lucru un index pe (name)?

Pentru verificarea inițialelor, folosesc deseori castingul către "char" (cu ghilimele duble). Nu este portabil, dar foarte rapid. Intern, pur și simplu detoastează textul și returnează primul caracter, iar operațiile de comparație „char” sunt foarte rapide deoarece tipul are o lungime fixă de 1 octeț:

SELECT s.name FROM spelers s WHERE s.name::"char" =ANY( ARRAY[ "char" "B", "D" ] ) ORDER BY 1 

Rețineți că aruncarea pe "char" este mai rapidă decât slut ascii() de @ Sole021, dar nu este compatibilă cu UTF8 (sau orice altă codificare pentru care contează), returnând pur și simplu primul octet, deci ar trebui să fie utilizat numai în cazurile în care comparația este comparată cu caractere ASCII vechi simple pe 7 biți.

Există încă două metode care nu sunt menționate încă pentru a trata astfel de cazuri:

1. parțial (sau partiționat – dacă este creat manual pentru gama completă) – cel mai util atunci când este necesar doar un subset de date (de exemplu în timpul unor lucrări de întreținere sau temporare pentru anumite raportări):

   CREATE INDEX ON spelers WHERE name LIKE "B%" 

2. partiționarea tabelului în sine (folosind primul caracter ca cheie de partiționare) – această tehnică este în special must h luând în considerare în PostgreSQL 10+ (partiționare mai puțin dureroasă) și 11+ (tăierea partiției în timpul executării interogării).

Mai mult, dacă datele dintr-un tabel sunt sortate, se poate beneficiați de utilizarea index BRIN (peste primul caracter).

Probabil mai rapid pentru a face o comparație cu un singur caracter:

SUBSTR(s.name,1,1)="B" OR SUBSTR(s.name,1,1)="D" 

Comentarii

• Nu într-adevăr. column LIKE 'B%' va fi mai eficient decât utilizarea funcției de șir pe coloană.