Vastus (25.03.2024 12:13): Looge tekstilisele veerule indeks ja kasutage selle puhul operaatori klassi, mis pole vaikimisi klass. TEXT tüüpi veeru korral kasutage operaatori klassi text_pattern_ops, VARCHAR tüüpi veeru korral varchar_pattern_ops ja CHAR tüüpi veeru korral char_pattern_ops. Operaatori klass määrab, milliste operaatorite kasutamise korral päringu tingimuses seda indeksit saaks kasutada. Eelnimetatud operaatori klassid võimaldavad, indeksit kasutada mustripõhise otsingu korral, kui soovitakse otsida stringi alguse järgi.

Järgnev näide on tehtud läbi PostgreSQL (16) põhjal.

Loon tabeli.
CREATE TABLE Isik (eesnimi VARCHAR(50) NOT NULL,
perenimi VARCHAR(50) NOT NULL);

Lisan tabelisse juhuslikult genereeritud 100 000 ees- ja perenime pikkusega 1 kuni 50 märki. Nimedes kasutatakse eesti tähestiku tähti.
INSERT INTO Isik(eesnimi, perenimi)
SELECT (SELECT initcap(string_agg(substring('abcdfghijklmnopqrstuvwõäöüxyz', floor(random() * (29 - 1 + 1) + 1)::int, 1), '')) AS string
FROM generate_series(1, floor(random() * (50 - 1 + 1) + 1)::int+(generator*0))) AS eesnimi,
(SELECT initcap(string_agg(substring('abcdfghijklmnopqrstuvwõäöüxyz', floor(random() * (29 - 1 + 1) + 1)::int, 1), '')) AS string
FROM generate_series(1, floor(random() * (50 - 1 + 1) + 1)::int+(generator*0))) AS perenimi
FROM generate_series(1, 100_000) AS generator;

Loon eesnime veerule B+ puu indeksi (see on vaikimisi indeksi tüüp), kus veergude puhul kasutatakse vaikimisi operaatori klassi.
CREATE INDEX idx_isik_nimi ON Isik(perenimi, eesnimi);

Värskendan andmebaasi statistikat.
ANALYZE;

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE eesnimi='Mart'
AND perenimi='Mets';
Päringu täitmiseks kasutatakse indeksit. Toimub Index Only Scan operatsioon ning andmeid loetakse ainult indeksist, ilma tabeli poole pöördumata.
Täitmisplaani täitmiseks kulub 0.303 ms.

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE eesnimi='Mart';
Päringu täitmiseks ei kasutata indeksit, sest otsitakse vaid eesnime järgi. Samas tegemist on liitindeksiga, kus esimesel kohal on perenimi.
Täitmisplaani täitmiseks kulub 11.456 ms.

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE perenimi<'Mets';
Päringu täitmiseks kasutatakse indeksit. Toimub Index Only Scan operatsioon ning andmeid loetakse ainult indeksist, ilma tabeli poole pöördumata.
Täitmisplaani täitmiseks kulub 11.499 ms.

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE perenimi<>'Mets';
Päringu täitmiseks ei kasutata indeksit. Toimub Seq Scan operatsioon ning andmeid loetakse ainult tabelist, ilma indeksi poole pöördumata.
Täitmisplaani täitmiseks kulub 20.710 ms.

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE perenimi LIKE 'M%';
Päringu täitmiseks ei kasutata indeksit. Toimub Seq Scan operatsioon ning andmeid loetakse ainult tabelist, ilma indeksi poole pöördumata.
Täitmisplaani täitmiseks kulub 14.647 ms.

Kustutan indeksi.
DROP INDEX idx_isik_nimi;

Loon indeksi uuesti kasutades operaatori klassi varchar_pattern_ops.
CREATE INDEX idx_isik_nimi ON Isik(perenimi varchar_pattern_ops, eesnimi varchar_pattern_ops);
Indeksi suurus on peaaegu sama kui eelneval indeksil, ehki mitte identne.

ANALYZE;

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE eesnimi='Mart'
AND perenimi='Mets';
Päringu täitmiseks kasutatakse indeksit.
Täitmisplaani täitmiseks kulub 0.112 ms.

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE eesnimi='Mart';
Päringu täitmiseks ei kasutata endiselt indeksit.
Täitmisplaani täitmiseks kulub 11.133 ms.

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE perenimi<'Mets';
Päringu täitmiseks ei kasutata enam indeksit. NB! kui tahaksin teha päringuid tingimustega, kus kasutatakse >=, >, < või <= operaatoreid, siis võiks lisaks luua ka vaikimisi operaatori klassiga indeksi, mida andmebaasisüsteem saaks kasutada selliste päringute täitmiseks. Ühel ja samal tabeli veerul võib olla mitu indeksit.
Täitmisplaani täitmiseks kulub 21.644 ms.

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE perenimi<>'Mets';
Päringu täitmiseks ei kasutata endiselt indeksit.
Täitmisplaani täitmiseks kulub 19.372 ms.

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE perenimi LIKE 'M%';
Päringu täitmiseks kasutatakse indeksit, sest lisatud indeks võimaldab otsida stringi alguse järgi.
Täitmisplaani täitmiseks kulub 2.536 ms.

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE eesnimi LIKE 'M%'
AND perenimi LIKE 'M%';
Päringu täitmiseks kasutatakse indeksit, sest lisatud indeks võimaldab otsida stringi alguse järgi.
Täitmisplaani täitmiseks kulub 0.402 ms.

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE perenimi ~ '^M';
Päringu täitmiseks kasutatakse indeksit, sest lisatud indeks võimaldab otsida stringi alguse järgi.
Täitmisplaani täitmiseks kulub 2.384 ms.

EXPLAIN ANALYZE SELECT *
FROM Isik
WHERE perenimi LIKE '_M%';
Päringu täitmiseks ei kasutata indeksit, sest teadaolev stringi osa pole stringi alguses.
Täitmisplaani täitmiseks kulub 11.936 ms.