Mønstermatchning med LIKE, LIGNENDE MED eller regulære udtryk i PostgreSQL

Din forespørgsel er stort set det optimale. Syntaks vinder ikke meget kortere, forespørgsel bliver ikke meget hurtigere:

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

Hvis y du vil virkelig forkorte syntaksen , brug et regulært udtryk med grene :

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

Eller lidt hurtigere med en karakterklasse :

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

En hurtig test uden indeks giver hurtigere resultater end for SIMILAR TO i begge tilfælde for mig.
Med et passende B-Tree-indeks på plads vinder LIKE dette løb med størrelsesordener.

Læs det grundlæggende om mønstermatchning i manualen .

Indeks for overlegen ydeevne

Hvis du er bekymret med ydeevne skal du oprette et indeks som dette for større tabeller:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Gør denne form for forespørgsel hurtigere efter størrelsesordener. Særlige overvejelser gælder for landespecifik sortering. Læs mere om operatørklasser i manualen . Hvis du bruger standard ” C ” landestandard (de fleste mennesker gør ikke), vil et almindeligt indeks (med standardoperatørklasse) gør.

Et sådant indeks er kun godt for venstreforankrede mønstre (matching fra begyndelsen af strengen).

SIMILAR TO eller regulære udtryk med grundlæggende venstreankrede udtryk kan også bruge dette indeks. Men ikke med grene (B|D) eller tegnklasser [BD] (i det mindste i mine tests på PostgreSQL 9.0).

Trigram-matches eller tekstsøgning bruger specielle GIN- eller GiST-indekser.

Oversigt over mønstermatchende operatorer

• LIKE ( ~~ ) er enkel og hurtig, men begrænset i dets evner.
  ILIKE ( ~~* ) den sagsfølsomme variant.
  pg_trgm udvider indeksunderstøttelse for begge.

• ~ (match med regulært udtryk) er kraftigt, men mere komplekst og kan være langsomt til alt andet end grundlæggende udtryk.

• SIMILAR TO er bare meningsløst . En ejendommelig halv race af LIKE og regulære udtryk. Jeg bruger det aldrig. Se nedenfor.

• % er ” lighed ” operatør, leveret af det ekstra modul pg_trgm. Se nedenfor.

• @@ er tekstsøgningsoperatoren. Se nedenfor.

pg_trgm – trigram matching

Begynder med PostgreSQL 9.1 du kan lette udvidelsen pg_trgm for at give indeksunderstøttelse til enhver LIKE / ILIKE mønster (og enkle regexp-mønstre med ~) ved hjælp af en GIN- eller GiST-indeks.

Detaljer, eksempel og links:

• Hvordan implementeres LIKE?

pg_trgm giver også disse operatører :

• % – ” lighed ” operator
• <% (kommutator: %>) – ” word_similarity ” operator i Postgres 9.6 eller senere
• <<% (kommutator: %>>) – ” strict_word_similarity ” operatør i Postgres 11 eller senere

Tekstsøgning

Er en speciel type mønstermatchning med separate infrastruktur- og indekstyper. Det bruger ordbøger og stemming og er et godt værktøj til at finde ord i dokumenter, især til naturlige sprog.

Præfiks, der matcher understøttes også:

• Få delvist match fra GIN-indekseret TSVECTOR-kolonne

Samt frasesøgning siden Postgres 9.6:

• Sådan søger du orddelede ord i PostgreSQL-fuldtekstsøgning?

Overvej introduktion i manualen og oversigt over operatører og funktioner .

Yderligere værktøjer til fuzzy strengtilpasning

Det ekstra modul fuzzystrmatch tilbyder nogle flere muligheder, men ydeevne er generelt dårligere end alle ovenstående.

Især forskellige implementeringer af levenshtein() -funktionen kan være medvirkende.

Hvorfor er regulære udtryk (~) altid hurtigere end SIMILAR TO?

Svaret er simpelt. SIMILAR TO udtryk omskrives til regulære udtryk internt. Så for hvert SIMILAR TO udtryk er der mindst et hurtigere regulært udtryk (der sparer omkostningerne ved omskrivning af udtrykket). Der er ingen præstationsgevinst ved at bruge SIMILAR TO nogensinde .

Og enkle udtryk, der kan gøres med LIKE (~~), er hurtigere med LIKE alligevel.

SIMILAR TO understøttes kun i PostgreSQL, fordi det endte i tidlige kladder til SQL-standarden. De har stadig ikke sluppet af med det. Men der er planer om at fjerne det og medtage regexp-matches i stedet – eller så hørte jeg det.

EXPLAIN ANALYZE afslører det. Prøv bare med enhver tabel selv!

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

Afslører:

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

