Obvyklé problémy s SQL - struktura

Podívejme se na jiný zdroj problémů - strukturu tabulky. Opět, toto je velmi široké téma, uvedu pouze jeden častý modelový příklad, jehož další variace si jistě dovedete sestavit sami.

Nevhodný datový typ sloupce

Řekněme že do tabulky (například audit log) potřebuji zaznamenávat IP adresu klienta - velmi často se bohužel setkávám s tímto přístupem:

CREATE TABLE test_table (id INTEGER, ip VARCHAR(15));

To samozřejmě funguje, tak v čem je problém? Inu, zatímco IP adresa je tvořena pouze 4 byty, zde se pro její uložení používá cca 15 bytů, tj. zhruba 4x tolik (a to nepočítám prostor na masku sítě apod.). Ale to ještě není to nejhorší - konec konců úložná kapacita je dnes relativně levná a pár giga navíc se na disku určitě najde, ne?

Problém je v tom že daný sloupec se ve všech ohledech chová jako text - dokonce i porovnání nelze provádět binárně (po bytech) ale v úvahu se musí brát lingvistická pravidla - a ta jsou například pro češtinu poměrně "nejednoduchá," což může znamenat řádově vyšší zatížení CPU a podobně.

Naplňme výše uvedenou tabulku ukázkovými daty (generována jsou tak aby obsahovala duplicitní IP adresy, a všechny IP adresy byly oněch 15 bytů dlouhé)

INSERT INTO test_table SELECT i, (200 + round(random()*10)) || '.' ||
                                 (200 + round(random()*10)) || '.' ||
                                 (200 + round(random()*10)) || '.' ||
                                 (200 + round(random()*10))
                       FROM generate_series(1,1000000) s(i);
CREATE INDEX test_table_idx ON test_table(ip);
ANALYZE test_table;

a podívejme se na tři ukázkové dotazy:

-- vyhledávání konkrétní IP adresy
SELECT * FROM test_table WHERE ip = '205.201.207.210';

-- hledání IP adres v podsíti 205.0.0.0/8
SELECT * FROM test_table WHERE ip BETWEEN '205.0' AND '205.9';

-- počítání unikátních IP adres (počet výskytů každé)
SELECT ip, COUNT(*) FROM test_table GROUP BY ip;

No, a nyní se podívejme na jejich execution plany

db=# EXPLAIN ANALYZE SELECT * FROM test_table
                             WHERE ip = '205.201.207.210';

                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on test_table
        (cost=5.12..339.75 rows=92 width=20)
        (actual time=0.086..0.217 rows=47 loops=1)
   Recheck Cond: ((ip)::text = '205.201.207.210'::text)
   ->  Bitmap Index Scan on test_table_idx
              (cost=0.00..5.09 rows=92 width=0)
              (actual time=0.071..0.071 rows=47 loops=1)
         Index Cond: ((ip)::text = '205.201.207.210'::text)
 Total runtime: 0.300 ms
(5 rows)

To nevypadá špatně - použil se index, dotaz nevyžaduje příliš mnoho zdrojů (cost=339 není nejnižší ale jde to), a běží velmi rychle. Tak dál:

db=# EXPLAIN ANALYZE SELECT * FROM test_table
                             WHERE ip BETWEEN '205.0' AND '205.9';

                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on test_table
        (cost=251.60..6502.15 rows=9678 width=20)
        (actual time=120.095..294.266 rows=100385 loops=1)
   Recheck Cond: (((ip)::text >= '205.0'::text) AND
                  ((ip)::text <= '205.9'::text))
   ->  Bitmap Index Scan on test_table_idx
              (cost=0.00..249.18 rows=9678 width=0)
              (actual time=118.547..118.547 rows=100385 loops=1)
         Index Cond: (((ip)::text >= '205.0'::text) AND
                      ((ip)::text <= '205.9'::text))
 Total runtime: 400.851 ms
(5 rows)

To už je horší - sice se opět použil index, ale doba potřebná na vyhodnocení dotazu není zrovna příznivá. Nehledě na to že pokud potřebujete vyhledávat IP adresy dle složitějších pravidel (složitější masky sítí apod.) tak se situace bude jen a jen zhoršovat.

No a nyní poslední dotaz:

db=# EXPLAIN ANALYZE SELECT ip, COUNT(*) FROM test_table GROUP BY ip;

                              QUERY PLAN
----------------------------------------------------------------------
 HashAggregate  (cost=20884.65..21019.03 rows=10750 width=16)
                (actual time=3127.823..3151.809 rows=14641 loops=1)
   ->  Seq Scan on test_table
           (cost=0.00..15884.10 rows=1000110 width=16)
           (actual time=0.218..1169.862 rows=1000000 loops=1)
 Total runtime: 3167.560 ms
(3 rows)

Ten logicky skončil sekvenčním skenem tabulky - PostgreSQL totiž nemá data o viditelnosti v indexech, takže podobné dotazy nemůže vyhodnotit pouze pomocí indexu a nakonec do tabulky stejně musí.

