Best way to select calculated values from another table in the same query postgres?

Question

Hi i have postgres database and four tables

vehicles -> trips

vehicles -> component_values -> component_types

vehicles:

╔════╦══════════════════════════╦════════════════════════╦════════════════╦═════════╗
║ id ║           slug           ║          name          ║  manufacturer  ║  model  ║
╠════╬══════════════════════════╬════════════════════════╬════════════════╬═════════╣
║  1 ║ manufacturer-x-model-3   ║ Manufacturer X Model 3 ║ Manufacturer X ║ Model 3 ║
║  2 ║ manufacturer-x-model-1   ║ Manufacturer X Model 1 ║ Manufacturer X ║ Model 1 ║
║  3 ║ manufacturer-x-model-1-1 ║ Manufacturer X Model 1 ║ Manufacturer X ║ Model 1 ║
╚════╩══════════════════════════╩════════════════════════╩════════════════╩═════════╝

trips:

╔═════╦════════════╦═════════════╦═════════════╦═════════════════╗
║ id  ║ vehicle_id ║    name     ║   mileage   ║   recorded_at   ║
╠═════╬════════════╬═════════════╬═════════════╬═════════════════╣
║   1 ║          1 ║ 10386735    ║ 386734.997  ║ 2/25/2014 13:56 ║
║   2 ║          1 ║ 11771530.14 ║ 771530.14   ║ 3/1/2014 19:41  ║
║   3 ║          1 ║ 121112028.4 ║ 1112028.39  ║ 3/5/2014 3:23   ║
║   4 ║          1 ║ 131406814.9 ║ 1406814.892 ║ 3/8/2014 20:56  ║
║   5 ║          1 ║ 141933528.7 ║ 1933528.711 ║ 3/13/2014 0:19  ║
║   6 ║          1 ║ 152256488.6 ║ 2256488.579 ║ 3/16/2014 21:15 ║
╚═════╩════════════╩═════════════╩═════════════╩═════════════════╝

component_values:

╔════╦═══════════════════╦═════════╦════════════╦════════════╦═════════════╦═════════════╗
║ id ║ component_type_id ║ trip_id ║ vehicle_id ║  mileage   ║   damage    ║ damage_eff  ║
╠════╬═══════════════════╬═════════╬════════════╬════════════╬═════════════╬═════════════╣
║  1 ║                 1 ║       1 ║          1 ║ 386734.997 ║ 0.002260565 ║ 0.002225831 ║
║  2 ║                 2 ║       1 ║          1 ║ 386734.997 ║ 0.002260306 ║ 0.002238006 ║
║  3 ║                 3 ║       1 ║          1 ║ 386734.997 ║ 0.002261288 ║ 0.002266295 ║
║  4 ║                 4 ║       1 ║          1 ║ 386734.997 ║ 0.002054489 ║ 0.002060029 ║
║  5 ║                 5 ║       1 ║          1 ║ 386734.997 ║ 0.002052669 ║ 0.002107272 ║
║  6 ║                 6 ║       1 ║          1 ║ 386734.997 ║ NULL        ║ NULL        ║
║  7 ║                 7 ║       1 ║          1 ║ 386734.997 ║ NULL        ║ NULL        ║
║  8 ║                 1 ║       2 ║          1 ║ 771530.14  ║ 0.004792952 ║ 0.0048514   ║
║  9 ║                 2 ║       2 ║          1 ║ 771530.14  ║ 0.004792404 ║ 0.004710451 ║
║ 10 ║                 3 ║       2 ║          1 ║ 771530.14  ║ 0.004794486 ║ 0.004805461 ║
╚════╩═══════════════════╩═════════╩════════════╩════════════╩═════════════╩═════════════╝

component_types:

