Mönstermatchning med LIKE, LIKNANDE med eller reguljära uttryck i PostgreSQL

Din fråga är ganska mycket optimal. Syntax blir inte mycket kortare, sökningen blir inte mycket snabbare:

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

Om y Du vill verkligen förkorta syntaxen , använd ett reguljärt uttryck med grenar :

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

Eller något snabbare, med en karaktärsklass :

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

Ett snabbtest utan index ger snabbare resultat än för SIMILAR TO i båda fallen för mig.
Med ett lämpligt B-Tree-index på plats vinner LIKE detta lopp med storleksordningar.

Läs grunderna om mönstermatchning i handboken .

Index för överlägsen prestanda

Om du är orolig med prestanda, skapa ett index så här för större tabeller:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Gör den här typen av frågor snabbare efter storleksordningar. Särskilda överväganden gäller för landsspecifik sorteringsordning. Läs mer om operatörsklasser i handboken . Om du använder standard ” C ” locale (de flesta gör inte) kommer ett vanligt index (med standardoperatörsklass) att visas gör.

Ett sådant index är bara bra för vänsterförankrade mönster (matchande från början av strängen).

SIMILAR TO eller reguljära uttryck med grundläggande vänsterförankrade uttryck kan också använda detta index. Men inte med grenar (B|D) eller teckenklasser [BD] (åtminstone i mina tester på PostgreSQL 9.0).

Trigram-matchningar eller textsökning använder speciella GIN- eller GiST-index.

Översikt över mönstermatchningoperatorer

• LIKE ( ~~ ) är enkelt och snabb men begränsad i dess funktioner.
  ILIKE ( ~~* ) den skiftlägeskänsliga varianten.
  pg_trgm utökar indexstöd för båda.

• ~ (matchning med vanligt uttryck) är kraftfull men mer komplex och kan vara långsam för allt annat än grundläggande uttryck.

• SIMILAR TO är bara meningslöst . En märklig halvras av LIKE och reguljära uttryck. Jag använder den aldrig. Se nedan.

• % är ” likhet ” operatör, tillhandahållen av den extra modulen pg_trgm. Se nedan.

• @@ är textsökningsoperatören. Se nedan.

pg_trgm – trigrammatchning

Börjar med PostgreSQL 9.1 du kan underlätta förlängningen pg_trgm för att ge indexstöd för någon LIKE / ILIKE mönster (och enkla regexp-mönster med ~) med en GIN- eller GiST-index.

Detaljer, exempel och länkar:

• Hur implementeras LIKE?

pg_trgm ger också dessa operatörer :

• % – ” likhet ” operatör
• <% (kommutator: %>) – ” word_similarity ” operatör i Postgres 9.6 eller senare
• <<% (kommutator: %>>) – ” strict_word_similarity ” operatör i Postgres 11 eller senare

Textsökning

Är en speciell typ av mönstermatchning med separata infrastruktur- och indextyper. Den använder ordböcker och stemming och är ett utmärkt verktyg för att hitta ord i dokument, särskilt för naturliga språk.

Prefix som matchar stöds också:

• Få partiell matchning från GIN-indexerad TSVECTOR-kolumn

Samt frasökning sedan Postgres 9.6:

• Hur man söker avstavade ord i PostgreSQL-fulltextsökning?

Tänk på introduktion i handboken och översikt över operatörer och funktioner .

Ytterligare verktyg för fuzzy strängmatchning

Tilläggsmodulen fuzzystrmatch erbjuder några fler alternativ, men prestanda är i allmänhet sämre än alla ovanstående.

I synnerhet olika implementeringar av levenshtein() -funktionen kan vara avgörande.

Varför är reguljära uttryck (~) alltid snabbare än SIMILAR TO?

Svaret är enkelt. SIMILAR TO uttryck skrivs om till reguljära uttryck internt. Så för varje SIMILAR TO -uttryck finns åtminstone ett snabbare reguljärt uttryck (som sparar kostnaden för att skriva om uttrycket). Det finns ingen prestationsvinster när du använder SIMILAR TO någonsin .

