moje wprowadzenie do baz danych i PostgreSQL dotyczyło tworzenia aplikacji internetowych i analizy statystycznej. Nauczyłem się wystarczająco dużo SQL, aby uzyskać zapytania, aby zwrócić właściwe odpowiedzi. Dzięki pracy z PostGIS (i FOSS4G) zaprzyjaźniłem się z Paulem Ramseyem. Jesteśmy teraz współpracownikami w Crunchy Data i pomaga mi się mój SQL-fu. Jedną z pierwszych lekcji, których mnie nauczył, było ” spróbuj użyć łączy, a nie zapytań podrzędnych.”

Dzisiejszy post będzie działał przez tę radę, ponieważ Paul i ja pracujemy przez jakiś SQL.

na co staramy się odpowiedzieć

próbuję stworzyć nowe materiały dydaktyczne i mówiące na temat korzystania z SQL w naukach o danych i pracowałem nad niektórymi manipulacjami danymi przed analizą. Miałem tabelę fire_weather, która jest podzbiorem tabeli pogody i chcę znaleźć wszystkie wpisy w weather, które nie są w fire_weather. Mój początkowy instynkt polegał na napisaniu zapytania podrzędnego, ale wydawało się to prostym i łatwym zapytaniem do naśladowania porady Paula „użyj połączenia”.

błędne zapytanie Join

zacząłem od tego zapytania join:

 select count(weather.*) from weather, fire_weather where weather.id != fire_weather.id;

i nie zadziałało (inaczej nie pisałbym tego posta na blogu). Okazuje się, że jest to połączenie krzyżowe, w którym kończymy ze wszystkimi parami kombinacji wszystkich wierszy w obu tabelach.

właściwe zapytanie Join

więc w tym momencie luzuję (w przeciwieństwie do dzwonienia przez telefon) Paul i zaczynamy dyskutować, jak zrobić właściwe połączenie. Okazuje się, że właściwa składnia jest:

select count(weather.*)from weather left join fire_weather on weather.id = fire_weather.idwhere fire_weather.id is null;

zasadniczo robisz lewy zewnętrzny join, dając Ci wszystkie wiersze z tabeli pogody i tylko wpisy fire_weather, które pasują. Następnie odfiltrowujesz wszystkie rekordy, w których są dopasowania dla fire_weather. Dość prosty do zrozumienia, ale niezbyt ustawiony, jak w przypadku stosowania teorii mnogości (która jest podstawą relacji w relacyjnych systemach bazodanowych). Z drugiej strony, mamy teraz działające zapytanie join.

Analyze This

w ramach mojej podróży do lepszego zrozumienia SQL w PostgreSQL, stałem się wielkim fanem EXPLAIN ANALYZE for for timings i patrzenia na plan zapytań. Tak z ciekawości postanawiam przyjrzeć się harmonogramowi i planowi zapytań dla zapytania join. Oto wyjście i zajęło około 7 milisekund z nieco skomplikowanym planem zapytań:

Aggregate (cost=1358.58..1358.59 rows=1 width=8) (actual time=6.780..6.781 rows=1 loops=1)
-> Hash Anti Join (cost=1168.76..1358.58 rows=1 width=64) (actual time=1.648..6.218 rows=6802 loops=1)
Hash Cond: (weather.id = fire_weather.id)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=68) (actual time=0.008..3.157 rows=8205 loops=1)
-> Hash (cost=785.56..785.56 rows=30656 width=4) (actual time=1.609..1.609 rows=1403 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 306kB
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..1.399 rows=1403 loops=1)
Planning Time: 0.110 ms
Execution Time: 6.807 ms

teraz Subquery

i teraz chciałem zobaczyć, jak będzie działał mój oryginalny pomysł na subquery. Oto sposób na odpowiedź na to samo pytanie:

select count(weather.*) from weather where id not in (select id from fire_weather);

więcej analizy

powinieneś zobaczyć, dlaczego to zapytanie do mnie przemawia, jest bardzo oparte na ustawieniach i bardzo proste do napisania. Kiedy patrzę na to zapytanie z explain analyze dostaję:

Aggregate (cost=1052.02..1052.03 rows=1 width=8) (actual time=7.458..7.459 rows=1 loops=1)
-> Seq Scan on weather (cost=862.20..1041.76 rows=4102 width=64) (actual time=1.139..6.727 rows=6802 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 1403
SubPlan 1
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.004..0.643 rows=1403 loops=1)
Planning Time: 0.068 ms
Execution Time: 7.497 ms

porównanie zapytań

więc kończymy z bardzo prostym planem i czasem, które są mniej więcej takie same jak join. Paul i ja dyskutowaliśmy, dlaczego terminy mogą być tak podobne i wymyśliliśmy co najmniej dwa powody:

  1. zbiór danych ma bardzo niewiele wierszy (8k), więc wydajność podquery może ulec pogorszeniu przy większym zbiorze danych.
  2. moja maszyna ma dyski NVMe, które zapewniają dostęp sekwencyjny i jeszcze większą różnicę w wydajności.

kolejne zapytanie bazujące na zestawach

