PostgreSQL – Blog Dandysa

Trigger ON UPDATE i ON DELETE na spartycjonowanej tabeli w PostgreSQL

Kolejna ciekawostka, na którą się naciąłem ostatnio w pracy. Otóż w przypadku spartycjonowanej tabeli, triggery ON UPDATE i ON DELETE są wykonywane jedynie na tabeli potomnej zawierającej dane wiersza określonego przez warunki w WHERE (o ile oczywiście istnieją).

Oznacza to, że w przypadku partycjonowania tabel na podstawie np. wartości klucza głównego poprzez ograniczenie CHECK, planner najpierw spróbuje wyznaczyć partycje zawierające wiersz(e) spełniające warunki zapytania, a następnie wywoła triggery ON UPDATE/ON DELETE jedynie na tabelach zawierających dane do usunięcia/aktualizacji.

Jest to zachowanie inne niż w przypadku ON INSERT, gdzie trigger wywoływany jest bezpośrednio na tabeli, na której wywołano instrukcje INSERT mimo, że sam trigger ON INSERT na niej może delegować operacje wstawienia do innych tabel potomnych.

NOW() przy zapytaniach na spartycjonowanych tabelach

UWAGA! Ten wpis ma już 5 lat. Pewne stwierdzenia i poglądy w nim zawarte mogą być nieaktualne.

Ostatnio dowiedziałem się, że funkcje NOW() oraz CURRENT_TIMESTAMP w PostgreSQL nie są immutable, co powoduje, że wyrażenia z tymi funkcjami nie pomogą w optymalizacji zapytań na spartycjonowanych tabelach, których klauzule CHECK dotyczą przedziałów czasowych. Wartości tych funkcji nie są znane planerowi w momencie wykonywania zapytania:

Constraint exclusion only works when the query’s WHERE clause contains constants (or externally supplied parameters). For example, a comparison against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, since the planner cannot know which partition the function value might fall into at run time.
https://www.postgresql.org/docs/10/ddl-partitioning.html

Aby zapytanie korzystało jedynie z właściwych partycji, należy więc przekazywać daty/przedziały czasowe jako stałe (np. pobierając je z poziomu języka programowania aplikacji korzystającej z bazy danych) lub z innych tabel. W niektórych rozwiązaniach można po prostu stworzyć tabelę z jednym wpisem i kolumną typu date, której wartość będzie codziennie aktualizowana (nadal jednak utracimy możliwość wyszukiwania uwzględniającego godzinę).

Kolejność w zapytaniu z DISTINCT ON

UWAGA! Ten wpis ma już 5 lat. Pewne stwierdzenia i poglądy w nim zawarte mogą być nieaktualne.

Na problem z nieustawioną kolejnością w zapytaniu zawierającym DISTINCT ON natknąłem się już jakiś czas temu, jednak dopiero teraz zebrałem się, aby go opisać. A warto, bo niby błahy, a jednak ciekawy i sam trochę ignorowałem jego znaczenie.

Otóż korzystając w zapytaniu z DISTINCT ON na zestawie kolumn powinniśmy ustawić kolejność prezentacji wierszy poprzez standardową klauzulę ORDER BY. DISTINCT ON pomija bowiem kolejne wiersze o wartościach, które wystąpiły już wcześniej:

DISTINCT ON ( expression [, …] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. […] Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. […] The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).
http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-DISTINCT

Bez ustawionej kolejności może być przypadkowa (chodź technicznie to nie do końca prawda) i różna w zależności od sposobu użycia.

Przykład? Opiszę mój przypadek w nieco zmodyfikowanej formie. Miałem zapytanie korzystające z dwóch tabel: pierwsza to tabela z numerami telefonów klientów wraz z etykietami. Jeden klient może posiadać kilka telefonów, nawet z tymi samymi etykietami (ze względu na przypisywanie tych numerów do jego umów, których może być wiele):

id_customer	id_contract	number	label
5	12	600123123	Company Ltd.
5	13	600123123	Company Ltd.
5	14	600123123	John Poe

Z tabeli pobierane były unikalne numery telefonów dla klienta wraz z pierwszą etykietą – ta bowiem nie była do końca ważna. Korzystałem więc z zapytania:

SELECT DISTINCT ON (id_customer, number)
    id_customer,
    number,
    label
FROM customers

Nakładając na zapytanie ograniczenie na ID żądanego klienta otrzymywałem wiersze z jego telefonami oraz etykietą. Jeżeli do jednego numeru klienta było kilka etykiet, mogła zostać zaprezentowana dowolna z nich, każda bowiem była prawidłowa i w tym zastosowaniu nie miało to znaczenia.

