Optymalizacja zapytań w PostgreSQL

Uwagi techniczne

Załączony program

Do tego dokumentu dołączony jest program użyty do uzyskania opisanych tutaj danych. Został on napisany w Pythonie. Wymagania:

• Python 2.5
• PostgreSQL 8.0 lub nowsze
• psycopg2 (często paczkowany jako python-psycopg2)

Program czyta zapytania z pliku pg.test. Konfiguracja połączenia do bazy podana jest w zmiennej DSN na początku pliku programu (czyli db.py).

Użycie:

python db_test.py load [rozmiar]

tworzy strukturę bazy i wstawia do niej dane, jeśli rozmiar zostanie podany wtedy zostanie stworzone tyle rekordów ile podano

python db_test.py explain [test1] [test2] … [testN]

wyświetla plany zapytań, jeśli nie zostały podane jawnie to wykonuje wszystkie z pliku pg.test

python db_test.py speed [test1] [test2] … [testN]

testuje czas wykonania zapytań zapytań, jeśli nie zostały podane jawnie to wykonuje wszystkie z pliku pg.test

W poniższych wynikach komend pozwalam sobie na pominięcie niektórych linii.

Struktura bazy danych

Struktura bazy jest tworzona za pomocą poleceń:

CREATE TABLE item_0(id serial PRIMARY KEY, pay float, section char(1), name varchar(50));
...
CREATE TABLE item_4(id serial PRIMARY KEY, pay float, section char(1), name varchar(50))


CREATE INDEX idx_item_1_pay     ON item_1 USING btree (pay);
CREATE INDEX idx_item_1_section ON item_1 USING btree (section);
CREATE INDEX idx_item_1_name    ON item_1             (name)")

CREATE INDEX idx_item_2_pay     ON item_2 USING hash (pay);
CREATE INDEX idx_item_2_section ON item_2 USING hash (section);
CREATE INDEX idx_item_2_name    ON item_2            (name varchar_pattern_ops);

CREATE INDEX idx_item_3_pay0    ON item_3            (pay) WHERE pay BETWEEN 0000 AND  1000;
...
CREATE INDEX idx_item_3_pay10   ON item_3            (pay) WHERE pay BETWEEN 9000 AND 10000;
CREATE INDEX idx_item_3_name    ON item_3            (substring(name, 0, 1));

CREATE INDEX idx_item_4_multi   ON item_4            (section, pay);

Do tabel są wstawiane losowo wygenerowane rekordy, tak aby każda z nich miała identyczną zawartość. Czyli ostatecznie tabele różnią się tylko indeksami.

Wszystkie tu podane testy zostały wykonane na bazie zawierającej po 1 milion rekordów w jednej tabeli.

Warunki testu

Test był prowadzony na Intel Core2 Duo T7300 @ 2.00GHz; 3GB pamięci operacyjnej.

Bazy bardzo intensywnie korzystaja z cachowania wyników w pamięci. Aby dało się porównać wyniki to podane tu listingi pochodzą z drugiego uruchomienia danego zapytania.

Aby móc zobaczyć różnicę to przykład z 1 uruchomienia:

python pg.py explain index_equal
  SELECT pay FROM item_0 WHERE pay = 9500
      Seq Scan on item_0  (cost=0.00..20609.56 rows=101 width=8) (actual time=1.683..253.070 rows=127 loops=1)
        Filter: (pay = 9500::double precision)
      Total runtime: 253.288 ms
  real time 433.31 ms

  SELECT pay FROM item_1 WHERE pay = 9500
      Bitmap Heap Scan on item_1  (cost=5.24..418.64 rows=113 width=8) (actual time=0.199..0.777 rows=127 loops=1)
        Recheck Cond: (pay = 9500::double precision)
        ->  Bitmap Index Scan on idx_item_1_pay  (cost=0.00..5.21 rows=113 width=0) (actual time=0.133..0.133 rows=127 loops=1)
              Index Cond: (pay = 9500::double precision)
      Total runtime: 1.051 ms
  real time 823.78 ms

  SELECT pay FROM item_2 WHERE pay = 9500
      Bitmap Heap Scan on item_2  (cost=5.33..418.74 rows=113 width=8) (actual time=0.362..0.930 rows=127 loops=1)
        Recheck Cond: (pay = 9500::double precision)
        ->  Bitmap Index Scan on idx_item_2_pay  (cost=0.00..5.30 rows=113 width=0) (actual time=0.285..0.285 rows=127 loops=1)
              Index Cond: (pay = 9500::double precision)
      Total runtime: 1.207 ms
  real time 879.79 ms

  SELECT pay FROM item_3 WHERE pay = 9500
      Bitmap Heap Scan on item_3  (cost=5.09..397.57 rows=107 width=8) (actual time=0.184..0.787 rows=127 loops=1)
        Recheck Cond: (pay = 9500::double precision)
        ->  Bitmap Index Scan on idx_item_3_pay9  (cost=0.00..5.06 rows=107 width=0) (actual time=0.119..0.119 rows=127 loops=1)
              Index Cond: (pay = 9500::double precision)
      Total runtime: 1.079 ms
  real time 827.77 ms

  SELECT pay FROM item_4 WHERE pay = 9500
      Seq Scan on item_4  (cost=0.00..20615.00 rows=113 width=8) (actual time=5.528..337.332 rows=127 loops=1)
        Filter: (pay = 9500::double precision)
      Total runtime: 337.577 ms
  real time 1898.83 ms

