Indizierung und ilike

Ich möchte eine Datenbank von MS SQLServer nach PostgreSQL migrieren und benötige mal Hilfe zur Suche in varchar Spalten.
Ich habe eine große Personaltabelle, die üblicherweise in etwa folgender Art abgefragt wird:

select * from personal where nachname like 'mei%' and vorname like 'han%'

In der Tabelle kann es Herrn Meier geben aber auch Herrn MEIER usw., beide sollten gefunden werden. Und ich kann auch nicht beeinflussen, ob der Benutzer nun nach Mei% oder mei% sucht.

In der Dokumentation fand ich, dass ich zunächst ilike anstelle von like benutzen sollte. Weiterhin fand ich, dass man bei einer anderen als der C-Locale für die Suche mit like einen Index mit varchar_pattern_ops erstellen muß und dass ilike intern lower verwendet.

Nun habe ich folgenden index erstellt:

CREATE INDEX ix_personal_nachname
  ON personal
  USING btree
  (lower(nachname) varchar_pattern_ops)
  TABLESPACE idx;

Ich habe auch andere Varianten probiert (upper(), lower(), ohne den speziellen Operator usw.) Aber ich sehe immer nur talescans, irgendwo ist da der Wurm drin.
Was ich vermeiden möchte, ist etwas in der Art upper(value) like upper(column) o.ä denn das Problem betrifft nicht nur die Suche in der Personaltabelle und ich müßte dann sehr viele Abragen umschreiben.

Wie ist in diesem Falle das beste Vorgehen? Wie erstellt man den Index passend zur Abfrage?

Pitti

sieht einklich okay aus.

Ich habe auch andere Varianten probiert (upper(), lower(), ohne den speziellen Operator usw.) Aber ich sehe immer nur talescans, irgendwo ist da der Wurm drin.
Was ich vermeiden möchte, ist etwas in der Art upper(value) like upper(column) o.ä denn das Problem betrifft nicht nur die Suche in der Personaltabelle und ich müßte dann sehr viele Abragen umschreiben.

Wie ist in diesem Falle das beste Vorgehen? Wie erstellt man den Index passend zur Abfrage?

Pitti


test=*# \d words
   Table "public.words"
 Column | Type | Modifiers
--------+------+-----------
 w      | text |
Indexes:
    "idx_words" btree (lower(w) varchar_pattern_ops)

test=*# explain analyse select * from words where lower(w) like 'foo*';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_words on words  (cost=0.00..6.01 rows=1 width=12) (actual time=0.066..0.066 rows=0 loops=1)
   Index Cond: (lower(w) ~=~ 'foo*'::character varying)
   Filter: (lower(w) ~~ 'foo*'::text)
 Total runtime: 0.202 ms
(4 rows)

Andreas

Ja danke, es wird mir nichts weiter übrig bleiben, als die Abfragen so umzuschreiben. Ich habe inzwischen in den FAQ gefunden, dass ilike keinen Index benutzt.
http://www.postgresql.org/docs/faqs.FAQ.html#item4.6

Case-insensitive searches such as ILIKE and ~* do not utilize indexes. Instead, use expression indexes, which are described in section 4.8.

Obwohl ich das nicht ganz verstehe.

Internally, ILIKE now calls lower() and then uses LIKE.

Also eigentlich sollte ilike den Index genau so benutzen können. Aber vielleicht gehört das ja in “requested features”.

Hallo Pittiplatsch,

in der Doku unter http://www.postgresql.org/docs/faqs.FAQ.html#item4.8 kann man folgendes lesen:

4.8) How do I perform regular expression searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?

The ~ operator does regular expression matching, and ~* does case-insensitive regular expression matching. The case-insensitive variant of LIKE is called ILIKE.

Case-insensitive equality comparisons are normally expressed as:

SELECT *
FROM tab
WHERE lower(col) = ‘abc’;

This will not use an standard index. > However, if you create an expression index, it will be used: >


CREATE INDEX tabindex ON tab (lower(col));

If the above index is created as UNIQUE, though the column can store upper and lowercase characters, it can not have identical values that differ only in case. To force a particular case to be stored in the column, use a CHECK constraint or a trigger.

Wie verhält sich die Abfrage wenn man den Ausdruck

WHERE lower(col) = ‘abc’

durch

WHERE lower(col) >= ‘abc’

ersetzt?





mfg

fristo

lower(col) like ‘abc%’ benutzt einen Index ix1 (lower(col) varchar_pattern_ops)
lower(col) = ‘abc’ benutzt diesen index nicht sondern benötigt statt dessen einen Index ix2(lower(col)) (also ohne den speziellen Operator)
lower(col)>‘abc’ oder >=‘abc’ benutzt gar keinen Index.

Was mich wundert ist, dass col ilike ‘abc’ nicht den Index ix1 benutzt, denn es ist ja intern genau das gleiche wie lower(col) like ‘abc’ (vgl. Zitat changelog s.o.)

Hallo Pittiplatsch,

Ich kann das nur als BUG deuten. Offensichtlich wird intern durch den Interpreter die Referenz zum INDEX vor dem Aufruf von “like” hergestellt. Es wäre gut, wenn Du dieses hier Report a Bug melden würdest.

Die einfachste Lösung dürfte im Augenblick sein, eine zusätzliche Spalte “lower_nachname” in die Tabelle einzufügen und den SELECT wegen der Unterstützung des INDEX mit lower(col) like ‘abc%’ auszuführen.




mfg

fristo