Vastus (19.08.2023 16:19): Võtmeveergudele loob PostgreSQL ja ka teised andmebaasisüsteemid automaatselt B-puu (B-tree) indeksi (Oracle, MySQL, MS SQL Server).

Lugege siit väga head ülevaadet hash indeksitest PostgreSQLis. Mõned järeldused, mida see artikkel tegi eksperimentide põhjal.

• Hash indeks on B-puu indeksist andmemahult väiksem.
• Hash indeksi suurus ei kasva lineaarselt, vaid sammudena.
• Hash indeksi suurust ei mõjuta indekseeritavate väärtuste suurus.
• Hash indeksi suurust ei mõjuta see kui unikaalsed (selektiivsed) on indekseeritavas veerus olevad väärtused.

Mis on hash indeksi probleemid?

Kõigepealt hoiatas PostgreSQL pikka aega hash indeksite kasutamise eest, sest sellega kaasnesid tehnilised probleemid. Kuni PostgreSQL 9.6 oli dokumentatsioonis hoiatus:
"Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash if there were unwritten changes. Also, changes to hash indexes are not replicated over streaming or file-based replication after the initial base backup, so they give wrong answers to queries that subsequently use them. For these reasons, hash index use is presently discouraged."

Andmebaasisüsteemi uuemates versioonides on need probleemid lahendatud. Kuid ikkagi, miks mitte kasutada unikaalsuse kitsenduse toetuseks hash indeksit?

Põhjus on selles, et tulenevalt indeksi ülesehituses saab süsteem seda indeksit kasutada ainult selliste otsingute toetuseks, kus kasutatakse võrdsuse kontrolli operaatorit (=).

Tsitaat: "Hash indexes store a 32-bit hash code derived from the value of the indexed column. Hence, such indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a comparison using the equal operator:" (allikas)

(PostgreSQLi) piirangud hash indeksile.

• Indeksit ei saa luua mitmele veerule (liitindeks).
  
  • Kandidaatvõti võib olla liitvõti.
• Indeksit ei saa sorteerida.
• Indeksi alusel ei saa tabeli ridu ühekordselt sorteerida (cluster).
  • Võtmeväärtuste järgi tabeli ridade sorteerimine võib olla kasulik.
• Indeksit ei saa kasutada vahemiku järgi otsingu teostamiseks.
  • Võtmeväärtuste vahemiku järgi ridade otsimine on levinud operatsioon.
• Indeksi alusel ei saa sorteerimisoperatsiooni kiirendada.
  • Võtmeväärtuse järgi sorteerimine on levinud operatsioon.
• Seda tüüpi indeksit ei saa luua unikaalse indeksina.
• Seda tüüpi indeksi puhul ei saa PostgreSQL täita päringut nii, et loeb ainult seda indeksit (index only scan) - ikka on vaja lugeda ka tabeliplokke.
  • Sellise võimaluse kasutamise eelduseks on, et indeksis peavad olema väärtused indekseeritud veerust või need väärtused peavad olema indeksis olevate andmete alusel taastatavad. Hash indeksis on seevastu nende väärtuste räsid ning ei ole "maagilist" võtit ega funktsiooni, mille abil nendest tabelites olevad tegelikud väärtused taastada.

Nendest piirangutest tulenevalt ei sobi hash indeks ka välisvõtme veerule loodavaks indeksiks.