A tu z 2 uruchomienia:

python pg.py explain index_equal
  SELECT pay FROM item_0 WHERE pay = 9500
      Seq Scan on item_0  (cost=0.00..20609.56 rows=101 width=8) (actual time=1.616..259.265 rows=127 loops=1)
        Filter: (pay = 9500::double precision)
      Total runtime: 259.491 ms
  real time 336.76 ms

  SELECT pay FROM item_1 WHERE pay = 9500
      Bitmap Heap Scan on item_1  (cost=5.24..418.64 rows=113 width=8) (actual time=0.066..0.332 rows=127 loops=1)
        Recheck Cond: (pay = 9500::double precision)
        ->  Bitmap Index Scan on idx_item_1_pay  (cost=0.00..5.21 rows=113 width=0) (actual time=0.042..0.042 rows=127 loops=1)
              Index Cond: (pay = 9500::double precision)
      Total runtime: 0.503 ms
  real time 2.18 ms

  SELECT pay FROM item_2 WHERE pay = 9500
      Bitmap Heap Scan on item_2  (cost=5.33..418.74 rows=113 width=8) (actual time=0.124..0.402 rows=127 loops=1)
        Recheck Cond: (pay = 9500::double precision)
        ->  Bitmap Index Scan on idx_item_2_pay  (cost=0.00..5.30 rows=113 width=0) (actual time=0.100..0.100 rows=127 loops=1)
              Index Cond: (pay = 9500::double precision)
      Total runtime: 0.583 ms
  real time 1.96 ms

  SELECT pay FROM item_3 WHERE pay = 9500
      Bitmap Heap Scan on item_3  (cost=5.09..397.57 rows=107 width=8) (actual time=0.062..0.331 rows=127 loops=1)
        Recheck Cond: (pay = 9500::double precision)
        ->  Bitmap Index Scan on idx_item_3_pay9  (cost=0.00..5.06 rows=107 width=0) (actual time=0.038..0.038 rows=127 loops=1)
              Index Cond: (pay = 9500::double precision)
      Total runtime: 0.498 ms
  real time 2.65 ms

  SELECT pay FROM item_4 WHERE pay = 9500
      Seq Scan on item_4  (cost=0.00..20615.00 rows=113 width=8) (actual time=1.682..269.960 rows=127 loops=1)
        Filter: (pay = 9500::double precision)
      Total runtime: 270.174 ms
  real time 269.69 ms

Jak widać różnica jest olbrzymia, ale tylko w „real time”. Wyniki samego „EXPLAIN” się nie różnią.

Format listingu

python pg.py explain index_equal
  SELECT pay FROM item_0 WHERE pay = 9500
      Seq Scan on item_0  (cost=0.00..20609.56 rows=101 width=8) (actual time=1.616..259.265 rows=127 loops=1)
        Filter: (pay = 9500::double precision)
      Total runtime: 259.491 ms
  real time 336.76 ms

  result size 54611 records

Poszczególne elementy wyniku oznaczono kolorami:

EXPLAIN

Wypisuje kolejne kroki jakie będą wykonane podczas wykonywania zapytania. Podaje oszacowane statystyki.

EXPLAIN ANALYZE

Użycie EXPLAIN ANALYZE w miejsce EXPLAIN spowoduje dokładniejsze oszacowanie statystyk zapytania. Poda również oszacowany czas w milisekundach.

pg.py