w końcu Paul wymyślił jeszcze jedno zapytanie bazujące na zestawach, aby odpowiedzieć na to samo pytanie:

select * from weather exceptselect weather.* from weather join fire_weather on weather.id = fire_weather.id

ten używa nowej klauzuli SQL, z wyjątkiem, która jest częścią zestawów zapytań operacyjnych. Dużym ograniczeniem dla tych zapytań jest to, że zapytania po każdej stronie klauzuli except muszą zwracać te same kolumny i typy danych. To wyjaśnia, dlaczego to zapytanie nie może zwrócić całkowitej liczby wierszy. Ale to zapytanie okazało się gorsze w wydajności i znacznie bardziej skomplikowany plan zapytań:

HashSetOp Except (cost=0.00..1984.13 rows=8205 width=74) (actual time=10.382..11.171 rows=6802 loops=1)
-> Append (cost=0.00..1532.86 rows=16410 width=74) (actual time=0.021..5.867 rows=9608 loops=1)
" -> Subquery Scan on ""*SELECT* 1"" (cost=0.00..241.10 rows=8205 width=44) (actual time=0.021..2.300 rows=8205 loops=1)"
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=40) (actual time=0.006..0.537 rows=8205 loops=1)
" -> Subquery Scan on ""*SELECT* 2"" (cost=261.61..1209.71 rows=8205 width=44) (actual time=1.796..2.788 rows=1403 loops=1)"
-> Hash Join (cost=261.61..1127.66 rows=8205 width=40) (actual time=1.795..2.583 rows=1403 loops=1)
Hash Cond: (fire_weather.id = weather_1.id)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..0.413 rows=1403 loops=1)
-> Hash (cost=159.05..159.05 rows=8205 width=40) (actual time=1.768..1.768 rows=8205 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 765kB
-> Seq Scan on weather weather_1 (cost=0.00..159.05 rows=8205 width=40) (actual time=0.002..0.518 rows=8205 loops=1)
Planning Time: 0.127 ms
Execution Time: 11.975 ms

zaskakujący zwrot akcji

potem pomyślałem o zapytaniu, które zasugerował Paul i zdałem sobie sprawę, że tak naprawdę nie potrzebujemy połączenia po prawej stronie klauzuli except. Ponieważ fire_weather zawiera wszystkie te same kolumny co weather, możemy po prostu użyć kolumn, które chcemy i uzyskać oczekiwaną odpowiedź.

select id from weather exceptselect id from fire_weather;

teraz ma ładną składnię zestawu, dzięki czemu jest naprawdę łatwy do zrozumienia. Jeśli chcemy uzyskać liczbę jak w innych zapytaniach, możemy zawinąć nasze zapytanie w CTE.

with count_me as (select id from weather exceptselect id from fire_weather)select count(*) from count_me;

z tym złotym biletem otrzymujemy 6 ms zapytań i plany zapytań, które są czystsze, ale nie najprostsze. Powinienem zauważyć, że czystość i prostota nie są kluczowymi czynnikami w ocenie planu zapytań.

HashSetOp Except (cost=0.00..1624.68 rows=8205 width=8) (actual time=4.834..5.313 rows=6802 loops=1)
-> Append (cost=0.00..1527.52 rows=38861 width=8) (actual time=0.004..3.248 rows=9608 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..241.10 rows=8205 width=8) (actual time=0.004..1.903 rows=8205 loops=1)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=4) (actual time=0.003..1.001 rows=8205 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..1092.12 rows=30656 width=8) (actual time=0.003..0.554 rows=1403 loops=1)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.002..0.409 rows=1403 loops=1)
Planning Time: 0.246 ms
Execution Time: 5.897 ms

moje ostatnie lekcje

oto Ostatnie lekcje, które chciałbym wam zostawić z tego małego ćwiczenia.

  1. nigdy nie pytaj o oko-to były wszystkie te same prędkości dla mojego oka. Explain analyze to twój przyjaciel.
  2. napisz zapytanie w sposób, który ma największy sens, a następnie wykonaj timingi. Jeśli nie jest dobrze, to poszukaj alternatywy (prawdopodobnie)
  3. istnieje wiele sposobów na uzyskanie tej samej odpowiedzi w SQL – „właściwa” odpowiedź będzie wysoce zależna od sytuacji. Kilka rzeczy, które będą miały wpływ na wynik:
    1. Rozmiar danych – zapytanie może przestać być „ok”, gdy Rozmiar danych rośnie
    2. indeksy
    3. prędkość dysku
    4. rozmiar pamięci
    5. prędkość procesora
    6. jak często planujesz wykonywać zapytanie
  4. wreszcie czas poświęcony na doskonalenie wiedzy i umiejętności SQL sowicie się opłaci. Nawet jeśli nie piszesz najbardziej wydajnych zapytań, są one zwykle szybsze niż pisanie wielu kodów proceduralnych. Gdy uczę się coraz więcej wzorców SQL, tym bardziej jestem zaskoczony kodem, który mogę zastąpić kilkoma linijkami SQL (i zwykle uzyskuję ogromny wzrost wydajności).

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.