Problem z wydajnością SELECT'a do utworzonego widoku

Witam,
Mam taki problem jak w temacie. Zacznę od tego jaką tabelę posiadam:

CREATE TABLE "public"."device_data" ( 
 "deviceid" Bigint REFERENCES cars (deviceid),
 "ts" Timestamp Without Time Zone,
 "longitude" Double Precision,
 "lattitude" Double Precision,
 "speedgps" Numeric( 5, 0 ),
 "heading" Numeric( 5, 0 ),
 "altitude" Numeric( 5, 0 ),
 "satelite" Numeric( 3, 0 ),
 "eventid" Numeric( 3, 0 ),
 "mileagegps" Numeric( 20, 0 ),
 "inputs" Numeric( 5, 0 ));

Powyższa tabela zawiera dane z pojazdu i teraz chcę wyciągnąć stąd informacje takie jak:

1. numer ID - deviceid
2. data rozpoczęcia trasy - ts
3. data zakończenia trasy - ts

Data rozpoczęcia trasy ma miejsce wtedy gdy pole eventid = 11 oraz mod pola inputs = 1. Data zakończenia trasy ma miejsce wtedy gdy pole eventid = 11 oraz mod pola inputs = 0.
Utworzyłem taki widok:

CREATE VIEW historiatras AS
SELECT
DataRozpoczeciaTrasy.deviceid AS deviceid,
DataZakonczeniaTrasy.ts - DataRozpoczeciaTrasy.ts AS RoznicaCzasu,
DataRozpoczeciaTrasy.ts AS RozpoczecieTrasy, DataZakonczeniaTrasy.ts AS ZakonczenieTrasy,
DataRozpoczeciaTrasy.totaldistance AS PrzebiegCANPoczatkowy, DataZakonczeniaTrasy.totaldistance AS PrzebiegCANKoncowy,
DataRozpoczeciaTrasy.mileagegps AS PrzebiegGPSPoczatkowy, DataZakonczeniaTrasy.mileagegps AS PrzebiegGPSKoncowy,
DataRozpoczeciaTrasy.totalfuel AS ZuzytePaliwoPoczatkowe, DataZakonczeniaTrasy.totalfuel AS ZuzytePaliwoKoncowe,
DataRozpoczeciaTrasy.inputs AS StanInputsRozpoczecie, DataZakonczeniaTrasy.inputs AS StanInputsZakonczenie,
DataRozpoczeciaTrasy.eventid AS StanEventidRozpoczecie, DataZakonczeniaTrasy.eventid AS StanEventidZakonczenie
FROM DEVICE_DATA DataRozpoczeciaTrasy
JOIN DEVICE_DATA DataZakonczeniaTrasy ON DataRozpoczeciaTrasy.deviceid = DataZakonczeniaTrasy.deviceid AND mod(DataZakonczeniaTrasy.inputs,2)=0 AND DataZakonczeniaTrasy.eventid = 11 AND DataZakonczeniaTrasy.ts > DataRozpoczeciaTrasy.ts
WHERE mod(DataRozpoczeciaTrasy.inputs,2)=1
AND DataRozpoczeciaTrasy.eventid = 11

Następnie, aby wylistować wszystkie trasy dla wszystkich numerów ID wysyłam:

SELECT * FROM HistoriaTras Trasy
WHERE RoznicaCzasu = (SELECT MIN(Trasy1.RoznicaCzasu) FROM HistoriaTras Trasy1 WHERE Trasy.RozpoczecieTrasy = Trasy1.RozpoczecieTrasy)

Niestety baza zawiera 100 tysięcy wierszy - to i tak mało - a SELECT wykonuje się 982717.118 ms czyli dramatycznie długo.

Moje pytanie brzmi - w jaki sposób to przyśpieszyć? Dziękuje za pomoc.

Wydaje się trochę na wyrost takie łączenie tabeli ze sobą, ponieważ każdy początek łączy się z końcami każdej następnej trasy, co powoduje nadmiarowość zwracanych danych.
Dodatkowo filtrujesz to licząc minimalną różnicę czasu na wszystkich danych.

Poszedłbym raczej w kierunku skupieniu się na pobieraniu danych które faktycznie potrzebujesz:

with cteT as (
select
    row_number() over (partition by deviceid order by ts) r,
    mod(inputs,2) koniec
  ,* 
from 
     device_data
where 
      eventid = 11 
       and mod(inputs,2) in (0,1)
  )
  
  select 
      *
  from 
      cteT S
      INNER JOIN cteT K on k.r-1=s.r and  k.deviceid=s.deviceid
  where 
      s.koniec = 0

http://sqlfiddle.com/#!17/e4ab5/3

Super!. Dziękuję za podpowiedź. Delikatnie zmodyfikowałem to co napisałeś: k.r=s.r-1 zamiast k.r-1=s.r. Teraz mam to o co mi chodziło.

Mam jeszcze jedno pytanie w tym temacie, mam jeszcze dwie dodatkowe kolumny w tabeli device_data. Są to kolumny vehiclespeed gdzie podawana jest prękość pojazdu i z niej chciałbym wyciągać wartość maksymalną w zakresie od startu do stopu + druga kolumna accelerator gdzie chciałbym sumować wszystkie wartości od startu do stopu. Jak to wykonać?

Z góry dziękuję za dalszą pomoc.

Ja mam wrażenie, że na siłę starasz się wyciągnąć dane z surowo zaimportowanych danych z jakiegoś urządzenia rejestrującego dane pojazdów, oczywiście, że się da to zrobić, ale warto się zastanowić czy nie lepiej sobie przygotować dane podczas importu, bo później to już robi się niefajnie.:

