Wydajność tymczasowych tabel w PostgreSQL?

Witam,

Ostatnio nadknąłem się za zapytanie, które oblicza ilość sprzedanych produktów, ich cenę, ilość, ilość zwrotów itd itd. Wszytko na kilku tabelach tj. orders, order_items itd. Ze wzgędu na to, że tabela orders ma wiele statusów, któś napisał to sama zapytanie kilka razy tylko z innym statusem, a następnie połączył podzapytania przez union. Razem 8 takich podzapytan. Wszsytko zapisał w tymczasowej tabeli. I następnie stowrzył kolejne zapytanie aby obliczyć średnią sprzedaż, średnią ilość sprzedanych produktów, suma sprzedaży itd itd.

Moje pytanie. Czy opłaca się tworzyć takie tymczasowe tabele? Czy nie szybciej będzie stworzyć jedno zapytanie? Takie samo tylko bez tworzenia tymczasowej tabeli? Czy tworzenie takiej tymczasowej tabeli nie jest dłuższe w wykonaniu niżeli samo zapytani? Przecież to dodatkowana akcja na bazie danych.

a nie jest to tak, że ta tabela jest zasilana raz a potem można z tak zagregowanych danych robić różne zestawienia? Bo jeśli działa to tak, że mamy insert into temp select potem select from temp i zaraz potem truncate table temp to to nie ma większego sensu

Nawet jeśli są robione z tego różne zestawienia to i tak jest bez sensu. Lepiej zrobić zmaterializowany widok - jest wydajniejszy. Odświeżać można np. cyklicznie raz na miesiąc.
A i samo tworzenie tabeli z siedmiokrotnego UNION jest bez sensu - UNION z zasady jest niewydajny (bo robi niejawne grupowanie i porównywanie typów kolumn i różne inne dziwne sprawy). Bardziej wydajne by już było 1 raz CREATE TABLE AS SELECT ... i 7 razy INSERT INTO .
Co i tak jest bezsensowne :). Bo skoro masz tylko 8 wartości danego parametru, to się idealnie nadaje na indeks. Pole indeksowane (przy założeniu, że nidzie indziej nie zrobiłeś błędu w zapytaniu) powinno śmigać aż miło... A jak nie, to zawsze moiżna użyć CTE - na pewno bardziej wydajne niż tabele tymczasowe.
Coś mi się wydaje, że to zapytanie co widziałeś, to jakiś stażysta pisał...:)

@Marcin.Miga: Co to te CTE?? :O

https://www.postgresql.org/docs/9.1/queries-with.html

Common Table Expressions

Ok, na pewno nie napisze swojego nowego zapytania w tymczasowej tabeli. Nie jest to wydajne w moim przypadku. Jednakze, moze ktos mi lopatologicznie wytlumaczyc jak dziala ten WITH? Jak moge go zastapic UNION? Nie kumam troche tegooo?

Podaj przykłąd, co chcesz osiągnąć.

Mam tabele reports. W niej trzymam zamowienia, produkty oraz zwroty. W takim mega uproszczeniu:

order_id | type | quantity | cost

Przyklad zmowinie kubka (10.00) i dlugopisa (3.00) oraz zwrot za kubek.

order_id | product_id | type | quantity | cost
---------------- | -------------------
111 | 5423 | order | 1 | 10.00
111 | 2244 | order | 1 | 3.00
111 | 5423 | product | 1 | 10.00
111 | 2244 | product | 1 | 3.00
111 | 5423 | refund | 1 | 0.00

Tabela jest bardzo prosta. Ale pokazuje jak jest zapisywane zamowienie. Chodzi o to, ze musze obliczyc kilka statystyk np srednia ilosc zamowien w danym miesiacu. Ilosc sprzedanych produktow, ilosc zysku itd. Wiadomo, ze tabela jest znacznie wieszka. Jest sporo kategorii, typow zwrotu. Wiec niektore podzapytania lacza1-3 razy do tej samej tabeli, zaleznie od typu zwroti. I pozniej idzie UNION. Mam 5 podzapytan, ze wzgedu na typ zwrotu. Chociaz wydaje mi sie, ze to mozna jakos uproscic.

A skąd wiadomo, co jest kubek, a co długopis?
type to kategoria? Bo piszesz, ze jest sporo kategorii..
Wydaje mi się, że wszystko to co napisałeś można załatwić jednym (ew. dwoma z uzyciem CTE) zapytaniem.

W tabeli jest ID produktu. Mozesz mi podaj jakis prosty przyklad jak te CTE dziala? Troche tego nie kumam :/

@poniatowski: nie jesteś na forum od dzisiaj a trzeba Cię za język ciągnąć jak jakiegoś nooba. Dajesz przykładowe dane wejściowe, ICH DOKŁADNY OPIS, to co DOKŁADNIE chcesz z nich wyciągnąć i na pewno ktoś Ci pomoże.

Tu masz przykłady i teorie: https://www.sqlpedia.pl/cte-common-table-expressions/

A tu problem z forum i moją propozycje rozwiązania z CTE (chociaż tam jakiś błąd mu wyskakuje - nie wiem może w Oracle jest coś inaczej)
sql oracle schemat hr zagnieżdżone zapytanie