Určitě si říkáte že to nejsou žádné odstrašující příklady execution planů - ano, máte pravdu. Nad sloupcem je index, vždy když to bylo užitečné tak se použil a dotazy běží celkem rychle. Zkusme ale takovou malou "fintu fň" - relační databáze kromě základních SQL typů obsahují i různé specializované datové typy, a PostgreSQL obsahuje datový typ INET určený k ukládání IP adres. Zkusme ho v tabulce použít namísto typu VARCHAR.

CREATE TABLE test_table (id INTEGER, ip INET);
INSERT INTO test_table SELECT i,((200 + round(random()*10)) || '.' ||
                                 (200 + round(random()*10)) || '.' ||
                                 (200 + round(random()*10)) || '.' ||
                                 (200 + round(random()*10)))::inet
                       FROM generate_series(1,1000000) s(i);
CREATE INDEX test_table_idx ON test_table(ip);
ANALYZE test_table;

A nyní se znovu podívejme na execution plany výše uvedených dotazů:

db=# EXPLAIN ANALYZE SELECT * FROM test_table
                             WHERE ip = '205.201.207.210';

                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on test_table
        (cost=4.98..295.32 rows=80 width=11)
        (actual time=0.061..0.227 rows=60 loops=1)
   Recheck Cond: (ip = '205.201.207.210'::inet)
   ->  Bitmap Index Scan on test_table_idx
              (cost=0.00..4.96 rows=80 width=0)
              (actual time=0.044..0.044 rows=60 loops=1)
         Index Cond: (ip = '205.201.207.210'::inet)
 Total runtime: 0.326 ms
(5 rows)

Žádná velká změna - čas ani cost dotazu se příliš nezměnil (ale to se ani nedalo čekat). Další dotaz:

db=# EXPLAIN ANALYZE SELECT * FROM test_table
                             WHERE ip BETWEEN '205.0' AND '205.9';

                              QUERY PLAN
----------------------------------------------------------------------
 Bitmap Heap Scan on test_table
        (cost=2210.12..8353.07 rows=500004 width=11)
        (actual time=33.760..221.775 rows=99585 loops=1)
   Filter: (ip << '205.0.0.0/8'::inet)
   ->  Bitmap Index Scan on test_table_idx
              (cost=0.00..2085.12 rows=99276 width=0)
              (actual time=32.549..32.549 rows=99585 loops=1)
         Index Cond: ((ip > '205.0.0.0/8'::inet) AND
                      (ip <= '205.255.255.255'::inet))
 Total runtime: 328.314 ms
(5 rows)

To už je lepší - zlepšení o cca 20% sice není nic světoborného, ale určitě nám nevadí ;-).

No a nyní poslední dotaz:

db=# EXPLAIN ANALYZE SELECT ip, COUNT(*) FROM test_table GROUP BY ip;

                              QUERY PLAN
----------------------------------------------------------------------
 HashAggregate  (cost=20884.65..21019.03 rows=10750 width=16)
                (actual time=3127.823..3151.809 rows=14641 loops=1)
   ->  Seq Scan on test_table
           (cost=0.00..15884.10 rows=1000110 width=16)
           (actual time=0.218..1169.862 rows=1000000 loops=1)
 Total runtime: 3167.560 ms
(3 rows)

Opět jen minimální rozdíl oproti variantě s VARCHAR sloupcem - tak proč to tady vůbec uvádím. Jak už jsem poznamenal, mezi VARCHAR a INET typy je markantní rozdíl co se náročnosti porovnání týká - a to se ve výše uvedených příkladech nemělo možnost projevit.

Zkusme tady trošku "brutálnější" příklad, při kterém se rychlost porovnávání projeví výrazněji. Naplňme zkušební tabulku a vyberme z ní seznam unikátních IP adres (nejdříve s indexem a pak bez indexu).

CREATE TABLE test_table (id INTEGER, ip VARCHAR(15));
INSERT INTO test_table SELECT i, (1 + round(random()*254) || '.' ||
                                  1 + round(random()*254) || '.' ||
                                  1 + round(random()*254) || '.' ||
                                  1 + round(random()*254))
                       FROM generate_series(1,1000000) s(i);
CREATE INDEX test_table_idx ON test_table(ip);
ANALYZE test_table;

-- zjisti velikost tabulky a indexu
SELECT relname, relpages, reltuples FROM pg_class
                                   WHERE relname LIKE 'test_table%';

    relname     | relpages | reltuples
----------------+----------+-----------
 test_table     |     5864 |    999918
 test_table_idx |     3819 |    999918
(2 rows)

-- načti unikátní IP adresy
EXPLAIN ANALYZE SELECT DISTINCT ip FROM test_table;

                              QUERY PLAN
----------------------------------------------------------------------
 Unique  (cost=0.00..56234.57 rows=1000129 width=14)
         (actual time=0.064..9917.689 rows=999889 loops=1)                                               
   ->  Index Scan using test_table_idx on test_table
             (cost=0.00..53734.24 rows=1000129 width=14)
             (actual time=0.059..7346.988 rows=1000000 loops=1) 
 Total runtime: 11028.266 ms                                                                                                                         
