Mønstermatching med LIKE, LIKNENDE med eller regulære uttrykk i PostgreSQL

Søket ditt er stort sett det optimale. Syntaksen blir ikke kortere, spørringen blir ikke mye raskere:

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

Hvis y du vil virkelig forkorte syntaksen , bruk et vanlig uttrykk med grener :

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

Eller litt raskere, med en karakterklasse :

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

En rask test uten indeks gir raskere resultater enn for SIMILAR TO i begge tilfeller for meg.
Med en passende B-treindeks på plass, vinner LIKE dette løpet etter størrelsesorden.

Les det grunnleggende om mønstermatching i håndboken .

Indeks for overlegen ytelse

Hvis du er bekymret med ytelse, lag en indeks som denne for større tabeller:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Gjør denne typen spørsmål raskere etter størrelsesorden. Spesielle hensyn gjelder for stedsspesifikk sorteringsrekkefølge. Les mer om operatørklasser i håndboken . Hvis du bruker standard » C » sted (de fleste ikke gjør det), vil en vanlig indeks (med standard operatørklasse) gjør.

En slik indeks er bare bra for venstreforankrede mønstre (matching fra begynnelsen av strengen).

SIMILAR TO eller regulære uttrykk med grunnleggende venstreankrede uttrykk kan også bruke denne indeksen. Men ikke med grener (B|D) eller tegnklasser [BD] (i det minste i testene mine på PostgreSQL 9.0).

Trigram-treff eller tekstsøk bruker spesielle GIN- eller GiST-indekser.

Oversikt over mønstermatchende operatører

• LIKE ( ~~ ) er enkelt og raskt, men begrenset i dets evner.
  ILIKE ( ~~* ) den saksfølsomme varianten.
  pg_trgm utvider indeksstøtte for begge.

• ~ (samsvar med vanlig uttrykk) er kraftig, men mer kompleks og kan være treg for alt mer enn grunnleggende uttrykk.

• SIMILAR TO er bare meningsløst . En merkelig halvras av LIKE og vanlige uttrykk. Jeg bruker den aldri. Se nedenfor.

• % er » likhet » operatør, levert av tilleggsmodulen pg_trgm. Se nedenfor.

• @@ er tekstsøkoperatøren. Se nedenfor.

pg_trgm – trigram matching

Begynner med PostgreSQL 9.1 du kan legge til rette for utvidelsen pg_trgm for å gi indeksstøtte for alle LIKE / ILIKE mønster (og enkle regexp-mønstre med ~) ved hjelp av en GIN- eller GiST-indeks.

Detaljer, eksempel og lenker:

• Hvordan implementeres LIKE?

pg_trgm gir også disse operatørene :

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

Tekstsøk

Er en spesiell type mønstermatching med separate infrastruktur- og indeksetyper. Den bruker ordbøker og stemming og er et flott verktøy for å finne ord i dokumenter, spesielt for naturlige språk.

Prefiks som samsvarer med støttes også:

• Få delvis samsvar fra GIN-indeksert TSVECTOR-kolonne

Samt frasesøk siden Postgres 9.6:

• Hvordan søke etter orddelte ord i PostgreSQL fulltekstsøk?

Vurder introduksjon i håndboken og oversikten over operatører og funksjoner .

Ekstra verktøy for fuzzy strengmatching

Tilleggsmodulen fuzzystrmatch tilbyr noen flere alternativer, men ytelsen er generelt dårligere enn alle de ovennevnte.

Spesielt forskjellige implementeringer av levenshtein() -funksjonen kan være medvirkende.

Hvorfor er vanlige uttrykk (~) alltid raskere enn SIMILAR TO?

Svaret er enkelt. SIMILAR TO uttrykk skrives om til regulære uttrykk internt. Så for hvert SIMILAR TO uttrykk er det minst ett raskere vanlig uttrykk (som sparer overhead for å omskrive uttrykket). Det er ingen ytelsesgevinst ved å bruke SIMILAR TO noensinne .

Og enkle uttrykk som kan gjøres med LIKE (~~) er raskere med LIKE uansett.

SIMILAR TO støttes bare i PostgreSQL fordi det havnet i tidlige utkast til SQL-standarden. De har fortsatt ikke blitt kvitt det. Men det er planer om å fjerne det og inkludere regexp-kamper i stedet – eller så hørte jeg det.

EXPLAIN ANALYZE avslører det. Bare prøv med hvilken som helst tabell selv!

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

Avslører:

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

