Porovnávání vzorů s LIKE, PODOBNÝMI nebo regulárními výrazy v PostgreSQL

Váš dotaz je do značné míry optimální. Syntaxe nebude mnohem kratší, dotaz nebude mnohem rychlejší:

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

Pokud y opravdu chcete zkrátit syntaxi , použijte regulární výraz s větvemi :

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

Nebo mírně rychlejší, s třídou znaků :

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

Rychlý test bez indexu přináší rychlejší výsledky než pro SIMILAR TO v obou případech pro mě.
Se zavedeným vhodným indexem B-Tree vyhrává LIKE tento závod řádově.

Přečtěte si základní informace o porovnávání vzorů v příručce .

Index pro lepší výkon

Pokud máte obavy s výkonem vytvořte takový index pro větší tabulky:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Zrychluje tento druh dotazu o řády. Zvláštní pořadí platí pro pořadí řazení specifické pro národní prostředí. Další informace o třídách operátorů naleznete v příručce . Pokud používáte standardní “ C “ národní prostředí (většina lidí ne), prostý index (s výchozí třídou operátora) bude do.

Takový index je vhodný pouze pro vzory zakotvené vlevo (odpovídající od začátku řetězce).

SIMILAR TO nebo regulární výrazy se základními levými ukotvenými výrazy mohou používat i tento index. Ale ne s větvemi (B|D) nebo třídami znaků [BD] (alespoň v mých testech na PostgreSQL 9.0).

Trigramové shody nebo textové vyhledávání používají speciální indexy GIN nebo GiST.

Přehled operátorů shody vzorů

• LIKE ( ~~ ) je jednoduchý a rychlé, ale omezené ve svých schopnostech.
  ILIKE ( ~~* ) varianta nerozlišující velká a malá písmena.
  pg_trgm rozšiřuje podporu indexu pro oba.

• ~ (shoda regulárního výrazu) je silný, ale složitější a může být pomalý pro cokoli jiného než pro základní výrazy.

• SIMILAR TO je pouze zbytečné . Zvláštní polovina plemene LIKE a regulárních výrazů. Nikdy to nepoužívám. Viz níže.

• % je “ operátor podobnosti “ poskytovaný přídavným modulem pg_trgm. Viz níže.

• @@ je operátor textového vyhledávání. Viz níže.

pg_trgm – shoda trigramů

Počínaje PostgreSQL 9.1 můžete rozšíření pg_trgm usnadnit a poskytnout podporu indexu pro any LIKE / ILIKE vzor (a jednoduché regexp vzory s ~) pomocí Index GIN nebo GiST.

Podrobnosti, příklad a odkazy:

• Jak je LIKE implementován?

pg_trgm také poskytuje tyto operátory :

• % – “ podobnost “ operátor
• <% (komutátor: %>) – “ word_similarity “ operátor v Postgresu 9.6 nebo novějším
• <<% (komutátor: %>>) – “ strict_word_similarity “ operátor v Postgresu 11 nebo novějším

Hledání textu

Je speciální typ shody vzorů se samostatnou infrastrukturou a typy indexů. Používá slovníky a odvozování a je skvělým nástrojem k vyhledání slov v dokumentech, zejména v přirozených jazycích.

Předpona odpovídající je také podporováno:

• Získat částečnou shodu ze sloupce TSVECTOR indexovaného GIN

Stejně jako hledání frází od verze Postgres 9.6:

• Jak hledat slova se spojením ve fulltextovém vyhledávání PostgreSQL?

Zvažte úvod v příručce a přehled operátorů a funkcí .

Další nástroje pro shodu fuzzy řetězců

Další modul fuzzystrmatch nabízí několik dalších možností, ale výkon je obecně horší než všechny výše uvedené.

Zejména různé implementace funkce levenshtein() mohou být pomocné.

Proč jsou regulární výrazy (~) vždy rychlejší než SIMILAR TO?

Odpověď je jednoduchá. SIMILAR TO výrazy jsou interně přepsány do regulárních výrazů. Takže pro každý SIMILAR TO výraz existuje alespoň jeden rychlejší regulární výraz (což šetří režii přepisování výrazu). Při používání SIMILAR TO nikdy nedochází k žádnému zvýšení výkonu.

A jednoduché výrazy, které lze provést pomocí LIKE (~~), jsou rychlejší s LIKE každopádně.

SIMILAR TO je podporován pouze v PostgreSQL, protože skončil v raných verzích standardu SQL. Stále se toho nezbavili. Existují však plány na jeho odstranění a místo toho zahrnout shody regexp – alespoň jsem to slyšel.

EXPLAIN ANALYZE to odhaluje. Vyzkoušejte to sami s jakoukoli tabulkou!

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

Odhalení:

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

SIMILAR TO byl přepsán regulárním výrazem (~).

Konečný výkon pro tento konkrétní případ

Ale EXPLAIN ANALYZE odhaluje více. Zkuste to s výše uvedeným indexem:

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

Odhalení:

