How can I optimize VIEW or SELECT?

Question

I created view named HistoriaTras that means routes history.

CREATE VIEW historiatras AS
SELECT
DataRozpoczeciaTrasy.deviceid AS deviceid,
DataZakonczeniaTrasy.ts - DataRozpoczeciaTrasy.ts AS RoznicaCzasu,
DataRozpoczeciaTrasy.ts AS RozpoczecieTrasy, DataZakonczeniaTrasy.ts AS ZakonczenieTrasy
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

ts = timestamp
DataRozpoczeciaTrasy = date of start route
DataZakonczeniaTrasy = date of stop route
RoznicaCzasu = time between DataRozpoczeciaTrasy - DataZakonczeniaTrasy

If I make below SELECT everything works almost good but it take too much time ~ 27 seconds - and it do not contains all routes (because I make select from one day and for one device - 85758):

SELECT * FROM HistoriaTras Trasy
WHERE RoznicaCzasu = (SELECT MIN(Trasy1.RoznicaCzasu) 
                      FROM HistoriaTras Trasy1
                      WHERE Trasy.RozpoczecieTrasy = Trasy1.RozpoczecieTrasy) 
   AND deviceid = 85758 
   AND RozpoczecieTrasy > '2017-11-05 00:00:00' 
   AND RozpoczecieTrasy < '2017-11-06 00:00:00'

If i delete:

AND deviceid = 85758 AND RozpoczecieTrasy > '2017-11-05 00:00:00' AND RozpoczecieTrasy < '2017-11-06 00:00:00'

It will take... too much time. I did not test it but it take too long time.

I have about 100 k rows in device_data table. Friends, please write me how can I optimize this select or view? I am using PostgreSQL 10.

I am pasting explain analyze of SELECT because analyze of VIEW is not allowed:

Nested Loop  (cost=0.00..21281.66 rows=1 width=40) (actual time=3973.445..982716.756 rows=380 loops=1)
  Join Filter: ((datazakonczeniatrasy.ts > datarozpoczeciatrasy.ts) AND (datarozpoczeciatrasy.deviceid = datazakonczeniatrasy.deviceid) AND ((datazakonczeniatrasy.ts - datarozpoczeciatrasy.ts) = (SubPlan 1)))
  Rows Removed by Join Filter: 142880
  ->  Seq Scan on device_data datarozpoczeciatrasy  (cost=0.00..5259.22 rows=1 width=16) (actual time=0.046..17.667 rows=380 loops=1)
        Filter: ((eventid = '11'::numeric) AND (mod(inputs, '2'::numeric) = '1'::numeric))
        Rows Removed by Filter: 97518
  ->  Seq Scan on device_data datazakonczeniatrasy  (cost=0.00..5259.22 rows=1 width=16) (actual time=0.011..17.850 rows=377 loops=380)
        Filter: ((eventid = '11'::numeric) AND (mod(inputs, '2'::numeric) = '0'::numeric))
        Rows Removed by Filter: 97521
  SubPlan 1
    ->  Aggregate  (cost=10763.19..10763.20 rows=1 width=16) (actual time=34.834..34.834 rows=1 loops=28011)
          ->  Nested Loop  (cost=0.00..10763.19 rows=1 width=16) (actual time=9.246..34.805 rows=112 loops=28011)
                Join Filter: ((datazakonczeniatrasy_1.ts > datarozpoczeciatrasy_1.ts) AND (datarozpoczeciatrasy_1.deviceid = datazakonczeniatrasy_1.deviceid))
                Rows Removed by Join Filter: 270
                ->  Seq Scan on device_data datarozpoczeciatrasy_1  (cost=0.00..5503.96 rows=1 width=16) (actual time=5.930..17.172 rows=1 loops=28011)
                      Filter: ((eventid = '11'::numeric) AND (datarozpoczeciatrasy.ts = ts) AND (mod(inputs, '2'::numeric) = '1'::numeric))
                      Rows Removed by Filter: 97897
                ->  Seq Scan on device_data datazakonczeniatrasy_1  (cost=0.00..5259.22 rows=1 width=16) (actual time=0.012..17.327 rows=377 loops=28407)
                      Filter: ((eventid = '11'::numeric) AND (mod(inputs, '2'::numeric) = '0'::numeric))
                      Rows Removed by Filter: 97521
Planning time: 0.699 ms
Execution time: 982717.118 ms

