Indexul coloanei cu tipul de date citext nu este utilizat (Administrarea bazelor de date, Postgresql, Index, Potrivire De Tipare, Sensibil La Majuscule Și Minuscule)

José Luis a intrebat.

În PostgreSQL 9.4, cu următoarea schemă:

CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    name TEXT,
    junk CHAR(1000)
);

INSERT INTO people(id, name)
 SELECT generate_series(1,100000), md5(random()::text);

CREATE INDEX ON people (name text_pattern_ops);

dacă fac o căutare după nume, indexul este utilizat:

test=# explain analyze select id, name from people where name like 'a%';
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on people  (cost=248.59..1160.92 rows=6061 width=37) (actual time=2.412..8.340 rows=6271 loops=1)
   Filter: (name ~~ 'a%'::text)
   Heap Blocks: exact=834
   ->  Bitmap Index Scan on people_name_idx  (cost=0.00..247.08 rows=6266 width=0) (actual time=2.123..2.123 rows=6271 loops=1)
         Index Cond: ((name ~>=~ 'a'::text) AND (name ~<~ 'b'::text))
 Planning time: 0.600 ms
 Execution time: 8.991 ms

Dar dacă înlocuiesc TEXT cu CITEXT:

CREATE EXTENSION CIText;

CREATE TABLE people (
    id INTEGER PRIMARY KEY,
    name CITEXT,
    junk CHAR(1000)
);

Indexul nu mai este utilizat:

test=# explain analyze select id, name from people where name like 'a%';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Seq Scan on people  (cost=0.00..2084.00 rows=500 width=36) (actual time=5.700..152.572 rows=6305 loops=1)
   Filter: (name ~~ 'a%'::citext)
   Rows Removed by Filter: 93695
 Planning time: 0.764 ms
 Execution time: 153.046 ms

În conformitate cu CITEXT documentația PostgreSQL, comportamentul ar trebui să fie ca și cu TEXT:

În caz contrar, se comportă aproape exact ca textul.

Cum îi pot spune lui PostgreSQL să utilizeze indexul?

1 răspunsuri
Erwin Brandstetter

Utilizarea indexului cu text_pattern_ops (precum și cu clasa de operatori implicită atunci când se utilizează clasa C locale) depinde de reprezentarea binară a datelor de caractere. citext stochează valorile originale cu majusculele păstrate, astfel încât nu există trebuie să să existe o problemă în acest sens …

După cum ați comentat, motivul real este îngropat în suportul de collationare.

Oricum ar fi, citext sau textputeți face să funcționeze cu un index de expresie:

CREATE INDEX people_name_idx ON people (lower(name) text_pattern_ops);

Și o interogare corespunzătoare:

SELECT id, name FROM people WHERE lower(name) LIKE 'abc%';

Rețineți că lower(name) returnează tipul de date text, chiar și atunci când se alimentează citext.

Alternativ, ați putea utiliza un index de trigramă, care este mai costisitor de întreținut, dar care oferă, de asemenea, mai multe capacități:

  • Cum se implementează LIKE?
  • Potrivirea tiparelor cu LIKE, SIMILAR TO sau expresii regulate în PostgreSQL

În plus: cazul dvs. de testare este suboptimal deoarece valorile fictive sunt toate minuscule pentru început și modelul 'a%' nu este adesea suficient de selectiv pentru a utiliza un index deloc. Și char(1000) nu are sens (chiar dacă este irelevant pentru test).

Comentarii

  • Se pare că optimizarea indexului LIKE nu funcționează cu CITEXT: postgresql.org/message-id/[email protected] Există o intrare în TODO de la PostgreSQL: wiki.postgresql.org/wiki/Todo (trebuie doar să căutați linkul anterior) În cazul meu, căutați modelele ancorate în stânga insensibile la majuscule și minuscule, utilizarea lui CITEXT nu pare să ofere niciun beneficiu. Am revenit la tipul de coloană la TEXT și folosesc un index funcțional. În ceea ce privește indexul triagam, deoarece unele dintre modelele mele de căutare au doar unul sau două caractere („a%” sau „ab%”), probabil că utilizarea unui index B-tree este mai performantă. –  > Por José Luis.
  • @JoséLuis: Pentru căutări cu ancorare numai în stânga, un index btree. text_pattern_ops index se comportă cel mai bine în orice caz. Un index trigram ar fi relevant pentru a face mai mult decât atât (căutări full-text, similaritate, …). Acesta face funcționează pentru 'a%' sau 'ab%', de asemenea. Trucul simplu din implementare este de a adăuga două caractere de spațiu înainte de a elimina trigramele (dar acest lucru este transparent pentru utilizator). –  > Por Erwin Brandstetter.
  • @JoséLuis Am trecut printr-un caz similar (același cu cel pe care l-ai postat). CITEXT nu ia indici nici măcar în 10.4. –  > Por Dinesh Kumar.