Och enkla uttryck som kan göras med LIKE (~~) är snabbare med LIKE i alla fall.

SIMILAR TO stöds bara i PostgreSQL eftersom det hamnade i tidiga utkast till SQL-standarden. De har fortfarande inte blivit av med det. Men det finns planer på att ta bort det och inkludera regexp-matchningar istället – eller så hörde jag det.

EXPLAIN ANALYZE avslöjar det. Försök bara med valfri tabell själv!

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

Avslöjar:

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

SIMILAR TO har skrivits om med ett reguljärt uttryck (~).

Ultimate performance för just detta fall

Men EXPLAIN ANALYZE avslöjar mer. Försök med det tidigare nämnda indexet på plats:

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

Avslöjar:

... -> 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 ett index som inte är lokalmedveten (text_pattern_ops eller använder språk C) enkla vänsterankrade uttryck skrivs om med dessa textmönsteroperatörer: ~>=~, ~<=~, ~>~, ~<~. Detta är fallet för ~, ~~ eller SIMILAR TO lika.

Detsamma gäller index för varchar -typer med varchar_pattern_ops eller char med bpchar_pattern_ops.

Så tillämpas till den ursprungliga frågan är detta snabbast möjliga sätt :

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

Om du råkar söka efter intilliggande initialer kan du naturligtvis förenkla ytterligare:

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

Vinsten över vanlig användning av ~ eller ~~ är liten. Om prestanda inte är ditt allra högsta krav, ska du bara hålla fast vid standardoperatörerna – när du når det du redan har i frågan. > OP: n har ’ t ett index på namn men vet du att om de gjorde det, skulle deras ursprungliga fråga innebära två intervallsökningar och similar en skanning?

Vad sägs om att lägga till en kolumn i tabell. Beroende på dina faktiska krav:

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

PostgreSQL stöder inte beräknade kolumner i bastabeller a la SQL Server men den nya kolumnen kan upprätthållas via trigger. Självklart skulle den nya kolumnen indexeras.

Alternativt kan ett -index på ett uttryck skulle ge dig samma, billigare. Exempel:

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

Frågor som matchar uttrycket under deras förhållanden kan använda detta index.

På detta sätt tas prestandatreffet när data skapas eller ändras, så det kan bara vara lämpligt för en miljö med låg aktivitet (dvs. mycket färre skrivningar än läsningar).

Du kan försöka

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

Jag har ingen aning om antingen ovanstående eller ditt ursprungliga uttryck är sargabla i Postgres.

Om du skapar det föreslagna indexet skulle det också vara intresserat att höra hur detta jämförs med de andra alternativen.

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 fungerade och jag fick en kostnad på 1,19 där jag hade 1,25. Tack!

Vad jag har gjort tidigare, inför en liknande prestationsfråga, är att öka ASCII-karaktären för den sista bokstaven och gör ett MELLAN. Du får då bästa prestanda för en delmängd av LIKE-funktionaliteten. Självklart fungerar det bara i vissa situationer, men för extremt stora datamängder där du till exempel söker på ett namn får prestanda att gå från dystra till acceptabla.

Mycket gammal fråga, men jag hittade en annan snabb lösning på detta problem:

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

Eftersom funktionen ascii ( ) ser bara på strängens första tecken.

Kommentarer

• Använder detta ett index på (name)?

För att kontrollera initialer använder jag ofta casting till "char" (med dubbla citat). Det är inte bärbart, men väldigt snabbt. Internt avfyrar det helt enkelt texten och returnerar det första tecknet, och ”char” -jämförelseoperationer är mycket snabba eftersom typen är 1 byte fast längd:

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

Observera att castning till "char" är snabbare än ascii() -slutningen med @ Sole021, men den är inte UTF8-kompatibel (eller någon annan kodning för den frågan), returnerar helt enkelt den första byten, så ska endast användas i fall där jämförelsen är mot vanliga gamla 7-bitars ASCII-tecken.

Förmodligen snabbare att göra en enstaka teckenjämförelse:

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

Kommentarer

• Inte verkligen. column LIKE 'B%' blir effektivare än att använda strängfunktionen i kolumnen.