# Datovy typ primarniho klice -- testovani
## Priprava databaze
Vychazime z nasledujicho schematu pro testovani daneho typu.
### Integer
Pro integer se pridala sekvence generovani id.
```sql=
CREATE TABLE IF NOT EXISTS integer_primary_key
(
id serial PRIMARY KEY,
zero smallint
);
```
### Textové typy
Jelikoz jsme pouzili stejnou funkci na "nahodne" generovani textu, pote budou rozdili v case naklady na rezii okolo ruznych textovych typu.
```sql=
CREATE OR REPLACE FUNCTION text_pk_gen() RETURNS text AS $$
BEGIN
return MD5(random()::text);
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS text_primary_key
(
id text PRIMARY KEY DEFAULT text_pk_gen(),
zero smallint
);
```
```sql=
CREATE OR REPLACE FUNCTION char_pk_gen() RETURNS char(128) AS $$
BEGIN
return MD5(random()::text);
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS char128_primary_key
(
id char(128) PRIMARY KEY DEFAULT char_pk_gen(),
zero smallint
);
```
```sql=
CREATE OR REPLACE FUNCTION varchar_pk_gen() RETURNS varchar AS $$
BEGIN
return MD5(random()::text);
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS varchar_primary_key
(
id varchar PRIMARY KEY DEFAULT varchar_pk_gen(),
zero smallint
);
```
```sql=
CREATE OR REPLACE FUNCTION varchar128_pk_gen() RETURNS varchar(128) AS $$
BEGIN
return MD5(random()::text);
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS varchar128_primary_key
(
id varchar(128) PRIMARY KEY DEFAULT varchar128_pk_gen(),
zero smallint
);
```
### UUID
```sql=
CREATE TABLE IF NOT EXISTS uuid_primary_key
(
id uuid PRIMARY KEY DEFAULT gen_random_uuid (),
zero smallint
);
```
### Network address
```sql=
--Functions for generating ip addresses
CREATE OR REPLACE FUNCTION inet_pk_gen() RETURNS inet AS $$
BEGIN
return CONCAT(
TRUNC(RANDOM() * 255 + 1), '.' ,
TRUNC(RANDOM() * 255 + 1), '.',
TRUNC(RANDOM() * 255 + 1), '.',
TRUNC(RANDOM() * 255 + 1)
)::INET;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION varchar_pk_gen() RETURNS inet AS $$
BEGIN
return CONCAT(
TRUNC(RANDOM() * 255 + 1), '.' ,
TRUNC(RANDOM() * 255 + 1), '.',
TRUNC(RANDOM() * 255 + 1), '.',
TRUNC(RANDOM() * 255 + 1)
);
END;
$$ LANGUAGE plpgsql;
--Definitions of tables
create table inet_primary_key(
id inet primary key default inet_pk_gen(),
zero smallint
);
create table varchar18_primary_key(
id varchar(18) primary key default varchar_pk_gen(),
zero smallint
);
```
## Testovanie
Pouzivame pgbench na simulovani poctu useru a requestu. Každý test sme opakovali 5x. Výsledky meraní sme spriemerovali.
#### Insert test
```bash
pgbench -c 10 -t 1000 -j 1 pa036 -f <script>.sql
```
#### Select test
```bash
pgbench -c 10 -t 1 -j 1 pa036 -f <script>.sql
```
Sledovane hodnoty:
- latency average (ms)
- transactions per second (tps)
### Testovací stroj
- Ryzen 5900HS 8c/16t 3.0-4.6Ghz
- 512GB PCI-E gen4 SSD
- 16GB RAM
### Nastavenia postresql
PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg21.10+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.2.0-7ubuntu2) 11.2.0, 64-bit
- work_mem = 4 MB
- effective_cache_size = 524288 disk pages (usuall 8 KB)
## Insert
V tomto teste sme vkladali do tabuľky 10000 záznamov. Tabuľky sme medzi testami nepremazávali.
### Integer
```sql=
insert into integer_primary_key (zero) VALUES (0);
```
| Nazev | hodnota |
| ----------------------- | -------- |
| latency average | 1,0948 ms |
| transactions per second | 9134,1049 |
### Textové typy
### text
```sql=
insert into text_primary_key (zero) VALUES (0);
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 1,0470 ms |
| transactions per second | 9305,3862 |
### char128
```sql=
insert into char128_primary_key (zero) VALUES (0);
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 1,041 ms |
| transactions per second | 9606,702611 |
### varchar
```sql=
insert into varchar_primary_key (zero) VALUES (0);
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 1,09 ms |
| transactions per second | 9147,844848 |
### varchar128
```sql=
insert into varchar128_primary_key (zero) VALUES (0);
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 1,0778 ms |
| transactions per second | 9281,56095 |
### UUID
```sql=
insert into uuid_primary_key (zero) VALUES (0);
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 1,0677 ms |
| transactions per second | 9345,7541 |
### Network address
### inet
```sql=
insert into inet_primary_key (zero) VALUES (0);
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 1,1490 ms |
| transactions per second | 8701,3498 |
### varchar18
```sql=
insert into varchar18_primary_key (zero) VALUES (0);
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 1,1432 ms |
| transactions per second | 8748,894 |
## Select
V tomto teste sme predpripravili tabuľky s miliónom záznamov a testovali sme select pre desať tisíc záznamov
#### Integer
```sql=
...
select * from integer_primary_key where id = 764662
...
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 554,3968 |
| transactions per second | 18,0919764 |
### Textové typy
### text
```sql=
...
SELECT id, zero FROM text_primary_key WHERE id = '1e1FCVOnjWoxdOmXAkosDB0LRlkag0chzK5KSNwZBbmozYudeitkDe3atsojzdpe9HYOXIqKMyMg6lRP2jS9txmeqy';
...
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 651,4806 |
| transactions per second | 15,3508308 |
### char128
```sql=
...
SELECT id, zero FROM char128_primary_key WHERE id = 'sBn55B4z9HaU3b3yPfMFaLFes6n5tUi87VSyWFGmtkExoeEme6DqpBNv3OOGolemuMOHEmf0waOPe7mqcyV8vlv2PU4XKSpDOwy40k3oR4adKQIEkoDADWZgEQ9BZ79p';
...
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 614,9822 |
| transactions per second | 16,2692894 |
### varchar
```sql=
...
SELECT id, zero FROM varchar_primary_key WHERE id = 'TfzREatZQEEBs1UVII5g4ufIaXF3AU1qspsedkUhx7JF8teyWQBDTxPdOCuqC9FotOFNsbyPp6DkxoNsTK51EhV0zksXOpx1FthAiORAMtogX8Kdkvf2KNR';
...
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 630,8586 |
| transactions per second | 15,8634568 |
### varchar128
```sql=
...
SELECT id, zero FROM varchar128_primary_key WHERE id = 'b8n3aZ6Q7qTFTmZfjJT7mBAIs64OmJEMCHuJlCuNAQYTJPbhJMz1FaqInd93QLyHonxY5wZMNMtUkkWY0ZAUoJSi9jSzLYRVTfA9BW7V5Okhu';
...
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 620,5056 |
| transactions per second | 16,124905 |
### bigtext
```sql=
...
SELECT id, zero FROM bigtext_primary_key WHERE id = 'nyLWjG4F24tyxurf5x7jEMBUWnT6WRTtTxK0e2BJQrzvDrdiDWgkodYpkzpUxDhTqL3vWixfnHymaud9xRkYh0ANJwYumgMbiuLoNlAZkgyxd5Xsdo1lzm54ApPgSB63iuAOAn9Ue3pRqJM6VvJKvLan5ZrdLZ2LgVPm0BEP1XjDHoC1ligSVY7CtfxN0mc9VH8fmgQc6nwJTMuz3hP9xtUX50hVpzBhg0ZJY69BVOwTqSe5ufiOdxRLqjLoJaN55VyiORAw0fErus14MCEcjrNag9DjVEtLeznqx5MTiu8auClDlHUeu2oGNNh7v2GstQAqj6DK65kvHOYj1vEHCjFLo2oUxSvdyclAjEw4NPNeOuw4CYbgv0Bt9Qw1xQr2LKnXP9UOXeWPTepKDk4XZ6udAMJA9lUdJlhnSPXfoOLrfGhpyP0I8Q9R7Gqmkw9hv9LovJETsi5ZtNdaE8jsPSKpopLPzpoOpo6cVlkj4ch6lt4YTzeUvbyW4UzR0m7i4wxrdjtL64rO2MB5AYZC7NVZ0DPUtzHWsDTTviJhAWlbQlyDKgAjxytRN3eQkfGj2cqjELGBufzmOL2Kx4MitHPyKGU2JPtCqiwYHQtRVzsZ3QpjhwskwdknMD7X6LGc1yWI';
...
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 698,372 |
| transactions per second | 14,323184 |
#### UUID
```sql=
...
select * from uuid_primary_key where id = '16dc56d7-269b-4ff1-bea8-d5ca8533a4c8';
...
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 561,6474 |
| transactions per second | 17,8225532 |
### Network address
### inet
```sql=
...
select id, zero from inet_primary_key where id = '184.200.130.67';
...
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 570,0558 |
| transactions per second | 17,558676 |
### varchar18
```sql=
...
select id, zero from varchar18_primary_key where id = '184.200.130.67';
...
```
| Nazev | hodnota |
| ----------------------- | ------- |
| latency average | 621,0332 |
| transactions per second | 16,1045912 |
## Velikosti tabulek
| Table name | index size | table size | total size |
| ---------------------- | ---------- | ---------- | ---------- |
| varchar18_primary_key | 46 MB | 38 MB | 84 MB |
| varchar128_primary_key | 99 MB | 122 MB | 222 MB |
| varchar_primary_key | 99 MB | 122 MB | 222 MB |
| uuid_primary_key | 50 MB | 39 MB | 88 MB |
| text_primary_key | 99 MB | 122 MB | 221 MB |
| integer_primary_key | 35 MB | 21 MB | 56 MB |
| inet_primary_key | 42 MB | 29 MB | 72 MB |
| char128_primary_key | 160 MB | 214 MB | 373 MB |
| bigtext_primary_key | 538 MB | 920 MB | 1458 MB |
### Grafy
## Sumár
Na základe našich testov sme zistili, že rýchlosť vkladania do tabuľky sa medzi jednotlivými typmi líši len málo.
Pre selecty 10000 nahodnych hodnot z vlozenych sme zaznamenali vacsie rozdiely.
Napriklad inet bol o 9 % rýchlejší ako varchar18. Keďže inet poskytuje aj ďalšie funkcie na zisťovanie príslušnosti k sieti, nevidíme význam v používaní charakterového typu pre ukladanie internetových adries.
#### Veľkosti tabuliek
Veľkosť tabuliek a teda aj indexov sa zvyšovala podľa priestorovej náročnosti dátového typu. Integer mal veľkosť najmenšiu. Textové typy mali veľkosti podľa množstva textu uloženého v riadkoch. Z tohto pohľadu je najhorši char128, ktorý na rozdiel od varchar a text má vždy 128 znakov a teda potencionálne plytvá miesto doplnením znakov.
Neintuitívne bolo, že velkosť indexov v prípade varchar 18, uuid, integer a inet bola väčšia ako velkosť samotnej tabulky.
Google Drive
Gist
Clipboard
Sign in with Wallet
