NOW() przy zapytaniach na spartycjonowanych tabelach

UWAGA! Ten wpis ma już 6 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ę).

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

UWAGA! Ten wpis ma już 8 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.