Pattern matching con LIKE, SIMILAR TO o espressioni regolari in PostgreSQL

La tua query è praticamente ottimale. La sintassi “non diventerà molto più breve, la query non diventerà molto più veloce:

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

Se y o vuoi davvero abbreviare la sintassi , usa unespressione regolare con rami :

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

O leggermente più veloce, con una classe di caratteri :

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

Un test rapido senza indice produce risultati più rapidi rispetto a SIMILAR TO in entrambi i casi per me.
Con un indice B-Tree appropriato, LIKE vince questa gara per ordini di grandezza.

Leggi le nozioni di base sulla corrispondenza di pattern nel manuale .

Indice per prestazioni superiori

Se sei preoccupato con le prestazioni, crea un indice come questo per tabelle più grandi:

CREATE INDEX spelers_name_special_idx ON spelers (name text_pattern_ops); 

Rende questo tipo di query più veloce per ordini di grandezza. Considerazioni speciali si applicano allordinamento specifico delle impostazioni internazionali. Ulteriori informazioni sulle classi di operatori nel manuale . Se stai utilizzando la lingua standard ” C ” (la maggior parte delle persone non “t), un indice semplice (con la classe operatore predefinita) fare.

Un indice di questo tipo è utile solo per i modelli ancorati a sinistra (corrispondenti dallinizio della stringa).

SIMILAR TO o anche le espressioni regolari con espressioni di base ancorate a sinistra possono utilizzare questo indice. Ma non con rami (B|D) o classi di caratteri [BD] (almeno nei miei test su PostgreSQL 9.0).

Le corrispondenze trigramma o la ricerca di testo utilizzano indici GIN o GiST speciali.

Panoramica degli operatori di corrispondenza dei modelli

• LIKE ( ~~ ) è semplice e veloce ma limitato nelle sue capacità.
  ILIKE ( ~~* ) la variante senza distinzione tra maiuscole e minuscole.
  pg_trgm estende il supporto dellindice per entrambi.

• ~ (corrispondenza di espressioni regolari) è potente ma più complesso e può essere lento per qualsiasi cosa oltre alle espressioni di base.

• SIMILAR TO è solo inutile . Un particolare meticcio di LIKE ed espressioni regolari. Non lo uso mai. Vedi sotto.

• % è loperatore ” similarity “, fornito dal modulo aggiuntivo pg_trgm. Vedi sotto.

• @@ è loperatore di ricerca di testo. Vedi sotto.

pg_trgm – corrispondenza trigramma

A partire da PostgreSQL 9.1 puoi facilitare lestensione pg_trgm per fornire supporto indice per qualsiasi LIKE / ILIKE pattern (e semplici pattern regexp con ~) utilizzando un Indice GIN o GiST.

Dettagli, esempio e link:

• Come viene implementato LIKE?

pg_trgm fornisce anche questi operatori :

• % – il ” somiglianza ” operatore
• <% (commutatore: %>) – la ” word_similarity ” in Postgres 9.6 o successivo
• <<% (commutatore: %>>) – ” strict_word_similarity ” operatore in Postgres 11 o successivo

Ricerca di testo

È un tipo speciale di corrispondenza del modello con infrastruttura e tipi di indice separati. Utilizza dizionari e stemming ed è un ottimo strumento per trovare parole nei documenti, soprattutto per le lingue naturali.

Corrispondenza prefisso è anche supportato:

• Ottieni corrispondenza parziale dalla colonna TSVECTOR indicizzata GIN

Oltre alla ricerca per frase da Postgres 9.6:

• Come cercare parole con trattino nella ricerca full text di PostgreSQL?

Considera il introduzione nel manuale e panoramica di operatori e funzioni .

Strumenti aggiuntivi per la corrispondenza di stringhe fuzzy

Il modulo aggiuntivo fuzzystrmatch offre alcune opzioni in più, ma le prestazioni sono generalmente inferiori a tutte le precedenti.

In particolare, vari le implementazioni della funzione levenshtein() possono essere strumentali.

Perché le espressioni regolari (~) sono sempre più veloci di SIMILAR TO?

La risposta è semplice. Le espressioni SIMILAR TO vengono riscritte internamente in espressioni regolari. Quindi, per ogni SIMILAR TO espressione, cè almeno unespressione regolare più veloce (che risparmia il sovraccarico di riscrittura dellespressione). Non vi è alcun miglioramento delle prestazioni utilizzando SIMILAR TO mai .

E le espressioni semplici che possono essere eseguite con LIKE (~~) sono più veloci con LIKE comunque.

SIMILAR TO è supportato solo in PostgreSQL perché è finito nelle prime bozze dello standard SQL. Non lhanno ancora eliminato. Ma ci sono piani per rimuoverlo e includere invece corrispondenze regexp – o almeno così ho sentito.