SIMILAR TO er blevet omskrevet med et regulært udtryk (~).

Den ultimative ydeevne til denne særlige sag

Men EXPLAIN ANALYZE afslører mere. Prøv med det førnævnte indeks på plads:

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

afslører:

... -> 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)) 

Internt med et indeks, der ikke er lokalbevidst (text_pattern_ops eller bruger landestandard C) enkle venstreankrede udtryk omskrives med disse tekstmønsteroperatorer: ~>=~, ~<=~, ~>~, ~<~. Dette er tilfældet for ~, ~~ eller SIMILAR TO ens.

Det samme gælder for indekser på varchar typer med varchar_pattern_ops eller char med bpchar_pattern_ops.

Så anvendt til det oprindelige spørgsmål er dette hurtigst mulig måde :

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

Hvis du tilfældigvis skulle søge efter tilstødende initialer , kan du selvfølgelig forenkle yderligere:

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

Gevinsten ved almindelig brug af ~ eller ~~ er lille. Hvis ydeevne ikke er dit største krav, skal du bare holde fast ved standardoperatørerne – når frem til det, du allerede har i spørgsmålet.

Kommentarer

• OPet har ikke ‘ t et indeks på navn, men ved du tilfældigvis, hvis de gjorde det, ville deres oprindelige forespørgsel indebære 2 rækkefølge og similar en scanning?
• @MartinSmith: En hurtig test med EXPLAIN ANALYZE viser 2 bitmap-indeksscanninger.Flere bitmap-indeksscanninger kan kombineres ret hurtigt.
• Tak. Så ville der være nogen kilometertal ved at erstatte OR med UNION ALL eller erstatte name LIKE 'B%' med name >= 'B' AND name <'C' i Postgres?
• @MartinSmith: UNION vandt ‘ t men ja, ved at kombinere intervallerne i en WHERE -klausul vil forespørgslen blive hurtigere. Jeg har tilføjet mere til mit svar. Selvfølgelig skal du tage din lokalitet i betragtning. Lokalbevidst søgning er altid langsommere.
• @a_horse_with_no_name: Jeg forventer ikke. De nye funktioner i pg_tgrm med GIN-indekser er en godbid til generisk tekstsøgning. En søgning, der er forankret i starten, er allerede hurtigere end det.

Hvad med at tilføje en kolonne til bord. Afhængigt af dine faktiske krav:

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

PostgreSQL understøtter ikke beregnede kolonner i basistabeller a la SQL Server , men den nye kolonne kan opretholdes via trigger. Naturligvis vil denne nye kolonne blive indekseret.

Alternativt et indeks på et udtryk ville give dig det samme, billigere. F.eks .:

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

Forespørgsler, der matcher udtrykket i deres betingelser, kan bruge dette indeks.

På denne måde tages præstationshit, når dataene oprettes eller ændres, så det kan kun være passende i et miljø med lav aktivitet (dvs. meget færre skriver end læsninger).

Du kunne prøve

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

Jeg har ingen idé om, hvorvidt ovenstående eller dit originale udtryk er sargable i Postgres eller ej.

Hvis du opretter det foreslåede indeks, ville det også være interesseret i at høre hvordan dette kan sammenlignes med de andre muligheder.

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 

Kommentarer

• Det fungerede, og jeg fik en pris på 1.19 hvor jeg havde 1.25. Tak!

Hvad jeg har gjort tidligere, står over for et lignende præstationsproblem, er at øg det sidste bogstavs ASCII-karakter, og udfør MELLEM. Derefter får du den bedste ydeevne for en delmængde af LIKE-funktionaliteten. Selvfølgelig fungerer det kun i visse situationer, men for ultra-store datasæt, hvor du f.eks. Søger på et navn, får det ydeevne til at gå fra uhyggelig til acceptabel.

Meget gammelt spørgsmål, men jeg fandt en anden hurtig løsning på dette problem:

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

Siden funktion ascii ( ) ser kun på strengens første tegn.

Kommentarer

• Bruger dette et indeks på (name)?

Til kontrol af initialer bruger jeg ofte casting til "char" (med dobbelt anførselstegn). Det er ikke bærbart, men meget hurtigt. Internt detoaster det simpelthen teksten og returnerer det første tegn, og “char” sammenligningsoperationer er meget hurtige, fordi typen er 1 byte fast længde:

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

Bemærk, at casting til "char" er hurtigere end ascii() -slusningen af @ Sole021, men den er ikke UTF8-kompatibel (eller nogen anden kodning til det spørgsmål), returnerer simpelthen den første byte, så bør kun bruges i tilfælde, hvor sammenligningen er med almindelige gamle 7-bit ASCII-tegn.