Kuvion sovittaminen LIKE-, VASTAAVA- tai säännöllisten lausekkeiden kanssa PostgreSQL: ssä

Kyselysi on melkein optimaalinen. Syntaksi ei tule paljon lyhyempi, kysely ei tule paljon nopeampi:

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

Jos y haluat todella lyhentää syntaksia , käyttää säännöllistä lauseketta haarojen kanssa:

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

Tai hieman nopeammin merkkiluokalla :

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

Nopea testi ilman hakemistoa tuottaa nopeammat tulokset kuin SIMILAR TO kummassakin tapauksessa minulle.
Kun sopiva B-Tree-hakemisto on paikallaan, LIKE voittaa tämän kilpailun suuruusluokittain.

Lue -kuvion vastaavuuden perusteet käsikirjasta .

Parhaan suorituskyvyn hakemisto

Jos olet huolissasi Suorituskyvyn avulla luo tällainen hakemisto isommille taulukoille:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Tekee tällaisen kyselyn nopeammaksi suuruusluokittain. Erityiset näkökohdat koskevat paikalliskohtaista lajittelujärjestystä. Lue lisää -operaattoriluokista käsikirjasta . Jos käytät tavanomaista ” C ” -kenttää (useimmat ihmiset eivät ”), tavallinen hakemisto (oletusoperaattoriluokan kanssa) tee.

Tällainen hakemisto on hyvä vain vasemmalle ankkuroiduille kuvioille (jotka vastaavat merkkijonon alusta).

SIMILAR TO tai säännölliset lausekkeet, joissa on vasemmalle ankkuroidut peruslausekkeet, voivat käyttää myös tätä hakemistoa. Mutta ei haaroilla (B|D) tai merkkiluokilla [BD] (ainakin testeissäni PostgreSQL 9.0 -käyttöjärjestelmässä).

Trigramivastaavuudet tai tekstihaku käyttävät erityisiä GIN- tai GiST-hakemistoja.

Katsaus mallien vastaavuusoperaattoreihin

• LIKE ( ~~ ) on yksinkertainen ja nopea, mutta sen kapasiteetti on rajallinen.
  ILIKE ( ~~* ) pienet ja pienet kirjaimet -vaihtoehto.
  pg_trgm laajentaa molempien hakemistotukea.

• iv ~ (säännöllisen lausekkeen ottelu) on tehokas, mutta monimutkaisempi ja voi olla hidasta muille kuin peruslausekkeille.

• SIMILAR TO on vain turhaa . Erityinen LIKE ja säännöllisten lausekkeiden puolirotu. En koskaan käytä sitä. Katso alla.

• % on ” samankaltaisuus ” -operaattori, jonka tarjoaa ylimääräinen moduuli pg_trgm. Katso alla.

• @@ on tekstihakuoperaattori. Katso alla.

pg_trgm – trigramien haku

Alkaen PostgreSQL 9.1 voit helpottaa laajennusta pg_trgm hakemistotuen tarjoamiseksi mille tahansa LIKE / ILIKE kuvio (ja yksinkertaiset regexp-mallit, joissa ~) käyttämällä GIN- tai GiST-hakemisto.

Tiedot, esimerkki ja linkit:

• Miten LIKE toteutetaan?

pg_trgm tarjoaa myös nämä operaattorit :

• % – ” samankaltaisuus ” -operaattori
• <% (kommutaattori: %>) – ” sana_samankaltaisuus ” -operaattori Postgres 9.6: ssa tai uudemmassa
• <<% (kommutaattori: %>>) – ” strict_word_similarity ” operaattori Postgres 11: ssä tai uudemmassa.

Tekstihaku

Onko erityinen tyyppimalli, joka vastaa erillisiä infrastruktuuri- ja hakemistotyyppejä. Se käyttää sanakirjoja ja juurtavia sanoja ja on erinomainen työkalu sanojen etsimiseen asiakirjoista, etenkin luonnollisille kielille.

iv Etuliite vastaa tuetaan myös:

• Hae osittainen haku GIN-indeksoidusta TSVECTOR-sarakkeesta