Sprawdza ile czasu na prawdę wymaga zapytanie (łącznie z pobraniem danych z bazy), sprawdza ile wyników uzyskano oraz czy wyniki w grupie zapytań są identyczne

W EXPLAIN bardzo ważne jest, że ono tylko szacuje dane i wyniki typu rows=101 należy traktować jak sugestię rzędu wyniku a nie wiążące dane.

Testy

Indeksy

Zapytania równościowe

python pg.py explain index_equal
  SELECT pay FROM item_0 WHERE pay = 9500
      Seq Scan on item_0  (cost=0.00..20609.56 rows=101 width=8) (actual time=1.560..255.903 rows=127 loops=1)
        Filter: (pay = 9500::double precision)
      Total runtime: 256.110 ms
  real time 261.04 ms

  SELECT pay FROM item_1 WHERE pay = 9500
      Bitmap Heap Scan on item_1  (cost=5.24..418.64 rows=113 width=8) (actual time=0.070..0.346 rows=127 loops=1)
        Recheck Cond: (pay = 9500::double precision)
        ->  Bitmap Index Scan on idx_item_1_pay  (cost=0.00..5.21 rows=113 width=0) (actual time=0.044..0.044 rows=127 loops=1)
              Index Cond: (pay = 9500::double precision)
      Total runtime: 0.518 ms
  real time 1.98 ms

  SELECT pay FROM item_2 WHERE pay = 9500
      Bitmap Heap Scan on item_2  (cost=5.33..418.74 rows=113 width=8) (actual time=0.130..0.416 rows=127 loops=1)
        Recheck Cond: (pay = 9500::double precision)
        ->  Bitmap Index Scan on idx_item_2_pay  (cost=0.00..5.30 rows=113 width=0) (actual time=0.104..0.104 rows=127 loops=1)
              Index Cond: (pay = 9500::double precision)
      Total runtime: 0.590 ms
  real time 3.32 ms

  SELECT pay FROM item_3 WHERE pay = 9500
      Bitmap Heap Scan on item_3  (cost=5.09..397.57 rows=107 width=8) (actual time=0.083..0.379 rows=127 loops=1)
        Recheck Cond: (pay = 9500::double precision)
        ->  Bitmap Index Scan on idx_item_3_pay9  (cost=0.00..5.06 rows=107 width=0) (actual time=0.055..0.055 rows=127 loops=1)
              Index Cond: (pay = 9500::double precision)
      Total runtime: 0.545 ms
  real time 3.44 ms

  SELECT pay FROM item_4 WHERE pay = 9500
      Seq Scan on item_4  (cost=0.00..20615.00 rows=113 width=8) (actual time=1.416..260.032 rows=127 loops=1)
        Filter: (pay = 9500::double precision)
      Total runtime: 260.255 ms
  real time 300.01 ms

 SELECT pay FROM item_4 WHERE section in ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k') AND pay = 9500
      Bitmap Heap Scan on item_4  (cost=47.68..320.83 rows=73 width=8) (actual time=0.209..0.507 rows=127 loops=1)
        Recheck Cond: ((section = ANY ('{a,b,c,d,e,f,g,h,i,j,k}'::bpchar[])) AND (pay = 9500::double precision))
        ->  Bitmap Index Scan on idx_item_4_multi  (cost=0.00..47.66 rows=73 width=0) (actual time=0.184..0.184 rows=127 loops=1)
              Index Cond: ((section = ANY ('{a,b,c,d,e,f,g,h,i,j,k}'::bpchar[])) AND (pay = 9500::double precision))
      Total runtime: 0.675 ms
  real time 2.26 ms


  result size 127 records

Jak widać przy zapytaniach równościowych index Hash (item_2) sprawuje się niemal tak dobrze jak B-Tree (item_1).

Również zgodna z oczekiwaniami jest powolność korzystania z danych bez zastosowania indeksu. W takiej sytuacji baza musi wykonać Seq Scan co niestety musi być czasochłonne. W tym przypadku zastosowanie indeksu przyśpieszyło zapytanie ponad 100 krotnie.

Zastosowanie indeksów częściowych (item_3) w tym przypadku okazało się być wolniejsze niż skorzystanie z pojedynczego indeksu. Indeksy częściowe są czasem wymieniane jako mechanizm mający być odpowiednikiem partycjonowania danych w Oracle.

PostgreSQL nie potrafi skorzystać z indeksów na wielu kolumnach w sposób odbiegający od podstawowego. Obawia się to Seq Scan (item_4).