... -> 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ě s indexem, který nezná národní prostředí (text_pattern_ops nebo pomocí národního prostředí C) jednoduché výrazy ukotvené vlevo jsou přepsány těmito operátory textového vzoru: ~>=~, ~<=~, ~>~, ~<~. To je případ ~, ~~ nebo SIMILAR TO podobně.

Totéž platí pro indexy u varchar typů s varchar_pattern_ops nebo char s bpchar_pattern_ops.

Takže, aplikováno k původní otázce je to nejrychlejší možný způsob :

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

Pokud byste měli náhodou vyhledat sousední iniciály , můžete to dále zjednodušit:

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

Zisk oproti prostému používání ~ nebo ~~ je malý. Pokud výkon není vaším prvořadým požadavkem, měli byste se držet standardních operátorů – dosáhnout toho, co již v otázce máte.

Komentáře

• OP nemá ‚ index s názvem, ale víte, pokud ano, zahrnoval by jejich původní dotaz 2 hledání rozsahu a similar sken?
• @MartinSmith: Rychlý test s EXPLAIN ANALYZE ukazuje 2 indexové skenování bitmap.Několik bitmapových indexových skenů lze poměrně rychle kombinovat.
• Děkuji. Existovala by tedy nějaká milage s nahrazením OR UNION ALL nebo s nahrazením name LIKE 'B%' name >= 'B' AND name <'C' v Postgresu?
• @MartinSmith: UNION nevyhrál ‚ t ale ano, kombinace rozsahů do jedné klauzule WHERE zrychlí dotaz. K odpovědi jsem přidal další. Samozřejmě musíte vzít v úvahu své národní prostředí. Vyhledávání podle národního prostředí je vždy pomalejší.
• @a_horse_with_no_name: Myslím, že ne. Nové funkce pg_tgrm s indexy GIN jsou lahůdkou pro obecné textové vyhledávání. Vyhledávání ukotvené na začátku je již rychlejší.

Co tak přidat sloupec do stůl. Podle vašich skutečných požadavků:

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

PostgreSQL nepodporuje vypočítané sloupce v základních tabulkách a la SQL Server , ale nový sloupec lze udržovat pomocí spouštěče. Je zřejmé, že tento nový sloupec bude indexován.

Případně index ve výrazu vám dá stejné, levnější. Například:

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

Dotazy, které odpovídají výrazu v jejich podmínkách, mohou tento index využít.

Tímto způsobem se výkonnostní zásah provede při vytváření nebo změně dat, takže může být vhodný pouze pro prostředí s nízkou aktivitou (tj. mnohem méně zápisů než čtení).

Můžete vyzkoušet

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

Nemám ponětí, zda je výše uvedený nebo váš původní výraz v Postgresu sargovatelný.

Pokud vytvoříte navrhovaný index, zajímalo by mě také, jak to je srovnatelné s ostatními možnostmi.

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 

Komentáře

• Fungovalo to a dostal jsem cenu 1,19, kde jsem měl 1,25. Děkujeme!

To, co jsem v minulosti udělal při podobném problému s výkonem, je: zvýší ASCII znak posledního písmene a provede MEZI. Poté získáte nejlepší výkon pro podmnožinu LIKE funkcí. Funguje to samozřejmě pouze v určitých situacích, ale u velmi velkých datových sad, kde například hledáte název, je výkon propastný až přijatelný.

Velmi stará otázka, ale našel jsem jiné rychlé řešení tohoto problému:

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

Protože funkce ascii ( ) se dívá pouze na první znak řetězce.

Komentáře

• Používá se index (name)?

Pro kontrolu iniciál často používám casting na "char" (s uvozovkami). Není přenosný, ale velmi rychlý. Interně jednoduše detoastuje text a vrátí první znak a operace porovnávání znaků jsou velmi rychlé, protože typ má pevnou délku 1 bajt:

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

Pamatujte, že odesílání do "char" je rychlejší než ascii() od @ Sole021, ale není kompatibilní s UTF8 (ani s jiným kódováním pro na tom záleží), vrací jednoduše první bajt, takže by měl být použit pouze v případech, kdy je srovnání s prostými starými 7bitovými znaky ASCII.

Existují dvě metody, které dosud nebyly zmíněny pro řešení těchto případů:

1. částečný (nebo rozdělený – pokud je vytvořen pro celý rozsah ručně) index – nejužitečnější, když je vyžadována pouze podmnožina dat (například při některé údržbě nebo dočasné pro některé přehledy):

   CREATE INDEX ON spelers WHERE name LIKE "B%" 

2. rozdělení tabulky samotné (s použitím prvního znaku jako rozdělovacího klíče) – tato technika je zvláště sladina h vzhledem k PostgreSQL 10+ (méně bolestivé rozdělení na oddíly) a 11+ (prořezávání oddílů během provádění dotazu).

Navíc, pokud jsou data v tabulce tříděna, lze těžit z používání BRIN indexu (nad prvním znakem).

Pravděpodobně rychlejší srovnání jednoho znaku:

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

Komentáře

• Ne opravdu. column LIKE 'B%' bude efektivnější než použití funkce podřetězce ve sloupci.