Sekä lausehaku Postgres 9.6: sta lähtien:

• Kuinka etsiä tavutettuja sanoja PostgreSQL-kokotekstihausta?

Harkitse käsikirjan esittely ja yleiskatsaus operaattoreista ja toiminnoista .

Lisätyökalut sumeaan merkkijonon täsmäykseen

Lisämoduuli fuzzystrmatch tarjoaa lisää vaihtoehtoja, mutta suorituskyky on yleensä kaikkia edellä mainittuja heikompi.

Erityisesti erilaisia levenshtein() -funktion toteutukset voivat olla instrumentaalisia.

Miksi säännölliset lausekkeet (~) ovat aina nopeampia kuin SIMILAR TO?

Vastaus on yksinkertainen. SIMILAR TO lausekkeet kirjoitetaan sisäisesti säännöllisinä lausekkeina. Joten jokaiselle SIMILAR TO lausekkeelle on vähintään yksi nopeampi säännöllinen lauseke (joka säästää lausekkeen uudelleenkirjoittamisen yleiskustannukset). SIMILAR TO ever : n käytöstä ei ole hyötyä.

Ja yksinkertaiset lausekkeet, jotka voidaan tehdä LIKE (~~) -toiminnolla, ovat nopeampi LIKE joka tapauksessa.

SIMILAR TO tuetaan vain PostgreSQL: ssä, koska se päätyi SQL-standardin varhaisiin luonnoksiin. He eivät ole vieläkään päässeet eroon siitä. Mutta suunnitelmissa on poistaa se ja sisällyttää sen sijaan regexp-ottelut – tai niin kuulin.

EXPLAIN ANALYZE paljastaa sen. Kokeile vain minkä tahansa pöydän kanssa!

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

Paljastaa:

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

SIMILAR TO on kirjoitettu uudestaan säännöllisellä lausekkeella (~).

Lopullinen suorituskyky tässä tapauksessa

Mutta EXPLAIN ANALYZE paljastaa enemmän. Yritä, kun edellä mainittu hakemisto on paikallaan:

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

Paljastaa:

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

Sisäisesti, hakemistolla, joka ei ole lokaleista tietoinen (text_pattern_ops tai käyttää aluetta C) yksinkertaiset vasemmalle ankkuroidut lausekkeet kirjoitetaan uudelleen näillä tekstikuviooperaattoreilla: ~>=~, ~<=~, ~>~, ~<~. Tämä pätee ~, ~~ tai SIMILAR TO samankaltaiset.

Sama koskee varchar -tyyppisiä hakemistoja varchar_pattern_ops tai char kanssa bpchar_pattern_ops.

Joten, käytössä alkuperäiselle kysymykselle tämä on nopein mahdollinen tapa :

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

Jos sinun pitäisi satunnaisesti etsiä vierekkäisiä nimikirjaimia , voit yksinkertaistaa edelleen:

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

~ tai ~~ tavallisen käytön hyöty on pieni. Jos suorituskyky ei ole tärkein vaatimus, sinun tulisi vain pitää kiinni tavallisista operaattoreista – päästäksesi siihen, mitä sinulla on jo kysymyksessä.

Kommentit

• OP: lla ei ’ ei ole hakemistoa nimessä, mutta tiedätkö jos tiedät, sisältyisikö heidän alkuperäiseen kyselyynsä kaksi aluehakua ja similar skannaus?
• @MartinSmith: Pikatesti EXPLAIN ANALYZE -toiminnolla näyttää kaksi bittikarttahakemistoa.Useat bittikarttahakemistot voidaan yhdistää melko nopeasti.
• Kiitos. Joten olisiko mitään kilometriä korvaamalla OR UNION ALL tai korvaamalla name LIKE 'B%' name >= 'B' AND name <'C' Postgresissä?
• @MartinSmith: UNION voitti ’ t mutta kyllä, alueiden yhdistäminen yhdeksi WHERE -lausekkeeksi nopeuttaa kyselyä. Olen lisännyt vastaukseeni lisää. Tietysti sinun on otettava huomioon alueesi. Maantieteellinen haku on aina hitaampaa.
• @a_horse_with_no_name: En usko. Pg_tgrm: n uudet ominaisuudet GIN-hakemistoilla ovat herkkyys yleiselle tekstihakulle. Alussa ankkuroitu haku on jo nopeampi.