Można jednak zmodyfikować zapytanie tak aby optymalizmowi wydawało się, że musi skorzystać z kolumny section. Wystarczy dodać warunek który jest zawsze prawdziwy, co w tym przypadku oznacza podanie wszystkich możliwych wartości section. Takie zapytanie jest prawie tak samo dobre jak zwykłe z użyciem B-Tree.

W zapytaniu 3 (item_2) EXPLAIN sugeruje, że zapytanie to wykona się wolniej od zapytania 4 (item_3). W praktyce jest odwrotnie, choć różnice nie są znaczące ani w ilości milisekund różnicy ani proporcji pomiędzy nimi. Rożnicę tą można wyjaśnić niechęcią PostgreSQL do korzystania z indeksów typu Hash. Najprawdopodobniej baza danych nie lubi również cachować tego indeksu. W praktyce objawia się to tym, że jeśli na danej kolumnie jest index zarówno B-Tree jak i Hash to postgres w zapytaniu równościowym skorzysta z B-Tree.

Zapytania nierównościowe

python pg.py explain index_greater
  SELECT pay FROM item_0 WHERE pay > 9500
      Seq Scan on item_0  (cost=0.00..20609.56 rows=52586 width=8) (actual time=0.024..346.241 rows=54611 loops=1)
        Filter: (pay > 9500::double precision)
      Total runtime: 419.762 ms
  real time 481.87 ms

  SELECT pay FROM item_1 WHERE pay > 9500
      Bitmap Heap Scan on item_1  (cost=1056.13..9873.98 rows=56228 width=8) (actual time=15.688..180.376 rows=54611 loops=1)
        Recheck Cond: (pay > 9500::double precision)
        ->  Bitmap Index Scan on idx_item_1_pay  (cost=0.00..1042.07 rows=56228 width=0) (actual time=13.559..13.559 rows=54611 loops=1)
              Index Cond: (pay > 9500::double precision)
      Total runtime: 255.899 ms
  real time 319.65 ms

  SELECT pay FROM item_2 WHERE pay > 9500
      Seq Scan on item_2  (cost=0.00..20615.00 rows=59879 width=8) (actual time=0.025..333.938 rows=54611 loops=1)
        Filter: (pay > 9500::double precision)
      Total runtime: 403.646 ms
  real time 488.11 ms

  SELECT pay FROM item_3 WHERE pay > 9500
      Seq Scan on item_3  (cost=0.00..20615.00 rows=48570 width=8) (actual time=0.030..333.590 rows=54611 loops=1)
        Filter: (pay > 9500::double precision)
      Total runtime: 402.078 ms
  real time 470.62 ms

  SELECT pay FROM item_4 WHERE pay > 9500
      Seq Scan on item_4  (cost=0.00..20615.00 rows=53272 width=8) (actual time=0.024..347.594 rows=54611 loops=1)
        Filter: (pay > 9500::double precision)
      Total runtime: 417.228 ms
  real time 509.56 ms

  result size 54611 records

Zgodnie z przewidywaniami tylko zapytanie takie zapytanie potrafi skorzystać tylko z indeksu B-Tree (item_1). To co dziwne – nie przyśpieszyło to znacząco zapytania. Możliwe, że wynika to z braku fastrygi w indeksie.

Funkcje agregujące

Wartość minimalna

python pg.py explain minimum
TEST minimum
  SELECT min(pay) FROM item_0
      Aggregate  (cost=20609.57..20609.58 rows=1 width=8) (actual time=2993.836..2993.838 rows=1 loops=1)
        ->  Seq Scan on item_0  (cost=0.00..18110.65 rows=999565 width=8) (actual time=0.018..1434.374 rows=1000000 loops=1)
      Total runtime: 2993.877 ms
  real time 424.77 ms

  SELECT pay FROM item_0 ORDER BY pay LIMIT 1
      Limit  (cost=23108.48..23108.48 rows=1 width=8) (actual time=3095.285..3095.287 rows=1 loops=1)
        ->  Sort  (cost=23108.48..25607.39 rows=999565 width=8) (actual time=3095.281..3095.281 rows=1 loops=1)
              Sort Key: pay
              Sort Method:  top-N heapsort  Memory: 17kB
              ->  Seq Scan on item_0  (cost=0.00..18110.65 rows=999565 width=8) (actual time=0.018..1555.827 rows=1000000 loops=1)
      Total runtime: 3095.317 ms
  real time 515.28 ms

  SELECT min(pay) FROM item_1
      Result  (cost=0.06..0.07 rows=1 width=0) (actual time=0.024..0.026 rows=1 loops=1)
        InitPlan
          ->  Limit  (cost=0.00..0.06 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=1)
                ->  Index Scan using idx_item_1_pay on item_1  (cost=0.00..58431.91 rows=1000000 width=8) (actual time=0.015..0.015 rows=1 loops=1)
                      Filter: (pay IS NOT NULL)
      Total runtime: 0.049 ms
  real time 0.48 ms

  SELECT pay FROM item_1 ORDER BY pay LIMIT 1
      Limit  (cost=0.00..0.06 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=1)
        ->  Index Scan using idx_item_1_pay on item_1  (cost=0.00..58431.91 rows=1000000 width=8) (actual time=0.015..0.015 rows=1 loops=1)
      Total runtime: 0.038 ms
  real time 0.34 ms

  result size 1 records

