Przykładowy wynik działa instrukcji EXPLAIN:
postgres@test(127.0.0.1) # explain select * from event e left join event_2 ee on e.c4 = ee.c1 where (e.c4 < 16 AND e.c5 = 90) and ee.c2 > 10;
QUERY PLAN
-----------------------------------------------------------------------------------------
Hash Join (cost=6809.87..12892.11 rows=1661 width=56)
Hash Cond: (e.c4 = ee.c1)
-> Bitmap Heap Scan on event e (cost=6806.56..12864.79 rows=1974 width=40)
Recheck Cond: ((c5 = 90) AND (c4 < 16))
-> BitmapAnd (cost=6806.56..6806.56 rows=1974 width=0)
-> Bitmap Index Scan on idx_c5 (cost=0.00..177.89 rows=12473 width=0)
Index Cond: (c5 = 90)
-> Bitmap Index Scan on idx_c4 (cost=0.00..6627.44 rows=211049 width=0)
Index Cond: (c4 < 16)
-> Hash (cost=2.25..2.25 rows=85 width=16)
-> Seq Scan on event_2 ee (cost=0.00..2.25 rows=85 width=16)
Filter: (c2 > 10)
(12 rows)
lub graficzny z pgAdmin III:
Jeśli będziemy wiedzieć jak odczytywać wynik działania polecenia EXPLAIN, będziemy też wiedzieć:
- jaka metoda skanowania została użyta (Scan Methods)
- Sequential Scan
- Index Scan
- Bitmap Scan
- jaka metoda łączenia została użyta (Join Methods)
- Nested Loop
- With Inner Sequential Scan
- With Inner Index Scan
- Hash Join
- Merge Join
- jaki kierunek łączenia został zastosowany (Join Order)
- jaki index został użyty
- jaki filtry danych zostały zastosowane
- oraz informacje, które zostały omówione we wprowadzeniu (koszty, użycie bufora, czas trwania i inne (które można zaleźć pod postem: EXPLAIN plan w PostgreSQL v9.1 - Wprowadzenie))
Metody skanowania
Sequential Scan
Ten sposób jest wybierany głównie gdy:
- na kolumnie na której został nałożony warunek, nie mamy index-u
- optymalizator zdecydował, że nasz warunek dotyczy większości danych w tabeli
postgres@test(127.0.0.1) # explain (ANALYZE, buffers true) select c5 from event offset 10 limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..0.58 rows=10 width=4) (actual time=0.044..0.066 rows=10 loops=1)
Buffers: shared read=1
-> Seq Scan on event (cost=0.00..38333.33 rows=1333333 width=4) (actual time=0.020..0.040 rows=20 loops=1)
Buffers: shared read=1
Total runtime: 0.097 ms
(5 rows)
Index Scan
Skanuje index, a następnie wybiera dane z tabeli aby je zwrócić.
Koło informacji, że index jest skanowany widzimy, który index został użyty i na jakiej tabeli:
Index Scan using idx_c5 on event
Przykład:
postgres@test(127.0.0.1) # explain select c5 from event order by c5 offset 10 limit 10;
QUERY PLAN
--------------------------------------------------------------------------------------
Limit (cost=16.31..32.62 rows=10 width=4)
-> Index Scan using idx_c5 on event (cost=0.00..2174766.57 rows=1333333 width=4)
(2 rows)
Bitmap Scan
Tym razem chcemy wybrać dane, gdzie warunek jest spełniony przez dwa index-y.
Optymalizator wykonuje następujące kroki:
- Buduje bitmapę wierszy, które to spełniają pierwszy warunek, używa do tego index-u, który spełnia ten warunek (-> Bitmap Index Scan on idx_c5)
- Buduje bitmapę wierszy, które to spełniają drugi warunek, także, używa do tego index-u, który spełnia ten warunek (-> Bitmap Index Scan on idx_c4)
- Łączy powstałe w 1 i 2 krokach bitmap-y przy pomocy operatora użytego w warunkach zapytania (BitmapOr dla operatora OR, BitmapAnd dla operatora AND) -> BitmapOr
- Przegląda tabelę docelową z danymi (-> Bitmap Heap Scan on event) i ponownie sprawdza czy spełniony został nasz warunek (-> Recheck Cond: ((c4 = 16) OR (c5 = 30)))
postgres@test(127.0.0.1) # explain select * from event where (c4 = 16 OR c5 = 30);
QUERY PLAN
---------------------------------------------------------------------------------
Bitmap Heap Scan on event (cost=631.46..27250.57 rows=28181 width=40)
Recheck Cond: ((c4 = 16) OR (c5 = 30))
-> BitmapOr (cost=631.46..631.46 rows=28328 width=0)
-> Bitmap Index Scan on idx_c4 (cost=0.00..389.03 rows=12328 width=0)
Index Cond: (c4 = 16)
-> Bitmap Index Scan on idx_c5 (cost=0.00..228.34 rows=16000 width=0)
Index Cond: (c5 = 30)
(7 rows)
Metody łączenia
Każdy sposób łączenia przyjmuje dwa argumenty, jakimi są: lewa i prawa relację.
Nested Loop With Inner Sequential Scan
Dla bardzo dużych tabel, ten sposób łączenia może być bardzo kosztowny gdyż w takim przypadku optymalizator: dla każdego elementu relacji lewej, sprawdza każdy wiersz relacji prawej. Jeśli warunek łączenia jest spełniony, zwraca wiersz jako wynik łączenia. W przeciwnym przypadku, wiersz jest pomijany.
Przykład:
postgres@test(127.0.0.1) # explain select * from (select * from event limit 10) as foo, event_2 limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------
Limit (cost=0.00..0.15 rows=10 width=56)
-> Nested Loop (cost=0.00..14.91 rows=1000 width=56)
-> Seq Scan on event_2 (cost=0.00..2.00 rows=100 width=16)
-> Materialize (cost=0.00..0.44 rows=10 width=40)
-> Limit (cost=0.00..0.29 rows=10 width=40)
-> Seq Scan on event (cost=0.00..38333.33 rows=1333333 width=40)
(6 rows)
Nested Loop With Inner Index Scan
Tym razem jednak do wybierana danych z tabeli prawej, używany jest index.
Przykład:
postgres@test(127.0.0.1) # explain select * from event where c1 IN (select c1 from (select count(*) as c, min(c1) as c1, c4 from event where c3 > '2014-01-01' group by c4 having count(*) > 1500) as foo);
QUERY PLAN
---------------------------------------------------------------------------------------
Nested Loop (cost=43606.41..43734.71 rows=15 width=40)
-> HashAggregate (cost=43606.41..43606.56 rows=15 width=4)
-> HashAggregate (cost=43606.03..43606.22 rows=15 width=8)
Filter: (count(*) > 1500)
-> Seq Scan on event (cost=0.00..41666.66 rows=193937 width=8)
Filter: (c3 > '2014-01-01 00:00:00'::timestamp without time zone)
-> Index Scan using event_pkey on event (cost=0.00..8.53 rows=1 width=40)
Index Cond: (c1 = (min(public.event.c1)))
(8 rows)
Hash Join
Prawa relacja jest skanowana i ładowana do tabeli hash-y w taki sposób, że atrybuty łączenia są kluczami hash-y. Następnie lewa relacja jest skanowana i odpowiednie wartości są znalezione poprzez klucze hash-y (relacje prawą).
Algorytm jest bardzo szybki ale wymaga wystarczająco dużo pamięci, aby pomieścić prawą relację.Nie gwarantuje także, że sortowanie wejściowe będzie zachowane w wyniku łączenia.
Przykład:
postgres@test(127.0.0.1) # explain select * from t3 left join t1 on t1.c1 = t3.c1;
QUERY PLAN
-------------------------------------------------------------------
Hash Left Join (cost=35.43..96.25 rows=2140 width=50)
Hash Cond: (t3.c1 = t1.c1)
-> Seq Scan on t3 (cost=0.00..31.40 rows=2140 width=8)
-> Hash (cost=21.30..21.30 rows=1130 width=42)
-> Seq Scan on t1 (cost=0.00..21.30 rows=1130 width=42)
(5 rows)
Merge Join
Zanim dojdzie do łączenia, relacje (prawa i lewa) są sortowane.
Następnie skanuje równolegle dopasowując równe wartości.
Zazwyczaj algorytm skanuje wiersz tylko raz, ale pod warunkiem, że w relacji lewej nie ma duplikatów.
Przykład:
postgres@test(127.0.0.1) # explain select * from (select * from event order by c2 desc) e1 join (select * from event order by c4 desc) e2 on e1.c5 = e2.c4;
QUERY PLAN
----------------------------------------------------------------------------------------
Merge Join (cost=828400.83..134185000.82 rows=8888884444 width=80)
Merge Cond: (public.event.c4 = public.event.c5)
-> Sort (cost=283355.83..286689.17 rows=1333333 width=40)
Sort Key: public.event.c4
-> Seq Scan on event (cost=0.00..38333.33 rows=1333333 width=40)
-> Materialize (cost=545045.00..551711.67 rows=1333333 width=40)
-> Sort (cost=545045.00..548378.33 rows=1333333 width=40)
Sort Key: public.event.c5
-> Sort (cost=283355.83..286689.17 rows=1333333 width=40)
Sort Key: public.event.c2
-> Seq Scan on event (cost=0.00..38333.33 rows=1333333 width=40)
(11 rows)
To jaką decyzje podejmie optymalizator, który algorytm użyć, jak wybrać dane, jest uzależnione od statystyk. Dlatego ważne jest aby było one zawsze aktualne.
Ale o samych statystykach może kiedy indziej.
Miłego czytania!
Brak komentarzy:
Prześlij komentarz