Kasutajatugi

Vastus (04.10.2024 12:10): Süsteemi genereeritud võtmeväärtustena võib kasutada täisarve. PostgreSQLis saab selleks kasutada SMALLSERIAL/SERIAL/BIGSERIAL notatsiooni või määrata, et vastav veerg on identiteedi veerg. Genereeritud väärtus on täisarv, mis on kas 2 baiti (SMALLSERIAL), 4 baiti (SERIAL) või 8 baiti (BIGSERIAL).

Universaalselt unikaalne identifikaator (UUID) (tuntakse ka kui globaalselt unikaalne identifikaator, GUID) on 16 baidi suurune väärtus, mille genereerimiseks kasutatud algoritm tagab, et on ülimalt vähetõenäoline, et kellelegi teisele genereeritakse sama algoritmi kasutades sama väärtus. PostgreSQLis saab määrata, et veerg on tüüpi UUID. PostgreSQLis saab lisada andmebaasi laienduse "uuid-ossp", milles olevad funktsioonid realiseerivad erinevaid UUID väärtuste genereerimise algoritme.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Kirjanduse põhjal tuuakse UUID eelistena esile:

unikaalsuse garantii üle erinevate andmebaaside (oluline hajusate andmebaaside korral).
väärtuse saab genereerida ka rakenduse poolel (loe lisaks).

ning puudustena:

suurem salvestusruumi kasutus (suurem on nii tabeli rida, kui ka UUID väärtustega veerule loodud indeks),
aeglasem ridade lisamine,
aeglasemad päringud (loe lisaks).

Punktid 2 ja 3 tulenevad punktist 1, sest andmebaasisüsteem peab vastuse andmiseks läbi vaatama rohkem plokke e lehekülgi.

Järgnev katsetus on tehtud PostgreSQL (17).

Andmebaasis on tabelid:

Party (party_id, ...)
Primaarvõti (party_id)

Person (party_id, ...)
Primaarvõti (party_id)
Välisvõti (party_id) Viitab Party (party_id)

Health_care_visit(health_care_visit_id, patient_id, ....)
Primaarvõti (health_care_visit)
Välisvõti (patient_id) Viitab Person (party_id)

Ridade arv:
Party: 100_000
Person: 70_000
Health_care_visit: 1_000_000

Välisvõtmetele on loodud indeks. Kui välisvõti kattub primaarvõtmega (vt tabel Person), siis eraldi indeksit ei looda, sest primaarvõtme toetuseks luuakse indeks automaatselt.

Ühes andmebaasis (INTEGER) on party_id tüüpi INTEGER ja teises (UUID) on party_id tüüpi UUID. UUID väärtused on genereeritud andmebaasi poolel kasutades funktsiooni uuid_generate_v4(), mis moodustab väärtuse juhuslikest arvudest. Enne katsetuste algust tegin prügikoristuse koos kasutamata salvestusruumi vabastamise ning statistika kogumisega.

VACUUM FULL ANALYZE;

Kokkuvõte: UUID korral on andmete maht suurem ning ka hõivatud plokkide arv suurem. UUID korral on ridade lisamine aeglasem. Päringud täidetakse INTEGER korral veidi kiiremalt.

Detailsemalt
Leidsin andmebaasi suuruse:

SELECT pg_size_pretty (pg_total_relation_size('party')+pg_total_relation_size('person')+pg_total_relation_size('health_care_visit'));

pg_total_relation_size leiab nii tabeli kui sellega seotud indeksite suuruse. pg_size_pretty formaadib tulemuse ilusamale kujule.

Andmebaasi INTEGER suurus: 207 MB
Andmebaasi UUID suurus: 239 MB

Tabelite (kuid mitte nende indeksite) poolt kasutatud plokkide summa

SELECT Sum(relpages) AS number_of_pages

FROM pg_class

WHERE relname in ('party','person','health_care_visit');

Andmebaas INTEGER: 13907
Andmebaas UUID: 16738

Töökiiruse katsetamisel tegin kolm katset ning leidsin tulemuse aritmeetilise keskmise. Töökiiruse leidmiseks kasutasin EXPLAIN ANALYZE ning arvestasin ainult täitmise, mitte plaani koostamise aega.

Ridade lisamine

START TRANSACTION;

EXPLAIN ANALYZE INSERT INTO Party(contact_number, comments, party_id) VALUES ('3132232323','on midagi', uuid_generate_v4());

ROLLBACK;

START TRANSACTION;
CREATE SEQUENCE seq_party START WITH 100001;

EXPLAIN ANALYZE INSERT INTO Party(contact_number, comments, party_id) VALUES ('3132232323','on midagi', nextval('seq_party'));

ROLLBACK;

Andmebaas INTEGER: 0,07 ms
Andmebaas UUID: 0,12 (167% INTEGER tulemusest)

Andmete otsimine

EXPLAIN ANALYZE SELECT from_date, thru_date, reason, visit_fee, personal_code, last_name, comments

FROM Health_care_visit INNER JOIN Person ON Health_care_visit.patient_id=Person.party_id

INNER JOIN Party USING (party_id);

Andmebaas INTEGER: 842,5 ms
Andmebaas UUID: 978,8 ms (116% INTEGER tulemusest)

EXPLAIN ANALYZE SELECT party_id

FROM Person

WHERE NOT EXISTS (SELECT *

FROM Health_care_visit

WHERE Person.party_id=Health_care_visit.patient_id);

Andmebaas INTEGER: 130,6 ms
Andmebaas UUID: 147,9 ms (113% INTEGER tulemusest)

EXPLAIN ANALYZE SELECT Count(patient_id) AS cnt

FROM Health_care_visit;

Andmebaas INTEGER: 19,6 ms
Andmebaas UUID: 22,7 ms (116% INTEGER tulemusest)

EXPLAIN ANALYZE SELECT from_date, thru_date, reason, visit_fee, personal_code, last_name, comments

FROM Health_care_visit INNER JOIN Person ON Health_care_visit.patient_id=Person.party_id

INNER JOIN Party USING (party_id)

WHERE party_id='062f2926-674f-491c-bafc-bca3d4495320';

EXPLAIN ANALYZE SELECT from_date, thru_date, reason, visit_fee, personal_code, last_name, comments

FROM Health_care_visit INNER JOIN Person ON Health_care_visit.patient_id=Person.party_id

INNER JOIN Party USING (party_id)

WHERE party_id=69718;

Andmebaas INTEGER: 0,17 ms
Andmebaas UUID: 0,19 ms (110% INTEGER tulemusest)

EXPLAIN ANALYZE SELECT patient_id, Count(*) AS cnt

FROM Health_care_visit

GROUP BY patient_id

HAVING Count(*)>20

ORDER BY patient_id;

Andmebaas INTEGER: 372,3 ms
Andmebaas UUID: 402,3 ms (108% INTEGER tulemusest)