Nie jest żadnym zaskoczeniem, że w zapytaniu o wartość minimalną kluczową rolę gra indeks. Zapytania korzystające z niego korzystające jest 6000 razy szybsze.

Warto tu zwrócić uwagę, że PostgreSQL w drugim zapytaniu użył metody sortowania top-N heapsort, która świetnie pracuje w zapytaniach ORDER BY ... LIMIT ....

Policzenie wszystkich elementów

python pg.py explain count
  SELECT count(*) FROM item_0
      Aggregate  (cost=20609.57..20609.58 rows=1 width=0) (actual time=2766.064..2766.066 rows=1 loops=1)
        ->  Seq Scan on item_0  (cost=0.00..18110.65 rows=999565 width=0) (actual time=0.026..1425.761 rows=1000000 loops=1)
      Total runtime: 2766.111 ms
  real time 251.70 ms

  SELECT count(*) FROM item_1
      Aggregate  (cost=20615.00..20615.01 rows=1 width=0) (actual time=2769.023..2769.025 rows=1 loops=1)
        ->  Seq Scan on item_1  (cost=0.00..18115.00 rows=1000000 width=0) (actual time=0.111..1400.902 rows=1000000 loops=1)
      Total runtime: 2769.070 ms
  real time 218.34 ms

  SELECT count(pay) FROM item_0
      Aggregate  (cost=20609.57..20609.58 rows=1 width=8) (actual time=2882.692..2882.693 rows=1 loops=1)
        ->  Seq Scan on item_0  (cost=0.00..18110.65 rows=999565 width=8) (actual time=0.025..1447.978 rows=1000000 loops=1)
      Total runtime: 2882.732 ms
  real time 295.68 ms

  SELECT count(pay) FROM item_1
      Aggregate  (cost=20615.00..20615.01 rows=1 width=8) (actual time=3183.981..3183.983 rows=1 loops=1)
        ->  Seq Scan on item_1  (cost=0.00..18115.00 rows=1000000 width=8) (actual time=0.026..1592.733 rows=1000000 loops=1)
      Total runtime: 3184.022 ms
  real time 294.60 ms

  result size 1 records

Niestety PostgreSQL nie potrafi sobie dobrze poradzić z ustaleniem ilości elementów w tabeli. Ta operacja mogła by być wykonywana w czasie stałym a tu jej wykonanie wymaga Seq Scan. Warto zwrócić uwagę, że baza przechowuje oszacowania tych danych (rows=999565 oraz rows=1000000).

Zapytania o unikalne elementy

Wszystkie unikalne elementy

python pg.py explain distinct
  SELECT DISTINCT pay FROM item_1
      Unique  (cost=0.00..60931.91 rows=8778 width=8) (actual time=0.061..9573.897 rows=9201 loops=1)
        ->  Index Scan using idx_item_1_pay on item_1  (cost=0.00..58431.91 rows=1000000 width=8) (actual time=0.058..8092.649 rows=1000000 loops=1)
      Total runtime: 9588.750 ms
  real time 7175.85 ms

  SELECT pay FROM item_1 GROUP BY pay
      HashAggregate  (cost=20615.00..20702.78 rows=8778 width=8) (actual time=3150.550..3163.936 rows=9201 loops=1)
        ->  Seq Scan on item_1  (cost=0.00..18115.00 rows=1000000 width=8) (actual time=0.037..1447.830 rows=1000000 loops=1)
      Total runtime: 3175.569 ms
  real time 592.40 ms

  result size 9201 records

