SELECT-Statements, welche LIKE in der WHERE-Clause nutzen, können einen Index benutzen, wenn dieser mit der Option: var_char_pattern_ops erstellt wurde. Dazu ein Beispiel:
CREATE DATABASE foo;
CREATE TABLE test (x int, name text);
INSERT INTO test VALUES ( generate_series(0,9999), md5(random()::text) );
CREATE INDEX ON test (name);
Der im Beispiel genutzte Code, erstellt eine Datenbank, und eine Tabelle, die 10.000 Test-Einträge erhält. Die Spalte ‘name’ wird indexiert. Man erhält Werte wie die Folgenden:
foo=# SELECT * FROM test LIMIT 5;
x | name
---+----------------------------------
0 | 23c815e10a56cfdc96aad36a832a4bbe
1 | 317deb027e211e9810621c04ef03d41a
2 | e6988c0fff72a487d02a4151bc588770
3 | 0e554d8efe0aee371fcd60c64b0c4a4a
4 | 86abee463f2687b466c0d24861e6a481
Nun wird die Spalte ‘name’ nach allen Werten beginnend mit ’23’ abgefragt.
foo=# EXPLAIN SELECT name FROM test where name LIKE '23%';
QUERY PLAN
-------------------------------------------------------
Seq Scan on test (cost=0.00..209.00 rows=1 width=33)
Filter: (name ~~ '23%'::text)
Wir richten unser Augenmerk auf die Ausgabe des Query-Plans und stellen dabei fest, dass hier ein sequentieller Scan auf die komplette Tabelle erfolgt. Bei gerade einmal 10.000 Einträgen mag dass noch relativ gut performen, wächst die Tabelle allerdings weiter an, könnte dies zu einem echten Performance-Problem werden. Und genau hier, kommt die Index-Option ‘varchar_pattern_ops’ ins Spiel.
DROP INDEX test_name_idx;
CREATE INDEX ON test (name varchar_pattern_ops);
ANALYZE test;
Der vorherige Index wurde entfernt und mit der eingangs genannten Option neu erstellt.
foo=# EXPLAIN SELECT name FROM test where name LIKE '23%';
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=4.63..72.61 rows=1 width=33)
Filter: (name ~~ '23%'::text)
-> Bitmap Index Scan on test_name_idx (cost=0.00..4.63 rows=38 width=0)
Index Cond: ((name ~>=~ '23'::text) AND (name ~<~ '24'::text))
PostgreSQL nutzt nun den neu erstellten Index auch für die Like-Abfragen. Dass sich das als deutlich effektiver erweist, zeigt ein Blick auf die geschätzten Kosten, diese entsprechen nun nur noch in etwa einem Drittel unserer Abfrage als zuvor. Je weiter man übrigens den Platzhalter ‘%’ nach rechts verschiebt, desto effektiver und damit auch schneller wird die Abfrage.
Weitere Informationen sind in der PostgreSQL-Dokumentation enthalten:
http://www.postgresql.org/docs/9.3/static/indexes-opclass.html