Trzeba było jednak rozszerzyć tabelę o dodatkowe dane (tagi), które nie występowały – jak etykiety – przy każdym wierszu klienta. Jeżeli już jednak istniały, należało je zaprezentować, a jeżeli było ich kilka – najważniejszą z nich według zadanego priorytetu. Pominę przy analizie priorytet, ponieważ nie jest on aż tak istotny. Tabela po zmianach wyglądała więc tak:

id_customer	id_contract	number	label	tag
5	12	600123123	Company Ltd.	urgent
5	13	600123123	Company Ltd.
5	14	600123123	John Poe

Do zapytania dodałem tylko kolumnę z interesującą mnie daną:

SELECT DISTINCT ON (id_customer, number)
    id_customer,
    number,
    label,
    tag
FROM customers

Przy testach wszystko było OK, zawsze zwracane były wiersze z wypełnioną kolumną tag. To nieco uśpiło moją czujność i nie przetestowałem takiej liczb przypadków, abym trafił na pustą wartość mimo istnienia tagu dla numeru.

Sytuacja zmieniła się, gdy zapytanie trafiło do SQL-owego widoku. To zmieniło sposób sortowania wyników, przez co pobierając dane z widoku miałem przypadki zwracania numerów bez tagów mimo, że te ewidentnie istniały. Żeby było zabawniej, wykonanie zapytania bezpośrednio (z pominięciem widoku) działało tak, jak bym chciał.

Wróciłem więc do dokumentacji i stackoverflow. To naprowadziło mnie na podany na początku wpisu fragment dokumentacji. Aby poprawić zapytanie, należało więc użyć ORDER BY:

SELECT DISTINCT ON (id_customer, number)
    id_customer,
    number,
    label,
    tag
FROM customers
ORDER BY tag NULLS LAST

Dzięki temu, wiersze z tagami były zwracane jako pierwsze i ewentualne późniejsze wiersze z pustymi wartościami w tej kolumnie dla tego samego klienta i numeru były pomijane, a nie odwrotnie.

Nie można więc zapominać o klauzuli ORDER BY po kolumnach niewystępujących w DISTINCT ON, bo prędzej czy później się to zemści.

Optymalizacja zapytań z GROUP BY w MySQL (i nie tylko)

UWAGA! Ten wpis ma już 7 lat. Pewne stwierdzenia i poglądy w nim zawarte mogą być nieaktualne.

Przyznam, że do wczoraj sposób działania planera w przypadku zapytań agregujących (z klauzulą GROUP BY) był dla mnie nie do końca jasny.

Akurat trafiłem w mojej aplikacji na zapytanie na tyle proste, a jednocześnie wolne (operujące na dużej tabeli), że łatwo było je analizować. Szukając przyczyny i sposobów optymalizacji, trafiłem na znakomitą odpowiedź na stackoverflow. Pozwolę sobie wkleić jej sedno:

For covering index you add:
1. columns used in where clauses first, then
2. columns used in group by, then
3. columns used in order by, and then
4. columns used in select.

Ze swoim zapytaniem miałem nieco większy problem, ponieważ łączyło ono dwie tabele poprzez JOIN. Jak się okazuje, kolumny z warunków JOIN-owania należy przypisać do pierwszego punktu, co potwierdziło się w moim dzisiejszym pytaniu.

Sprawdziłem działanie tego schematu również na zapytaniach z GROUP BY na dużych tabelach w PostgreSQL i również działało (skutkowało wykonywaniem zapytania w pełni na indeksach), więc można przyjąć, że podobnie działa to dla większości popularnych DBMS.

PostgreSQL – losowe wartości z podzapytania

UWAGA! Ten wpis ma już 7 lat. Pewne stwierdzenia i poglądy w nim zawarte mogą być nieaktualne.

W PostgreSQL, korzystając z podzapytania używającego funkcji RANDOM() tak, jak np. w MySQL, otrzymamy te same wyniki dla każdego wiersza.

Przykład:

SELECT
    t1.id,
    (SELECT name FROM table2 ORDER BY RANDOM() LIMIT 1) AS random_name
FROM table1 t1

W tym DBMS podzapytanie tego typu wykonuje się raz, więc przy każdym wierszu z tabeli table1 będzie widniała ta sama wartość random_name.

Aby to zmienić, wystarczy sztucznie skorelować podzapytanie z zapytaniem nadrzędnym, np. tak:

SELECT
    t1.id,
    (SELECT name FROM table2 WHERE t1.id = t1.id ORDER BY RANDOM() LIMIT 1) AS random_name
FROM table1 t1

Niby proste, ale jakiś czas temu straciliśmy w pracy na tym trochę czasu (zwłaszcza nie wiedząc, że problem jest związany z podzapytaniami, bo samo zapytanie było bardziej złożone).