SIMILAR TO har blitt skrevet om med et regulært uttrykk (~).

Ultimate ytelse for denne spesielle saken

Men EXPLAIN ANALYZE avslører mer. Prøv med ovennevnte indeks på plass:

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

Avslø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 en indeks som ikke er lokalbevisst (text_pattern_ops eller bruker lokal C) enkle venstreankrede uttrykk skrives om med disse tekstmønsteroperatørene: ~>=~, ~<=~, ~>~, ~<~. Dette er tilfellet for ~, ~~ eller SIMILAR TO likt.

Det samme gjelder indekser på varchar typer med varchar_pattern_ops eller char med bpchar_pattern_ops.

Så anvendt til det opprinnelige spørsmålet, dette er raskest mulig måte :

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

Hvis du tilfeldigvis søker etter tilstøtende initialer , kan du selvfølgelig forenkle videre:

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

Gevinsten ved vanlig bruk av ~ eller ~~ er liten. Hvis ytelsen ikke er ditt viktigste krav, bør du bare holde deg til standardoperatørene – når du kommer til det du allerede har i spørsmålet.

Kommentarer

• OP har ‘ t en indeks på navn, men vet du tilfeldigvis, hvis de gjorde det, ville deres opprinnelige forespørsel innebære to områdesøk og similar en skanning?
• @MartinSmith: En rask test med EXPLAIN ANALYZE viser 2 bitmap-indeksskanninger.Flere bitmap-indeksskanninger kan kombineres ganske raskt.
• Takk. Så ville det være noen miletall med å erstatte OR med UNION ALL eller erstatte name LIKE 'B%' med name >= 'B' AND name <'C' i Postgres?
• @MartinSmith: UNION vant ‘ t men ja, å kombinere områdene i en WHERE -sats vil øke spørringen. Jeg har lagt til mer i svaret mitt. Selvfølgelig må du ta hensyn til lokaliteten din. Lokalbevisst søk er alltid tregere.
• @a_horse_with_no_name: Jeg forventer ikke. De nye funksjonene til pg_tgrm med GIN-indekser er en godbit for generisk tekstsøk. Et søk som er forankret i starten, er allerede raskere enn det.

Hva med å legge til en kolonne i bord. Avhengig av 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 støtter ikke beregnede kolonner i basistabeller a la SQL Server , men den nye kolonnen kan opprettholdes via utløseren. Åpenbart vil denne nye kolonnen bli indeksert.

Alternativt, en indeks på et uttrykk vil gi deg det samme, billigere. F.eks:

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

Spørringer som samsvarer med uttrykket under deres forhold, kan bruke denne indeksen.

På denne måten blir ytelsestreffet tatt når dataene blir opprettet eller endret, så det kan bare være aktuelt for et miljø med lav aktivitet (dvs. mye færre skriver enn lesninger).

Du kan prøve

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

Jeg har ingen anelse om det ovennevnte eller det opprinnelige uttrykket ditt er sargable i Postgres. Ikke.

Hvis du oppretter den foreslåtte indeksen, vil det også være interessert i å høre hvordan dette sammenlignes med de andre alternativene.

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 fungerte og jeg fikk en kostnad på 1,19 hvor jeg hadde 1,25. Takk!

Det jeg har gjort tidligere, overfor et lignende ytelsesproblem, er å øk ASCII-tegnet til den siste bokstaven, og gjør en MELLOM. Du får da den beste ytelsen, for en delmengde av LIKE-funksjonaliteten. Selvfølgelig fungerer det bare i visse situasjoner, men for ultra-store datasett der du for eksempel søker på et navn, får det ytelsen til å gå fra dyp til akseptabel.

Veldig gammelt spørsmål, men jeg fant en annen rask løsning på dette problemet:

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

Siden funksjon ascii ( ) ser bare på strengens første tegn.

Kommentarer

• Bruker dette en indeks på (name)?

For å sjekke initialer bruker jeg ofte casting til "char" (med doble anførselstegn). Det er ikke bærbart, men veldig raskt. Internt, detoasts bare teksten og returnerer det første tegnet, og «char» sammenligningsoperasjoner er veldig raske fordi typen er 1 byte fast lengde:

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

Merk at casting til "char" er raskere enn ascii() -slusingen av @ Sole021, men den er ikke UTF8-kompatibel (eller annen koding for den saken), returnerer bare den første byten, så skal bare brukes i tilfeller der sammenligningen er mot vanlige gamle 7-biters ASCII-tegn.