Pierwsze rozwiązanie cechuje się prostotą, czytelnością oraz tym, że łatwo je wymyślić. Niestety pomimo zastosowania indeksu nie jest wstanie wygrać z haszowaniem. Okazuje się być 12 krotnie wolniejsze.

Wybrane unikalne elementy

python pg.py explain distinct_where
  SELECT DISTINCT pay FROM item_1 WHERE pay   Index Scan using idx_item_1_pay on item_1  (cost=0.00..39314.56 rows=240959 width=8) (actual time=0.071..1965.921 rows=239234 loops=1)
              Index Cond: (pay < 3000::double precision)
      Total runtime: 2323.769 ms
  real time 1741.87 ms

  SELECT pay FROM item_1 WHERE pay   Bitmap Heap Scan on item_1  (cost=4515.79..15642.78 rows=240959 width=8) (actual time=58.506..467.832 rows=239234 loops=1)
              Recheck Cond: (pay   Bitmap Index Scan on idx_item_1_pay  (cost=0.00..4455.55 rows=240959 width=0) (actual time=56.219..56.219 rows=239234 loops=1)
                    Index Cond: (pay < 3000::double precision)
      Total runtime: 880.148 ms
  real time 277.76 ms

  SELECT pay FROM item_1 GROUP BY pay HAVING pay   Bitmap Heap Scan on item_1  (cost=4515.79..15642.78 rows=240959 width=8) (actual time=58.250..467.493 rows=239234 loops=1)
              Recheck Cond: (pay   Bitmap Index Scan on idx_item_1_pay  (cost=0.00..4455.55 rows=240959 width=0) (actual time=55.926..55.926 rows=239234 loops=1)
                    Index Cond: (pay < 3000::double precision)
      Total runtime: 888.473 ms
  real time 283.04 ms

  result size 2200 records

Jest to lekko zmodyfikowany poprzedni przypadek, różniący się tylko warunkiem. W tym przypadku grupowanie nie ma już tak dużej przewagi bo tylko(?) 6 krotną.

Warto zwrócić uwagę, że w tym przypadku często powtarzana zasada „stosuj WHERE tam gdzie się da a HAVING dopiero jeśli musisz” tu się nie sprawdza. Po prostu PostgreSQL przepisał te zapytania na takie same plany.

Zliczanie unikalnych elementów

python pg.py explain count_distinct
TEST count_distinct
  SELECT count(DISTINCT pay) AS a FROM item_1
      Aggregate  (cost=20615.00..20615.01 rows=1 width=8) (actual time=5789.449..5789.451 rows=1 loops=1)
        ->  Seq Scan on item_1  (cost=0.00..18115.00 rows=1000000 width=8) (actual time=0.022..1433.576 rows=1000000 loops=1)
      Total runtime: 5789.501 ms
  real time 3190.76 ms

  SELECT count(*) from (SELECT 1 FROM item_1 GROUP BY pay) as job;
      Aggregate  (cost=20812.51..20812.52 rows=1 width=0) (actual time=3190.341..3190.342 rows=1 loops=1)
        ->  HashAggregate  (cost=20615.00..20702.78 rows=8778 width=8) (actual time=3164.037..3178.169 rows=9201 loops=1)
              ->  Seq Scan on item_1  (cost=0.00..18115.00 rows=1000000 width=8) (actual time=0.025..1420.910 rows=1000000 loops=1)
      Total runtime: 3190.464 ms
  real time 590.71 ms

  result size 1 records

Również w tym przypadku GROUP BY okazuje się być o wiele szybsze od DISTINCT.

Zapytania o prefiksy napisów

W bazach danych często zachodzi potrzeba zapytania o napisy zawierające dany podciąg. O ile wyszukiwanie pełnotekstowe jest zagadnieniem, któremu warto poświecić książkę to zapytanie o napisy zaczynajace się na dany prefix są relatywnie prostym ale równie przydatnym zagadnieniem.

Bez przygotowanej do tego bazy danych

W tych zapytaniach zostanie użyta tabela prostym indeksem B-Tree założonym na jej kolumnę „name”.

python pg.py explain string_start
  SELECT pay FROM item_1 WHERE name LIKE 'a%'
      Seq Scan on item_1  (cost=0.00..20615.00 rows=62340 width=8) (actual time=0.058..347.848 rows=18502 loops=1)
        Filter: ((name)::text ~~ 'a%'::text)
      Total runtime: 371.993 ms
  real time 414.74 ms

  SELECT pay FROM item_1 WHERE name ~ '^a.*'
      Seq Scan on item_1  (cost=0.00..20615.00 rows=62340 width=8) (actual time=0.161..1011.445 rows=18502 loops=1)
        Filter: ((name)::text ~ '^a.*'::text)
      Total runtime: 1035.623 ms
  real time 1090.11 ms

  SELECT pay FROM item_1 WHERE substr(name, 0, 1) = 'a'
      Seq Scan on item_1  (cost=0.00..23115.00 rows=5000 width=8) (actual time=682.164..682.164 rows=0 loops=1)
        Filter: (substr((name)::text, 0, 1) = 'a'::text)
      Total runtime: 682.194 ms
  real time 694.45 ms

  SELECT pay FROM item_1 WHERE 'a' <= name AND name <= 'b'
      Bitmap Heap Scan on item_1  (cost=1879.43..10929.53 rows=62340 width=8) (actual time=32.185..165.301 rows=37046 loops=1)
        Recheck Cond: (('a'::text <= (name)::text) AND ((name)::text <= 'b'::text))
        ->  Bitmap Index Scan on idx_item_1_name  (cost=0.00..1863.85 rows=62340 width=0) (actual time=29.822..29.822 rows=37046 loops=1)
              Index Cond: (('a'::text <= (name)::text) AND ((name)::text <= 'b'::text))
      Total runtime: 212.977 ms
  real time 257.41 ms

  result size 18502 records

Jak widać najszybszym rozwiązaniem jest rozwiązanie korzystające z operatorów mniejszości. Wynika to głównie z tego, że jako jedyne zostało przepisane tak aby skorzystać z indeksu.

Operator LIKE okazuje się być całkiem wydajny. Jest trzykrotnie szybszy od wyrażeń regularnych oraz dwukrotnie szybszy od zapytania porównującego podnapis.

python pg.py explain string_start_opt
  SELECT pay FROM item_2 WHERE name LIKE 'a%'
      Index Scan using idx_item_2_name on item_2  (cost=0.00..8.47 rows=47825 width=8) (actual time=0.067..163.999 rows=18502 loops=1)
        Index Cond: (((name)::text ~>=~ 'a'::text) AND ((name)::text ~<~ 'b'::text))
        Filter: ((name)::text ~~ 'a%'::text)
      Total runtime: 188.630 ms
  real time 237.19 ms

  SELECT pay FROM item_2 WHERE name ~ '^a.*'
      Index Scan using idx_item_2_name on item_2  (cost=0.00..8.47 rows=47825 width=8) (actual time=0.074..185.303 rows=18502 loops=1)
        Index Cond: (((name)::text ~>=~ 'a'::text) AND ((name)::text ~<~ 'b'::text))
        Filter: ((name)::text ~ '^a.*'::text)
      Total runtime: 209.987 ms
  real time 259.40 ms

  SELECT pay FROM item_3 WHERE substr(name, 0, 1) = 'a'
      Seq Scan on item_3  (cost=0.00..23115.00 rows=5000 width=8) (actual time=699.043..699.043 rows=0 loops=1)
        Filter: (substr((name)::text, 0, 1) = 'a'::text)
      Total runtime: 699.074 ms
  real time 707.79 ms

  result size 18502 records

Tu został dodany index CREATE INDEX idx_item_2_name ON item_2 (name varchar_pattern_ops). Zastosowanie varchar_pattern_ops umożliwia korzystanie z specjalnego operatora umożliwiającego porównywanie bajt po bajcie. Umożliwia on dobre wykorzystanie indeksów w LIKE/wyrażeniach regularnych.

Doskonale to widać w tym przykładzie. Dwa pierwsze zapytania wykonały się szybciej niż najszybsze z poprzedniego zestawu.

Natomiast indeks na wartości (CREATE INDEX idx_item_3_name ON item_3 (substring(name, 0, 1))) nie zdał egzaminu i został totalnie zignorowany.

Losowy element

python pg.py explain random
TEST random
  SELECT id FROM item_1 ORDER BY random() LIMIT 1
      Limit  (cost=25615.00..25615.00 rows=1 width=4) (actual time=3210.675..3210.677 rows=1 loops=1)
        ->  Sort  (cost=25615.00..28115.00 rows=1000000 width=4) (actual time=3210.672..3210.672 rows=1 loops=1)
              Sort Key: (random())
              Sort Method:  top-N heapsort  Memory: 17kB
              ->  Seq Scan on item_1  (cost=0.00..20615.00 rows=1000000 width=4) (actual time=0.040..1665.266 rows=1000000 loops=1)
      Total runtime: 3210.711 ms
  real time 658.06 ms

  SELECT id FROM item_1 LIMIT 1 OFFSET floor(random() * (1000000 - 1))
      Limit  (cost=1811.50..1811.52 rows=1 width=4) (actual time=1346.399..1346.401 rows=1 loops=1)
        ->  Seq Scan on item_1  (cost=0.00..18115.00 rows=1000000 width=4) (actual time=0.007..733.375 rows=489524 loops=1)
      Total runtime: 1346.435 ms
  real time 88.49 ms


  SELECT id FROM item_1 WHERE id >= floor(random() * (select max(id) from item_1)) LIMIT 1
      Limit  (cost=0.04..0.14 rows=1 width=4) (actual time=0.040..0.041 rows=1 loops=1)
        InitPlan
          ->  Result  (cost=0.03..0.04 rows=1 width=0) (actual time=0.021..0.022 rows=1 loops=1)
                InitPlan
                  ->  Limit  (cost=0.00..0.03 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)
                        ->  Index Scan Backward using item_1_pkey on item_1  (cost=0.00..31886.34 rows=1000000 width=4) (actual time=0.012..0.012 rows=1 loops=1)
                              Filter: (id IS NOT NULL)
        ->  Seq Scan on item_1  (cost=0.00..33115.00 rows=333333 width=4) (actual time=0.037..0.037 rows=1 loops=1)
              Filter: ((id)::double precision >= floor((random() * ($1)::double precision)))
      Total runtime: 0.076 ms
  real time 1.54 ms

  result size 1 records

Zapytania o losowy element bywają potrzebne częściej niż się wydaje.

Metoda pierwsza jest najbardziej standardowa i ma wiele zalet:

• prawdziwa losowość
• możliwość pobrania za jednym zamachem nie jednego ale wielu elementów (nie powtarzających się)
• przewidywalny czas wykonania

Niestety jest powolna i nie da się temu zaradzić.

Metoda druga ma jedną poważną wadę – do OFFSET nie da się wstawić podzapytania – więc w praktyce aby z niej korzystać trzeba się posłużyć PL/pgSQL lub inną finezyjną metodą.

Metoda trzecia potrafi być bardzo szybka ale ma 2 wady:

• Jej czas wykonania trudno przewidzieć – czasami jest kilkudziesięciokrotnie wolniejsza (niestety w głównym (zewnętrznym) zapytaniu jest wykonywany Seq Scan z losowym limitem)
• Nie zwraca w pełni losowych wyników. Jeśli odstępy pomiędzy id rekordów są nierównomierne to rekordy które są „gęsto” mają mniejszą szansę na wylosowanie

Ciekawy opis jak też jeszcze jedno rozwiązanie można znaleźć tu.

Podzapytania

python pg.py explain subselect
  SELECT pay FROM item_1 WHERE pay BETWEEN 4000 AND 5000
      Bitmap Heap Scan on item_1  (cost=2317.48..12067.28 rows=108987 width=8) (actual time=34.468..276.750 rows=108995 loops=1)
        Recheck Cond: ((pay >= 4000::double precision) AND (pay <= 5000::double precision))
        ->  Bitmap Index Scan on idx_item_1_pay  (cost=0.00..2290.23 rows=108987 width=0) (actual time=32.190..32.190 rows=108995 loops=1)
              Index Cond: ((pay >= 4000::double precision) AND (pay <= 5000::double precision))
      Total runtime: 419.148 ms
  real time 553.88 ms

  SELECT pay FROM (SELECT pay FROM item_1 WHERE 4000 <= pay) AS job WHERE pay <= 5000;
      Bitmap Heap Scan on item_1  (cost=2317.48..12067.28 rows=108987 width=8) (actual time=34.796..275.877 rows=108995 loops=1)
        Recheck Cond: ((4000::double precision <= pay) AND (pay <= 5000::double precision))
        ->  Bitmap Index Scan on idx_item_1_pay  (cost=0.00..2290.23 rows=108987 width=0) (actual time=32.491..32.491 rows=108995 loops=1)
              Index Cond: ((4000::double precision <= pay) AND (pay <= 5000::double precision))
      Total runtime: 420.154 ms
  real time 573.61 ms

  result size 108995 records

Jak widać na tym prostym zapytaniu PostgreSQL potrafi rozwijać zagnieżdżone zapytania w FROM.