My table:

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 ),
 "voltageanalog1" Numeric( 4, 2 ),
 "voltageanalog2" Numeric( 4, 2 ),
 "voltageanalog3" Numeric( 4, 2 ),
 "voltageanalog4" Numeric( 4, 2 ),
 "voltageanalog5" Numeric( 4, 2 ),
 "outputs" Numeric( 3, 0 ),
 "totaldistance" Numeric( 20, 0 ),
 "totalfuel" Numeric( 20, 0 ),
 "vehiclespeed" Numeric( 5, 0 ),
 "enginespeed" Numeric( 5, 0 ),
 "fuellevel" Numeric( 5, 0 ),
 "fuelcons" Numeric( 5, 0 ),
 "accelerator" Numeric( 3, 0 ),
 "tachograph" Numeric( 5, 0 ),
 "axleweight" Numeric( 5, 0 ),
 "indicators" Numeric( 10, 0 ),
 "drivercode" Numeric( 20, 0 ),
 "wiretemp1" Double Precision,
 "wiretemp2" Double Precision,
 "wiretemp3" Double Precision,
 "wiretemp4" Double Precision,
 "wiretemp5" Double Precision,
 "wiretemp6" Double Precision,
 "fuelflag" Integer,
 "gsmsignal" SmallInt,
 "speedcan" Integer,
 "gsmoperator" Bigint,
 "totalidlefuel" Bigint,
 "fuellevelperc" Integer,
 "enginetemp" SmallInt,
 "enginetotalhours" Bigint,
 "engineidletime" Bigint,
 "oiltemp" SmallInt,
 "hydroiltemp" SmallInt,
 "wirecode1" Numeric( 20, 0 ),
 "wirecode2" Numeric( 20, 0 ),
 "wirecode3" Numeric( 20, 0 ),
 "wirecode4" Numeric( 20, 0 ),
 "wirecode5" Numeric( 20, 0 ),
 "wirecode6" Numeric( 20, 0 ),
 "rapidpedalpress" Bigint,
 "rapidaccel" Bigint,
 "rapidbreak" Bigint,
 "engineoverspeed" Bigint,
 "torgue" SmallInt,
 "drivetimeoverspeedlimit0x12" Bigint,
 "drivetimeoverspeedlimit0x13" Bigint,
 "driver1idcard" Numeric( 20, 0 ),
 "driver2idcard" Numeric( 20, 0 ),
 "x3d" SmallInt,
 "y3d" SmallInt,
 "z3d" SmallInt,
 "axleweight1" Integer,
 "axleweight2" Integer,
 "axleweight3" Integer,
 "axleweight4" Integer );

Run `analyze HistoriaTras;`. Then run `explain analyze your_query`, and paste its output into your question. Also read the [info link](https://stackoverflow.com/tags/postgresql-performance/info) in the postgresql performance tag. — Mike Sherrill 'Cat Recall', Nov 26 '17 at 20:40
Thank you for the information. I have pasted analyze of SELECT because analyze of VIEW is not allowed because it is virtual table. — Damian Kowalewski, Nov 26 '17 at 21:01
You can absolutely generate the execution plan for a select based on a view (but it wouldn't make a difference) — , Nov 26 '17 at 21:12
No index usage whatsoever, can you post your table structure and also what indexes you have on it please? — Jorge Campos, Nov 26 '17 at 22:02
are there any indexes on that table? see https://stackoverflow.com/questions/37329561/how-to-list-indexes-created-for-table-in-postgres — Paul Maxwell, Nov 26 '17 at 22:36
[Some rules of thumb for indexing](https://dba.stackexchange.com/a/31517/1064) — Mike Sherrill 'Cat Recall', Nov 27 '17 at 01:59

Paul Maxwell · Answer 1 · 2017-11-27T22:52:41.183

0

I suggest using the "window function" LEAD() instead of self-joining involving the use of MOD().

SELECT
      d.deviceid       AS deviceid
    , d.ts - d.lead_ts AS roznicaczasu
    , d.ts             AS rozpoczecietrasy
    , d.lead_ts        AS zakonczenietrasy
FROM (
      SELECT
            deviceid
          , ts
          , case when MOD(Inputs,2) = 1
                 then LEAD(ts) OVER (PARTITION BY deviceid, eventid 
                                     ORDER BY inputs DESC)
            end AS lead_ts
      FROM DEVICE_DATA
      WHERE eventid = 11
      ) d
WHERE d.lead_ts IS NOT NULL
;

edited Nov 27 '17 at 22:52

answered Nov 26 '17 at 22:31

Paul Maxwell

33,002
3
32
51

your proposal work almost good but now I see that in few cases date of start route is later than date of stop route: https://imgur.com/a/K8n3P Start of route is when eventid = 11 and inputs mod 2 return 1. Stop of route is when eventid = 11 and inputs mod 2 return 0. – Damian Kowalewski Nov 27 '17 at 07:18
try this `ORDER BY MOD(Inputs,2) DESC` instead of `ORDER BY Inputs` in the query above. **As I cannot see any data I am working blindfolded** – Paul Maxwell Nov 27 '17 at 07:26
Thank you for the information but still I have wrong date of stop route. Please see image: https://imgur.com/a/7tfmn I inserted an example above. Your select detected stop of route where I marked as blue "your stop" but I want to get date that is near red "stop". Detecting start of route is OK. :) – Damian Kowalewski Nov 27 '17 at 08:03
reverse the calculation to avoid the negatives e.g. a-b = -? so b-a = +? – Paul Maxwell Nov 27 '17 at 08:08
I cannot work with "images of data" sorry, it is useless to me – Paul Maxwell Nov 27 '17 at 08:10
I hosted csv for you: https://nofile.io/f/o96pGdOegMz/46747.csv Thank you for your help. – Damian Kowalewski Nov 27 '17 at 08:24
You should share no more than what you put in that image. It is unreasonable to expect any volunteer to convert 18.44Mb of raw data into a table. I suggest you read these: [Provide a `Minimal Complete Verifiable Example` (MCVE)](https://stackoverflow.com/help/mcve) and [Why should I provide a MCVE](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Paul Maxwell Nov 27 '17 at 22:58
I have made changes to the query above, I do not know if it will solve remaining problems but perhaps you could try it. – Paul Maxwell Nov 27 '17 at 22:59

How can I optimize VIEW or SELECT?

1 Answers1