Patroonherkenning met LIKE, SOORTGELIJK AAN of reguliere expressies in PostgreSQL

Uw vraag is vrijwel optimaal. Syntaxis wordt niet veel korter, vraag wordt niet veel sneller:

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

Als y je wilt echt de syntaxis verkorten, een reguliere expressie gebruiken met branches :

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

Of een beetje sneller, met een character class :

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

Een snelle test zonder index levert snellere resultaten op dan voor SIMILAR TO in beide gevallen voor mij.
Met een geschikte B-Tree-index op zijn plaats, LIKE wint deze race in ordes van grootte.

Lees de basisprincipes van patroonovereenkomst in de handleiding .

Index voor superieure prestaties

Als u zich zorgen maakt met prestaties, maak een index zoals deze voor grotere tabellen:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Maakt dit soort zoekopdrachten sneller met ordes van grootte. Er gelden speciale overwegingen voor de locale-specifieke sorteervolgorde. Lees meer over operatorklassen in de handleiding . Als u de standaard ” C ” landinstelling gebruikt (de meeste mensen niet), zal een gewone index (met de standaard operatorklasse) do.

Zon index is alleen goed voor links verankerde patronen (matching vanaf het begin van de string).

SIMILAR TO of reguliere expressies met links verankerde basisexpressies kunnen deze index ook gebruiken. Maar niet met vertakkingen (B|D) of tekenklassen [BD] (tenminste in mijn tests op PostgreSQL 9.0).

Trigram-overeenkomsten of tekstzoekopdrachten gebruiken speciale GIN- of GiST-indexen.

Overzicht van operatoren voor patroonovereenkomst

• LIKE ( ~~ ) is eenvoudig en snel maar beperkt in zijn mogelijkheden.
  ILIKE ( ~~* ) de niet hoofdlettergevoelige variant.
  pg_trgm breidt indexondersteuning uit voor beide.

• ~ (overeenkomst met reguliere expressies) is krachtig maar complexer en kan traag zijn voor meer dan eenvoudige expressies.

• SIMILAR TO is gewoon zinloos . Een eigenaardige halfbreeding van LIKE en reguliere expressies. Ik gebruik het nooit. Zie hieronder.

• % is de ” overeenkomst ” operator, geleverd door de aanvullende module pg_trgm. Zie hieronder.

• @@ is de tekstzoekoperator. Zie hieronder.

pg_trgm – trigram-matching

Beginnend met PostgreSQL 9.1 u kunt de extensie pg_trgm faciliteren om indexondersteuning te bieden voor elke LIKE / ILIKE patroon (en eenvoudige regexp-patronen met ~) met een GIN- of GiST-index.

Details, voorbeeld en links:

• Hoe wordt LIKE geïmplementeerd?

pg_trgm biedt ook deze operatoren :

• % – de ” overeenkomst ” operator
• <% (commutator: %>) – het ” word_similarity ” -operator in Postgres 9.6 of hoger
• <<% (commutator: %>>) – de ” strict_word_similarity ” operator in Postgres 11 of later

Tekst zoeken

Is een speciaal type patroonovereenkomst met afzonderlijke infrastructuur- en indextypes. Het maakt gebruik van woordenboeken en stamnamen en is een geweldig hulpmiddel om woorden in documenten te vinden, vooral voor natuurlijke talen.

Voorvoegsels matchen wordt ook ondersteund:

• Haal gedeeltelijke overeenkomst op uit de door GIN geïndexeerde TSVECTOR-kolom

Evenals zoeken op woordgroep sinds Postgres 9.6:

• Hoe zoek je naar afgebroken woorden in PostgreSQL full text search?

Overweeg de introductie in de handleiding en het overzicht van operatoren en functies .

Extra tools voor het matchen van vage tekenreeksen

De aanvullende module fuzzystrmatch biedt wat meer opties, maar de prestaties zijn over het algemeen slechter dan al het bovenstaande.

Met name verschillende implementaties van de levenshtein() -functie kunnen een belangrijke rol spelen.

Waarom zijn reguliere expressies (~) altijd sneller dan SIMILAR TO?

Het antwoord is simpel. SIMILAR TO expressies worden intern herschreven in reguliere expressies. Dus voor elke SIMILAR TO expressie is er minstens één snellere reguliere expressie (dat bespaart de overhead van het herschrijven van de expressie). Er is geen prestatiewinst bij het gebruik van SIMILAR TO ooit .

En eenvoudige uitdrukkingen die kunnen worden gedaan met LIKE (~~) zijn sneller met LIKE hoe dan ook.

SIMILAR TO wordt alleen ondersteund in PostgreSQL omdat het in vroege versies van de SQL-standaard terechtkwam. Ze zijn er nog steeds niet van af. Maar er zijn plannen om het te verwijderen en in plaats daarvan regexp-overeenkomsten op te nemen – zo hoorde ik.

EXPLAIN ANALYZE onthult het. Probeer het gewoon zelf met een willekeurige tafel!

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

Onthult:

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

SIMILAR TO is herschreven met een reguliere expressie (~).

Ultieme prestaties voor dit specifieke geval

Maar EXPLAIN ANALYZE onthult meer. Probeer, met de bovengenoemde index op zijn plaats:

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

Onthult:

... -> 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, met een index die niet locale-bewust is (text_pattern_ops of met locale C) eenvoudige links verankerde expressies worden herschreven met deze tekstpatroonoperatoren: ~>=~, ~<=~, ~>~, ~<~. Dit is het geval voor ~, ~~ of SIMILAR TO gelijk.

