Indexarea campurilor de tip varchar in PostgreSQL

indexStringPG

Am făcut niscai teste și toată căcănăria provine din biblioteca folosită de PostgreSQL pentru comparația de string-uri pentru crearea arborilor când se face indexarea.
Baza când este inițializată se inițializează ca proasta cu opțiunea default de "collate en_US.UTF-8" în loc de anticul "collate C" și folosește o altă bibliotecă, specială, pentru comparațiile "lexicografice" de caractere!
Să vedem niște teste și să luăăăm amiiiiinteeeeee ....

// "Încălzim cache-ul" să ducem tabela în memorie să nu conteze citirea din disc

root@bnd:/home/teo# time psql cs_muster -c "select count(*) from crotalie"
  count   
----------
 91.505.920
real	11.56s

root@bnd:/home/teo# time psql cs_muster -c "select count(*) from crotalie"
  count   
----------
 91.505.920
real	11.23s

// Indexăm acum pe un câmp de tip string cu "collate-ul" default cum l-a instalat PostgreSQL-ul

root@bnd:/home/teo# time psql cs_muster -c "create unique index crotalie_codu_din_nou on crotalie using btree(cod)"
CREATE INDEX (pe câmp varchar)
real	22m 51s

// Indexăm acum pe un câmp de tip integer (n-are nevoie de collate)

root@bnd:/home/teo# time psql cs_muster -c "create unique index crotalie_idul_din_nou on crotalie using btree(id)"
CREATE INDEX (pe câmp integer)
real	1m 52s

După cum se poate vedea, indexarea pe un câmp "string" lăsat cu collate-ul de la mă'sa durează de vreo 10 ori mai mult decât indexarea pe un câmp de tip numeric.
Pentru a verifica exact ce și cum durează, am făcut o altă tabelă cu date identice din crotalie la care am mai adăugat un câmp cod_collate_c varchar(16) cu specificația clară pentru el collate "C"
Pe urmă am făcut iar testare pe indexare câmp id (integer), apoi câmp cod (collate default cum a fost făcută baza) și apoi câmp cod_collate_c.
Timpii îi vedeți mai jos:

root@bnd:/home/teo# time psql cs_muster -c "create unique index ccrotalie_idul_din_nou on ccrotalie using btree(id)"
CREATE INDEX (pe câmp integer)
real	1m 57s

root@bnd:/home/teo# time psql cs_muster -c "create unique index ccrotalie_cod_collate_c on ccrotalie using btree(cod_collate_c)" 
CREATE INDEX (pe câmp varchar definit cu collate "C")
real	5m 32s

root@bnd:/home/teo# time psql cs_muster -c "create unique index ccrotalie_cod_cu_ccollate_default on ccrotalie using btree(cod)"
CREATE INDEX  (pe câmp varchar cu collate lăsat de la mă'sa, cum a fost definită baza)
real	21m 10s

Problema se rezolvă prin crearea bazei de date de la început cu opțiunea --lc-collate=C , singura care folosește pentru comparări de string-uri biblioteca aia scrisă pe vremea lu'  Brian W. Kernighan și Dennis M. Ritchie, unde se comparau civilizat codurile ASCII. 
Pentru comparare de sirute, coduri de crotalii și exploatație și sortări de localități și județe, NU AM NEVOIE DE COLLATION ORDER TIP en_US.UTF-8 sau românești, să vină localitatea Pârâul Rece după Padina! Pe chestia asta, că se folosește inutil o comparație "custom", s-a dus dracului timpul de indexare de 4 ori mai mare. 
La indecșii mari, de la 3 ore la 12 ore e o diferență sesizabilă, nu? :-(

Iaca timpii pe o bază făcută de la zero cu opțiunea de "collate C" , 5 minute crearea indexului pe cod (string) față de 21 minute (vezi mai sus)

$ createdb -E SQL_ASCII -T template0 --lc-collate=C cs_muie

// Încălzim cache-ul bazei
teo@ bnd  ~ $ time psql cs_muie -c "select count(*) from crotalie"
  count   
----------
 91.590.935
real	8.21s

teo@ bnd  ~ $ time psql cs_muie -c "select count(*) from crotalie"
  count   
----------
 91.590.935
real	8.34s

teo@ bnd  ~ $ time psql cs_muie -c "create unique index crotalie_id on crotalie using btree(id)"
CREATE INDEX
real	2m 16s

teo@ bnd  ~ $ time psql cs_muie -c "create unique index crotalie_cod on crotalie using btree(cod)"
CREATE INDEX
real	5m 36s