Snaha odlehčit databázi přes LIMIT / OFFSET

Uvažujme dva systémy které si musí pravidelně vyměňovat data, přičemž to nelze realizovat přes sdílenou databází - například dvě webové aplikace na oddělených serverech. Jednou z možností je skript (např. PHP) který - po ověření loginu a hesla - vypíše požadovaná data například ve formátu CSV. Řekněme že dat je netriviální množství (řádově desetitisíce položek, přičemž každá zabírá několik kB), takže každé "načtení dat" znamená řádově desítky MB dat.

V jednom nejmenovaném českém e-shopu to bylo řešeno právě takto - existovala centrální tabulka produktů, zhruba s následující strukturou (v realitě je sloupců daleko víc, pro demonstraci stačí toto):

CREATE TABLE Produkty (
    id            SERIAL PRIMARY KEY,
    nazev         VARCHAR(100),
    popis         TEXT,
    exportovat    BOOLEAN NOT NULL DEFAULT 't',
    pocet_skladem INTEGER NOT NULL DEFAULT 0,
    cena          NUMERIC(10,2)
);

a několik "spřátelených webů" si pravidelně - často i několikrát denně - načítalo aktuální seznam produktů. Tabulka měla cca 40.000 záznamů a cca 20.000 z nich bylo exportováno. Exportovací skript vypadal zhruba takto:

-- zjisteni poctu (WHERE exportovat AND (pocet_skladem > 0))
$pocet = pocet_vyrobku_k_exportu();

-- nacitani a vypis po blocich o velikosti 100
$pocetBloku = ceil($pocet / 100);

FOR i IN 0..($pocetBloku - 1) LOOP

    -- nacti a vypis
    SELECT * FROM Produkty WHERE exportovat AND (pocet_skladem > 0)
                           ORDER BY id LIMIT 100 OFFSET ($i*100);

END LOOP;

Soustřeďte se zejména na rozsekávání do bloků. Programátora k němu vedla snaha o "optimalizaci" paměťové náročnosti (původní praxe byla takové že se do PHP načetl celý resultset naráz a až pak byl vypsán) a rychlosti zpracování.

Naplňme nyní tabulku zkušebními daty (50.000 řádek)

db=# INSERT INTO Produkty
          SELECT i AS id,
                 md5(i::text) AS nazev,
                 repeat(md5(i::text), 100) AS popis,
                 (random()*100 < 95) AS exportovat,
                 round(random()*10) AS pocet_skladem,
                 (round(random()*10000) / 100) AS cena
            FROM generate_series(1,50000) s(i);

a podívejme se jak bude zpracován první blok

EXPLAIN ANALYZE SELECT * FROM Produkty
                        WHERE exportovat AND (pocet_skladem > 0)
                     ORDER BY id LIMIT 100 OFFSET 0;

                              QUERY PLAN
---------------------------------------------------------------------
 Limit  (cost=0.00..7.97 rows=100 width=758)
        (actual time=0.034..0.441 rows=100 loops=1)
   ->  Index Scan using produkty_pkey on produkty
             (cost=0.00..3936.25 rows=49412 width=758)
             (actual time=0.031..0.218 rows=100 loops=1)
         Filter: (exportovat AND (pocet_skladem > 0))
 Total runtime: 0.584 ms
(4 rows)

Pokud by takto byly vyhodnoceny všechny části (kterých je 500), znamenalo by to kompletní vyhodnocení zhruba za 300ms. Problém je v tom že doba potřebná k vyhodnocení dané části závisí na jejím offsetu, a lineárně roste - v mém případě zhruba o 30ms na každých 10000 záznamů.

db=# EXPLAIN ANALYZE SELECT * FROM Produkty
                             WHERE exportovat AND (pocet_skladem > 0)
                          ORDER BY id LIMIT 100 OFFSET 10000;

                              QUERY PLAN
---------------------------------------------------------------------
 Limit  (cost=796.62..804.59 rows=100 width=758)
        (actual time=29.784..30.178 rows=100 loops=1)
   ->  Index Scan using produkty_pkey on produkty
             (cost=0.00..3936.25 rows=49412 width=758)
             (actual time=0.027..19.315 rows=10100 loops=1)
         Filter: (exportovat AND (pocet_skladem > 0))
 Total runtime: 30.323 ms
(4 rows)

db=# EXPLAIN ANALYZE SELECT * FROM Produkty
                             WHERE exportovat AND (pocet_skladem > 0)
                          ORDER BY id LIMIT 100 OFFSET 20000;

                              QUERY PLAN
---------------------------------------------------------------------
 Limit  (cost=1593.24..1601.20 rows=100 width=758)
        (actual time=62.815..63.225 rows=100 loops=1)
   ->  Index Scan using produkty_pkey on produkty
             (cost=0.00..3936.25 rows=49412 width=758)
             (actual time=0.027..38.742 rows=20100 loops=1)
         Filter: (exportovat AND (pocet_skladem > 0))
 Total runtime: 63.378 ms
(4 rows)

db=# EXPLAIN ANALYZE SELECT * FROM Produkty
                             WHERE exportovat AND (pocet_skladem > 0)
                          ORDER BY id LIMIT 100 OFFSET 30000;

                              QUERY PLAN
---------------------------------------------------------------------
 Limit  (cost=2389.86..2397.82 rows=100 width=758)
        (actual time=91.332..91.742 rows=100 loops=1)
   ->  Index Scan using produkty_pkey on produkty
             (cost=0.00..3936.25 rows=49412 width=758)
             (actual time=0.028..58.388 rows=30100 loops=1)
         Filter: (exportovat AND (pocet_skladem > 0))
 Total runtime: 91.889 ms
(4 rows)

Průměrná doba vyhodnocení části tedy bude cca 75ms, a to při 500 částech dává cca 37,5 vteřin. Zkusme nyní jednu kacířskou úpravu - odstraňme LIMIT / OFFSET klauzule:

im=# EXPLAIN ANALYZE SELECT * FROM Produkty
                        WHERE exportovat AND (pocet_skladem > 0)
                     ORDER BY id;

                              QUERY PLAN
---------------------------------------------------------------------
 Index Scan using produkty_pkey on produkty
       (cost=0.00..3936.25 rows=49412 width=758)
       (actual time=0.031..128.397 rows=48017 loops=1)
   Filter: (exportovat AND (pocet_skladem > 0))
 Total runtime: 182.205 ms
(3 rows)

Jak vidět, odstraněním "vychytralé LIMIT/OFFSET kontrukce" se vyhodnocení výrazně urychlilo.

Co se týká snahy ušetřit pomocí LIMIT / OFFSET paměť, daleko efektivnější je nenačítat celý resultset najednou, ale pěkně řádek po řádku a vždy ho zpracovat.