Entä sarakkeen lisääminen pöytä. Todellisista vaatimuksistasi riippuen:

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

PostgreSQL ei tue laskettuja sarakkeita perustaulukoissa a la SQL Palvelin , mutta uutta saraketta voidaan ylläpitää liipaisimen kautta. Tämä uusi sarake luonnollisesti indeksoidaan.

Vaihtoehtoisesti lausekkeen -hakemisto antaisi sinulle saman, halvemman. Esimerkiksi:

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

Hakemukset, jotka vastaavat lauseketta olosuhteissaan, voivat käyttää tätä hakemistoa.

Tällä tavoin suorituskykyosuma otetaan, kun tiedot luodaan tai niitä muutetaan, joten ne saattavat olla sopivia vain matalan aktiivisuuden ympäristössä (ts. paljon vähemmän kirjoituksia kuin luettuja).

Voit yrittää

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

En ole aavistanut, onko joko yllä oleva tai alkuperäinen lausekkeesi laajennettavissa Postgresissä.

Jos luot ehdotetun hakemiston, olisi myös kiinnostavaa kuulla, miten tätä verrataan muihin vaihtoehtoihin.

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 

Kommentit

• Se toimi ja sain kustannukset 1,19 missä minulla oli 1,25. Kiitos!

Mitä olen aiemmin tehnyt samanlaisen suorituskykyongelman edessä, on lisää viimeisen kirjaimen ASCII-merkkiä ja tee VÄLIN. Sitten saat parhaan suorituskyvyn LIKE-toiminnon osajoukolle. Se toimii tietysti vain tietyissä tilanteissa, mutta erittäin suurille tietojoukoille, joissa etsit esimerkiksi nimeä, se tekee suorituskyvystä tylsän hyväksyttäväksi.

Hyvin vanha kysymys, mutta löysin toisen nopean ratkaisun tähän ongelmaan:

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

Koska funktio ascii ( ) näyttää vain merkkijonon ensimmäisen merkin.

Kommentit

• Käyttääkö tämä hakemistoa sarakkeessa (name)?

Nimikirjaimien tarkistuksessa käytän usein lähetystä kohtaan "char" (kaksoislainausmerkeillä). Se ei ole kannettava, mutta erittäin nopea. Sisäisesti se yksinkertaisesti tyhjentää tekstin ja palauttaa ensimmäisen merkin, ja ”char” -vertailutoiminnot ovat erittäin nopeita, koska tyyppi on 1-tavuinen kiinteä pituus:

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

Huomaa, että suoratoisto kohtaan "char" on nopeampaa kuin @ divole =21: n laimennus ascii(), mutta se ei ole UTF8-yhteensopiva (tai mikä tahansa muu koodaus Palauttaa yksinkertaisesti ensimmäisen tavun, joten sitä tulisi käyttää vain tapauksissa, joissa vertailu on tehty vanhojen 7-bittisten ASCII-merkkien kanssa.

On olemassa kaksi tapaa, joita ei vielä ole mainittu tällaisten tapausten käsittelemiseksi:

1. osittainen (tai osioitu – jos luotu koko alueelle manuaalisesti) hakemisto – hyödyllisin, kun vain osajoukko vaaditaan (esimerkiksi ylläpidon aikana tai tilapäisesti joillekin raporteille):

   CREATE INDEX ON spelers WHERE name LIKE "B%" 

2. itse taulukon osiointi (ensimmäisen merkin käyttö osiointiavain) – tämä tekniikka on erityisen vierre h huomioon ottaen PostgreSQL 10+ (vähemmän tuskallinen osiointi) ja 11+ (osion karsiminen kyselyn suorituksen aikana).

Lisäksi, jos taulukon tiedot lajitellaan, voidaan hyötyä BRIN-indeksin (ensimmäisen merkin yli) käytöstä.