with cteT as (
select
    row_number() over (partition by deviceid order by ts) r,
    mod(inputs,2) koniec
  ,* 
from 
     device_data
where 
      eventid = 11 
       and mod(inputs,2) in (0,1)
  )
 , trasy as (  
  select 
    s.deviceid AS deviceid
    ,k.ts - s.ts AS RoznicaCzasu
    ,s.ts AS RozpoczecieTrasy
    ,k.ts AS ZakonczenieTrasy
    ,s.inputs AS StanInputsRozpoczecie
    ,k.inputs AS StanInputsZakonczenie
    ,s.eventid AS StanEventidRozpoczecie
    ,k.eventid AS StanEventidZakonczenie
  from 
      cteT S
      INNER JOIN cteT K on k.r-1=s.r and  k.deviceid=s.deviceid
  where 
      s.koniec = 0
), pkt as (
select
    deviceid
  ,ts
  ,vehiclespeed
  ,accelerator 
from 
     device_data
where 
      (eventid = 11 and mod(inputs,2) > 1)
      or eventid <> 11
)

select
    trasy.deviceid,
    roznicaczasu,
    rozpoczecietrasy,
    zakonczenietrasy,
    staninputsrozpoczecie,
    staninputszakonczenie,
    staneventidrozpoczecie,
    staneventidzakonczenie,
    max(vehiclespeed) maxspeed,
    sum(accelerator) as accelerator
from 
trasy
left join pkt on pkt.deviceid = trasy.deviceid 
and pkt.ts between trasy.rozpoczecietrasy and trasy.zakonczenietrasy
group by
    trasy.deviceid,
    roznicaczasu,
    rozpoczecietrasy,
    zakonczenietrasy,
    staninputsrozpoczecie,
    staninputszakonczenie,
    staneventidrozpoczecie,
    staneventidzakonczenie,
    vehiclespeed,
    accelerator

http://sqlfiddle.com/#!17/c4c97/5

Dziękuję. Przygotowuję na podstawie danych raporty w Power BI. Raczej staram się wszystko zorganizować SELECTami.

Delikatnie przerobiłem jednak selecta ponieważ daty start i stop były zamienione:

WITH cteT AS (
SELECT
    ROW_NUMBER() OVER (partition BY deviceid ORDER BY ts) r,
    MOD(inputs,2) koniec
  ,* 
FROM 
     device_data
WHERE 
      eventid = 11 
       AND MOD(inputs,2) IN (0,1)
  )
 , trasy AS (  
  SELECT 
    s.deviceid AS deviceid
    ,s.ts - k.ts AS RoznicaCzasu
    ,k.ts AS RozpoczecieTrasy
    ,s.ts AS ZakonczenieTrasy
    ,k.inputs AS StanInputsRozpoczecie
    ,s.inputs AS StanInputsZakonczenie
    ,k.eventid AS StanEventidRozpoczecie
    ,s.eventid AS StanEventidZakonczenie
  FROM 
      cteT S
      INNER JOIN cteT K ON k.r=s.r-1 AND  k.deviceid=s.deviceid
  WHERE 
      s.koniec = 0
), pkt AS (
SELECT
    deviceid
  ,ts
  ,vehiclespeed
  ,accelerator 
FROM 
     device_data
WHERE 
      (eventid = 11 AND MOD(inputs,2) > 1)
      OR eventid <> 11
)
 
SELECT
    trasy.deviceid,
    roznicaczasu,
    rozpoczecietrasy,
    zakonczenietrasy,
    staninputsrozpoczecie,
    staninputszakonczenie,
    staneventidrozpoczecie,
    staneventidzakonczenie,
    MAX(vehiclespeed) maxspeed,
    SUM(accelerator) AS accelerator
FROM 
trasy
LEFT JOIN pkt ON pkt.deviceid = trasy.deviceid 
AND pkt.ts BETWEEN trasy.rozpoczecietrasy AND trasy.zakonczenietrasy
GROUP BY
    trasy.deviceid,
    roznicaczasu,
    rozpoczecietrasy,
    zakonczenietrasy,
    staninputsrozpoczecie,
    staninputszakonczenie,
    staneventidrozpoczecie,
    staneventidzakonczenie,
    vehiclespeed,
    accelerator

Jednak teraz zamiast sumy accelerator i max wartości vehiclespeed zwraca mi każdy wiersz z danej trasy:
https://imgur.com/a/V902d

Problemu jakby nie widać, jeżeli w datach nie ma różnych godzin.

Dwa razy dałem ci rozwiązanie na sqlfiddle, nie dlatego, żeby się pochwalić, ale dlatego, ze tak najłatwiej testować zapytania. Więc zamiast screenów, przygotuj próbkę danych, wtedy możemy rozmawiać, ja szklanej kuli nie mam...

Przepraszam, podsyłam preview:
http://sqlfiddle.com/#!17/f1fa4/1/0

Chodzi o to, że w rezultacie widzimy 6 wierszy, a trasy są dwie:
start:
(1,'2017-11-01 0200',11,3,0,0)
koniec:
,(1,'2017-11-01 0200',11,2,0,0)
start:
,(1,'2017-11-01 0200',11,3,0,0)
koniec:
,(1,'2017-11-01 0200',11,2,0,0)

Bo niepotrzebnie w group by masz grupowanie po kolumnach na których robisz obliczenia, konkretnie: vehiclespeed, accelerator. To że nie ma ich w select nie znaczy, że silnik po nich nie grupuje...

http://sqlfiddle.com/#!17/f1fa4/5

Dziękuję!