╔════╦═════════════════════════════════════╦════════════════╦══════════════════════╗
║ id ║                slug                 ║  manufacturer  ║         name         ║
╠════╬═════════════════════════════════════╬════════════════╬══════════════════════╣
║  6 ║ manufacturer-d-battery              ║ Manufacturer D ║ Battery              ║
║  2 ║ manufacturer-b-differential-1       ║ Manufacturer B ║ Differential 1       ║
║  3 ║ manufacturer-c-driveshaft-1         ║ Manufacturer C ║ Driveshaft 1         ║
║  5 ║ manufacturer-c-gearbox-output-shaft ║ Manufacturer C ║ Gearbox output shaft ║
║  1 ║ manufacturer-a-motor-1              ║ Manufacturer A ║ Motor 1              ║
║  4 ║ manufacturer-c-gearbox-input-shaft  ║ Manufacturer C ║ Gearbox input shaft  ║
║  7 ║ usage-profile                       ║                ║ Usage profile        ║
╚════╩═════════════════════════════════════╩════════════════╩══════════════════════╝

and i'm trying to get the vehicles with the latest trip dates and mileage and also the heights and lowest damaged component for each vehicle

so the result will be like:

╔════════════╦══════════════════╦══════════════════════════╦═════════════════════════════════╦════════════════════════════════╦════════════════════════════════╦═══════════════════════════════╗
║ vehicle_id ║ latest_trip_date ║ latest_trip_date_mileage ║ heights_damaged_component_value ║ heights_damaged_component_name ║ lowest_damaged_component_value ║ lowest_damaged_component_name ║
╠════════════╬══════════════════╬══════════════════════════╬═════════════════════════════════╬════════════════════════════════╬════════════════════════════════╬═══════════════════════════════╣
║          1 ║ 4/19/2014 3:27   ║ 4844305.912              ║ 0.029372972                     ║ Gearbox input shaft            ║ 0.002052669                    ║ Gearbox output shaft          ║
║          2 ║ 5/19/2014 16:13  ║ 5567945.164              ║ 0.029405924                     ║ Driveshaft 1                   ║ 0.001864137                    ║ Gearbox output shaft          ║
║          3 ║ 4/28/2014 12:55  ║ 5286175.763              ║ 0.030745029                     ║ Driveshaft 1                   ║ 0.001957685                    ║ Differential 1                ║
║          4 ║ 2/25/2014 3:32   ║ 5398006.007              ║ 0.030495792                     ║ Driveshaft 1                   ║ 0.001814434                    ║ Differential 1                ║
║          5 ║ 4/25/2014 9:51   ║ 5179558.475              ║ 0.032060074                     ║ Gearbox input shaft            ║ 0.001936431                    ║ Differential 1                ║
║          6 ║ 5/9/2014 7:43    ║ 5234355.804              ║ 0.030576454                     ║ Gearbox input shaft            ║ 0.002254191                    ║ Gearbox output shaft          ║
║          7 ║ 6/21/2014 18:09  ║ 5705722.416              ║ 0.029957374                     ║ Driveshaft 1                   ║ 0.001653441                    ║ Gearbox output shaft          ║
║          8 ║ 4/23/2014 5:25   ║ 5590470.028              ║ 0.031900163                     ║ Driveshaft 1                   ║ 0.002083733                    ║ Gearbox output shaft          ║
║          9 ║ 3/28/2014 20:37  ║ 5598159.883              ║ 0.031208918                     ║ Driveshaft 1                   ║ 0.00162805                     ║ Differential 1                ║
║         10 ║ 5/24/2014 19:27  ║ 5020795.001              ║ 0.02962505                      ║ Gearbox input shaft            ║ 0.001729646                    ║ Differential 1                ║
╚════════════╩══════════════════╩══════════════════════════╩═════════════════════════════════╩════════════════════════════════╩════════════════════════════════╩═══════════════════════════════╝

i already tried this query

select 