EXPLAIN ANALYZE lo rivela. Prova tu stesso con qualsiasi tabella!

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

Rivela:

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

SIMILAR TO è stato riscritto con unespressione regolare (~).

Prestazioni eccezionali per questo caso particolare

Ma EXPLAIN ANALYZE rivela di più. Prova, con lindice di cui sopra:

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

Rivela:

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

Internamente, con un indice che non riconosce le impostazioni locali (text_pattern_ops o utilizza le impostazioni locali C) semplici espressioni ancorate a sinistra vengono riscritte con questi operatori di pattern di testo: ~>=~, ~<=~, ~>~, ~<~. Questo è il caso di ~, ~~ o SIMILAR TO allo stesso modo.

Lo stesso vale per gli indici sui tipi varchar con varchar_pattern_ops o char con bpchar_pattern_ops.

Quindi, applicato alla domanda originale, questo è il modo più veloce possibile :

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

Ovviamente, se ti capita di cercare iniziali adiacenti , puoi semplificare ulteriormente:

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

Il vantaggio rispetto al semplice utilizzo di ~ o ~~ è minimo. Se le prestazioni non sono il tuo requisito fondamentale, dovresti semplicemente attenersi agli operatori standard, arrivando a ciò che hai già nella domanda.

Commenti

• LOP non ‘ ha un indice sul nome, ma sai se lo facessero, la loro query originale coinvolgerebbe 2 ricerche di intervallo e similar una scansione?
• @MartinSmith: un rapido test con EXPLAIN ANALYZE mostra 2 scansioni dellindice bitmap.È possibile combinare più scansioni di indici bitmap piuttosto rapidamente.
• Grazie. Quindi sarebbe possibile sostituire OR con UNION ALL o sostituire name LIKE 'B%' con name >= 'B' AND name <'C' in Postgres?
• @MartinSmith: UNION ha vinto ‘ t ma sì, combinare gli intervalli in una clausola WHERE velocizzerà la query. Ho aggiunto di più alla mia risposta. Ovviamente devi tenere in considerazione la tua localizzazione. La ricerca basata sulle impostazioni locali è sempre più lenta.
• @a_horse_with_no_name: Mi aspetto di no. Le nuove funzionalità di pg_tgrm con indici GIN sono un piacere per la ricerca di testo generica. Una ricerca ancorata allinizio è già più veloce di così.

Che ne dici di aggiungere una colonna al tavolo. A seconda delle tue effettive esigenze:

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

PostgreSQL “t non supporta colonne calcolate nelle tabelle di base a la SQL Server ma la nuova colonna può essere mantenuta tramite trigger. Ovviamente, questa nuova colonna verrebbe indicizzata.

In alternativa, un indice su unespressione ti darebbe lo stesso, più economico. Ad esempio:

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

Le query che corrispondono allespressione nelle loro condizioni possono utilizzare questo indice.

In questo modo, il calo delle prestazioni viene preso quando i dati vengono creati o modificati, quindi potrebbe essere appropriato solo per un ambiente a bassa attività (cioè molto meno scritture che letture).

Puoi provare

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

Non ho idea se la precedente o la tua espressione originale siano sargable in Postgres.

Se crei lindice suggerito, sarei anche interessato a sapere come questo è paragonabile alle altre opzioni.

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 

Commenti

• Ha funzionato e ho ottenuto un costo di 1.19 dove avevo 1.25. Grazie!

Quello che ho fatto in passato, di fronte a un problema di prestazioni simile, è incrementa il carattere ASCII dellultima lettera e fai un TRA. Si ottengono quindi le migliori prestazioni, per un sottoinsieme della funzionalità LIKE. Ovviamente funziona solo in determinate situazioni, ma per set di dati di grandi dimensioni in cui, ad esempio, stai cercando un nome, rende le prestazioni da abissali ad accettabili.

Domanda molto vecchia, ma ho trovato unaltra rapida soluzione a questo problema:

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

Poiché la funzione ascii ( ) esamina solo il primo carattere della stringa.

Commenti

• Utilizza un indice su (name)?

Per il controllo delle iniziali, utilizzo spesso il casting per "char" (con le virgolette doppie). Non è portatile, ma molto veloce. Internamente, detoast semplicemente il testo e restituisce il primo carattere, e le operazioni di confronto “char” sono molto veloci perché il tipo ha una lunghezza fissa di 1 byte:

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

Tieni presente che la trasmissione a "char" è più veloce della soluzione ascii() di @ Sole021, ma non è compatibile con UTF8 (o qualsiasi altra codifica per che importa), restituendo semplicemente il primo byte, quindi dovrebbe essere usato solo nei casi in cui il confronto è contro semplici vecchi caratteri ASCII a 7 bit.