(3 rows)

-- odstraň index
DROP INDEX test_table_idx;

-- načti unikátní IP adresy
EXPLAIN ANALYZE SELECT DISTINCT ip FROM test_table;

                              QUERY PLAN
----------------------------------------------------------------------
 Unique  (cost=149720.60..154721.06 rows=1000092 width=14)
         (actual time=15914.665..22675.646 rows=999889 loops=1)                   
   ->  Sort  (cost=149720.60..152220.83 rows=1000092 width=14)
             (actual time=15914.659..20260.847 rows=1000000 loops=1)              
         Sort Key: ip                                                                                                               
         Sort Method:  external merge  Disk: 25712kB                                                                                
         ->  Seq Scan on test_table
                 (cost=0.00..15864.92 rows=1000092 width=14)
                 (actual time=0.012..1331.510 rows=1000000 loops=1) 
 Total runtime: 23764.882 ms                                                                                                        
(6 rows)

A nyní varianta s INET sloupcem:

CREATE TABLE test_table (id INTEGER, ip INET);
INSERT INTO test_table SELECT i, (1 + round(random()*254) || '.' ||
                                  1 + round(random()*254) || '.' ||
                                  1 + round(random()*254) || '.' ||
                                  1 + round(random()*254))::inet
                       FROM generate_series(1,1000000) s(i);
CREATE INDEX test_table_idx ON test_table(ip);
ANALYZE test_table;

-- zjisti velikost tabulky a indexu
SELECT relname, relpages, reltuples FROM pg_class
                                   WHERE relname LIKE 'test_table%';

    relname     | relpages |  reltuples
----------------+----------+-------------
 test_table     |     4902 | 1.00001e+06
 test_table_idx |     2745 | 1.00001e+06
(2 rows)

-- načti unikátní IP adresy
EXPLAIN ANALYZE SELECT DISTINCT ip FROM test_table;

                              QUERY PLAN
----------------------------------------------------------------------
 Unique  (cost=0.00..48086.15 rows=999995 width=7)
         (actual time=0.068..8977.387 rows=999892 loops=1)
   ->  Index Scan using test_table_idx on test_table
             (cost=0.00..45586.16 rows=999995 width=7)
             (actual time=0.065..6425.966 rows=1000000 loops=1)
 Total runtime: 10089.392 ms
(3 rows)

-- odstraň index
DROP INDEX test_table_idx;

-- načti unikátní IP adresy
EXPLAIN ANALYZE SELECT DISTINCT ip FROM test_table;

                              QUERY PLAN
----------------------------------------------------------------------
 Unique  (cost=141909.78..146909.82 rows=1000008 width=7)
         (actual time=5965.684..10284.653 rows=999892 loops=1)
   ->  Sort  (cost=141909.78..144409.80 rows=1000008 width=7)
             (actual time=5965.679..7893.319 rows=1000000 loops=1)
         Sort Key: ip
         Sort Method:  external merge  Disk: 18560kB
         ->  Seq Scan on test_table
                 (cost=0.00..14902.08 rows=1000008 width=7)
                 (actual time=0.012..1304.278 rows=1000000 loops=1)
 Total runtime: 11370.620 ms
(6 rows)

Z toho lze vyvodit následující tři pozorování:

  • Volbou správného typu sloupce lze výrazně snížit prostor obsazený na pevném disku. Tabulka s INET sloupcem je oproti tabulce s VARCHAR sloupcem cca o 20% menší, index nad INET sloupem je ve srovnání s indexem nad VARCHAR sloupem menší dokonce o 30%.
  • Pokud se použijí indexy, jsou oba dotazy zhruba stejně rychlé (10 vteřin v případě použití INET sloupce oproti 11 vteřinám v případě VARCHAR sloupce), protože indexy radikálně snižují potřebný počet porovnání (a tudíž se neprojeví rozdílná náročnost porovnávání).
  • V případě dotazů bez indexů na plné čáře vyhrává varianta s INET sloupcem (11 vteřin oproti téměř 24 vteřinám potřebným pro vyhodnocení varianty s VARCHAR sloupcem).

Ano, máte pravdu - nejsou to tak markantní rozdíly jako v prvním díle tohoto miniseriálu, ve kterém bylo demonstrování že správné použití může klidně znamenat i rozdíl několika řádů, ale dvojnásobné zrychlení přeci také není k zahození, ne?

Komentáře

K tomuto článku zatím žádné komentáře neexistují (nebo čekají na schválení).

Nový komentář

Všechny komentáře podléhají schválení - mezi odesláním komentáře a jeho zobrazením na této stránce tedy může být prodleva. Vyplníte-li e-mailovou adresu, budete o schválení či neschválení komentáře informováni.

V titulku ani v textu nejsou povoleny HTML tagy - budou automaticky odstraněny. Odstavec ukončíte prázdným řádkem.

(nepovinné)