vehicles.id as vehicle_id,
latest_trips.recorded_at as latest_trip_date,
latest_trips.mileage as latest_trip_date_mileage,
heights_damaged_components.damage as heights_damaged_component_value,
heights_damaged_components.name as heights_damaged_component_name,
lowest_damaged_components.damage as lowest_damaged_component_value,
lowest_damaged_components.name as lowest_damaged_component_name

from vehicles 

left join (
    SELECT t.id, t.vehicle_id, t.mileage, t.recorded_at
    FROM public.trips t
    inner JOIN (SELECT vehicle_id, MAX(recorded_at) as latest_trip_date FROM public.trips  GROUP BY vehicle_id)
    tm ON t.vehicle_id = tm.vehicle_id AND t.recorded_at = tm.latest_trip_date
) 
as latest_trips on latest_trips.vehicle_id = vehicles.id

left join (
    select ct.name, hd.component_type_id, hd.vehicle_id, hd.damage
    from public.component_values as hd
    INNER JOIN (
        SELECT vehicle_id,
        MAX(damage) as heights_damaged_component 
        FROM public.component_values 
        GROUP BY vehicle_id
    )
    hdm ON hd.vehicle_id = hdm.vehicle_id AND hd.damage = hdm.heights_damaged_component
    join public.component_types as ct on ct.id = hd.component_type_id
) 
as heights_damaged_components on heights_damaged_components.vehicle_id = vehicles.id

left join (
    select ct.name, ld.component_type_id, ld.vehicle_id, ld.damage
    from public.component_values as ld
    INNER JOIN (
        SELECT vehicle_id, MIN(damage) as lowest_damaged_component 
        FROM public.component_values  
        GROUP BY vehicle_id
    )
    ldm ON ld.vehicle_id = ldm.vehicle_id AND ld.damage = ldm.lowest_damaged_component
    join public.component_types as ct on ct.id = ld.component_type_id
) as lowest_damaged_components on lowest_damaged_components.vehicle_id = vehicles.id

but i have like 10000 vehicles and big trips and component_values and this query give me the result in like 3 to 6 seconds, is their a batter way to do this with better performance and time?

can i use GENERATED columns in my case and how ?

please any help and many many thanks in advance.

window functions may be helpfull, rewriting your inner joins, something like select max(damage) over (PARTITION by vehicle_id) as heights_damaged_component — FatFreddy, Sep 04 '19 at 10:11
How often is such data required, that 3 seconds is an unacceptable runtime for this query ?! — Caius Jard, Sep 04 '19 at 10:26
@CaiusJard this query required for listing the vehicles in grid view with pagination so if the user start to move from page 1.2.3.4.5 each time he will wait like 3 sec — Fadi, Sep 04 '19 at 10:32
Oof, yeah I don't think I would calculate all that for 10,000 vehicles just so I could show 10 of them, then calc it all again for page 2 so I could show another 10. Can you break the workload up a bit so that you're calcing it on a reduced set of vehicles (just the ones on the page)? — Caius Jard, Sep 04 '19 at 12:15
@CaiusJard i hop i can but i also need to order on them and filter :(, so how i can do this without calculating all those fields for 10,000 vehicles — Fadi, Sep 04 '19 at 12:22
@CaiusJard what you mean by "order and filter first?", and i meant i need to filter and order on the calculated value so how i can order and filter on them before i calculated them ? — Fadi, Sep 04 '19 at 12:35
You ask for GENERATED columns, but won't tell us your version of Postgres. — Erwin Brandstetter, Sep 04 '19 at 14:29
@ErwinBrandstetter sorry didn't know we need version, and it's "PostgreSQL 11.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit" — Fadi, Sep 04 '19 at 14:50
Like explained in the referenced answer, `STORED` generated columns are introduced with Postgres 12. Not available in Postgres 11. — Erwin Brandstetter, Sep 04 '19 at 14:53
@ErwinBrandstetter i can upgrade the version np, but how should i use it in my case their is no example for it, — Fadi, Sep 04 '19 at 19:12

Best way to select calculated values from another table in the same query postgres?

0 Answers0