Hetzelfde geldt voor indexen op varchar typen met varchar_pattern_ops of char met bpchar_pattern_ops.

Dus, toegepast op de oorspronkelijke vraag, dit is de snelst mogelijke manier :

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

Als u toevallig zoekt naar aangrenzende initialen , kunt u natuurlijk verder vereenvoudigen:

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

De winst ten opzichte van gewoon gebruik van ~ of ~~ is klein. Als prestatie niet uw belangrijkste vereiste is, moet u gewoon bij de standaardoperatoren blijven – om te komen tot wat u al in de vraag heeft.

Opmerkingen

• Het OP heeft geen ‘ geen index op naam, maar weet je toevallig dat als ze dat wel deden, hun oorspronkelijke zoekopdracht 2 bereikzoekopdrachten zou omvatten en similar een scan?
• @MartinSmith: een snelle test met EXPLAIN ANALYZE toont 2 bitmapindexscans.Meerdere bitmapindexscans kunnen vrij snel worden gecombineerd.
• Bedankt. Zou er dus sprake zijn van enige kilometerstand door OR te vervangen door UNION ALL of door name LIKE 'B%' te vervangen door name >= 'B' AND name <'C' in Postgres?
• @MartinSmith: UNION won ‘ t maar ja, het combineren van de bereiken in één WHERE -clausule zal de zoekopdracht versnellen. Ik heb meer aan mijn antwoord toegevoegd. Je moet natuurlijk rekening houden met je locale. Zoeken op locatiebewust is altijd langzamer.
• @a_horse_with_no_name: ik verwacht van niet. De nieuwe mogelijkheden van pg_tgrm met GIN-indexen zijn een feest voor het zoeken naar algemene tekst. Een zoekopdracht die aan het begin is verankerd, is al sneller dan dat.

Hoe zit het met het toevoegen van een kolom aan de tafel. Afhankelijk van uw daadwerkelijke vereisten:

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

PostgreSQL ondersteunt niet berekend kolommen in basistabellen a la SQL Server , maar de nieuwe kolom kan worden onderhouden via trigger. Het is duidelijk dat deze nieuwe kolom zou worden geïndexeerd.

Als alternatief kan een index op een uitdrukking zou u hetzelfde, goedkoper opleveren. Bijvoorbeeld:

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

Querys die overeenkomen met de uitdrukking in hun voorwaarden, kunnen deze index gebruiken.

Op deze manier wordt de prestatiehit genomen wanneer de gegevens worden gemaakt of gewijzigd, dus mogelijk alleen geschikt voor een omgeving met weinig activiteit (dwz veel minder schrijfbewerkingen dan leesbewerkingen).

U kunt proberen

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

Ik heb geen idee of het bovenstaande of je originele uitdrukking al dan niet sargable zijn in Postgres.

Als je de voorgestelde index maakt, zou ik ook graag willen horen hoe dit is vergelijkbaar met de andere opties.

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 

Reacties

• Het werkte en ik kreeg een prijs van 1,19 waar ik 1,25 had. Bedankt!

Wat ik in het verleden heb gedaan, geconfronteerd met een soortgelijk prestatieprobleem, is verhoog het ASCII-teken van de laatste letter, en doe een TUSSEN. U krijgt dan de beste prestaties voor een subset van de LIKE-functionaliteit. Het werkt natuurlijk alleen in bepaalde situaties, maar voor ultragrote datasets waarbij je bijvoorbeeld op een naam zoekt, worden de prestaties van erbarmelijk naar acceptabel.

Zeer oude vraag, maar ik vond nog een snelle oplossing voor dit probleem:

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

Sinds function ascii ( ) kijkt alleen naar het eerste teken van de string.

Reacties

• Gebruikt dit een index op (name)?

Voor het controleren van initialen gebruik ik vaak casten naar "char" (met dubbele aanhalingstekens) Het is niet draagbaar, maar erg snel. Intern verwijdert het eenvoudig de tekst en retourneert het eerste teken, en “char” -vergelijkingsbewerkingen zijn erg snel omdat het type een vaste lengte van 1 byte heeft:

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

Houd er rekening mee dat casten naar "char" sneller is dan de ascii() -slutie van @ Sole021, maar het is niet UTF8-compatibel (of enige andere codering voor dat er toe doet), en alleen de eerste byte retourneert, dus zou alleen moeten worden gebruikt in gevallen waarin de vergelijking plaatsvindt met gewone oude 7-bits ASCII-tekens.

Er zijn twee methoden die nog niet zijn genoemd om met dergelijke gevallen om te gaan:

1. gedeeltelijke (of gepartitioneerde – indien handmatig aangemaakt voor volledig bereik) index – zeer nuttig wanneer slechts een subset van gegevens is vereist (bijvoorbeeld tijdens onderhoud of tijdelijk voor sommige rapportages):

   CREATE INDEX ON spelers WHERE name LIKE "B%" 

2. de tabel zelf partitioneren (met het eerste teken als partitiesleutel) – deze techniek is vooral wort h overwegen in PostgreSQL 10+ (minder pijnlijke partitionering) en 11+ (partitie opschonen tijdens het uitvoeren van querys).

Bovendien, als de gegevens in een tabel zijn gesorteerd, kan men profiteren van het gebruik van BRIN-index (boven het eerste teken).

Waarschijnlijk sneller om één teken te vergelijken:

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

Reacties

• Niet werkelijk. column LIKE 'B%' zal efficiënter zijn dan het gebruik van de substring-functie op de kolom.