explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0eLF

Settings
# exclusive inclusive rows x rows loops node
1. 0.455 9,708.310 ↓ 4,896.0 4,896 1

Limit (cost=5,926.13..5,926.13 rows=1 width=212) (actual time=9,706.914..9,708.310 rows=4,896 loops=1)

2.          

CTE _filtered_vehicles

3. 2.901 122.465 ↓ 271.8 1,359 1

Unique (cost=699.58..699.65 rows=5 width=38) (actual time=118.274..122.465 rows=1,359 loops=1)

4. 2.382 119.564 ↓ 271.8 1,359 1

Sort (cost=699.58..699.59 rows=5 width=38) (actual time=118.265..119.564 rows=1,359 loops=1)

  • Sort Key: dim_dim_vehicle.vehicle_sid, dim_dim_vehicle.vehicle_id, dim_dim_organization.organization_sid, dim_dim_organization.display_id, dim_dim_organization.organization
  • Sort Method: quicksort Memory: 155kB
5. 1.566 117.182 ↓ 271.8 1,359 1

Nested Loop (cost=0.99..699.52 rows=5 width=38) (actual time=0.107..117.182 rows=1,359 loops=1)

6. 0.256 10.973 ↓ 4.7 1,359 1

Nested Loop (cost=0.58..474.40 rows=291 width=30) (actual time=0.087..10.973 rows=1,359 loops=1)

7. 0.046 0.241 ↑ 3.5 36 1

Nested Loop (cost=0.28..172.45 rows=126 width=30) (actual time=0.035..0.241 rows=36 loops=1)

8. 0.015 0.015 ↑ 2.8 36 1

Function Scan on unnest param_org_sid (cost=0.00..3.00 rows=100 width=4) (actual time=0.011..0.015 rows=36 loops=1)

9. 0.180 0.180 ↑ 1.0 1 36

Index Scan using ix_dim_dim_organization_02 on dim_dim_organization (cost=0.28..1.66 rows=1 width=30) (actual time=0.005..0.005 rows=1 loops=36)

  • Index Cond: (organization_sid = param_org_sid.param_org_sid)
10. 10.476 10.476 ↓ 9.5 38 36

Index Scan using ix_factless_factless_organization_vehicle_history_01 on factless_factless_organization_vehicle_history (cost=0.29..2.28 rows=4 width=8) (actual time=0.011..0.291 rows=38 loops=36)

  • Index Cond: (organization_sk = dim_dim_organization.organization_sk)
  • Filter: ((end_date_sk = (-1)) AND ('2019-09-27'::date >= ((start_date_sk)::text)::date) AND ((end_date_sk = (-1)) OR ('2019-09-27'::date <= ((end_date_sk)::text)::date)))
  • Rows Removed by Filter: 4
11. 104.643 104.643 ↑ 1.0 1 1,359

Index Scan using pk_dim_dim_vehicle on dim_dim_vehicle (cost=0.42..0.74 rows=1 width=16) (actual time=0.077..0.077 rows=1 loops=1,359)

  • Index Cond: (vehicle_sk = factless_factless_organization_vehicle_history.vehicle_sk)
  • Filter: (company_id = 762)
12. 11.419 9,707.855 ↓ 4,900.0 4,900 1

Sort (cost=5,226.47..5,226.48 rows=1 width=212) (actual time=9,706.911..9,707.855 rows=4,900 loops=1)

  • Sort Key: segment_event_operational_snapshot.modified_date
  • Sort Method: quicksort Memory: 1,494kB
13. 4.400 9,696.436 ↓ 4,900.0 4,900 1

Nested Loop (cost=0.00..5,226.46 rows=1 width=212) (actual time=238.252..9,696.436 rows=4,900 loops=1)

14. 124.676 124.676 ↓ 271.8 1,359 1

CTE Scan on _filtered_vehicles vehicle (cost=0.00..0.30 rows=5 width=104) (actual time=118.276..124.676 rows=1,359 loops=1)

15. 17.667 9,567.360 ↑ 1.2 4 1,359

Append (cost=0.00..1,045.08 rows=5 width=116) (actual time=5.525..7.040 rows=4 loops=1,359)

16. 0.000 0.000 ↓ 0.0 0 1,359

Seq Scan on segment_event_operational_snapshot (cost=0.00..0.00 rows=1 width=116) (actual time=0.000..0.000 rows=0 loops=1,359)

  • Filter: ((gps_date_time > '2019-06-29 13:24:16'::timestamp without time zone) AND (gps_date_time <= '2019-09-27 13:34:05.927209'::timestamp without time zone) AND (modified_date >= '2019-09-27 13:24:16.724947'::timestamp without t (...)
17. 3,254.805 3,254.805 ↓ 0.0 0 1,359

Index Scan using ix_segment_event_operational_snapshot_12_p2019_08 on segment_event_operational_snapshot_p2019_08 (cost=0.57..372.63 rows=1 width=116) (actual time=2.395..2.395 rows=0 loops=1,359)

  • Index Cond: ((vehicle_sid = vehicle.vehicle_sid) AND (gps_date_time > '2019-06-29 13:24:16'::timestamp without time zone) AND (gps_date_time <= '2019-09-27 13:34:05.927209'::timestamp without time zone) AND (modified_date >= '2019 (...)
18. 2,872.926 2,872.926 ↓ 4.0 4 1,359

Index Scan using ix_segment_event_operational_snapshot_12_p2019_09 on segment_event_operational_snapshot_p2019_09 (cost=0.56..297.27 rows=1 width=116) (actual time=2.096..2.114 rows=4 loops=1,359)

  • Index Cond: ((vehicle_sid = vehicle.vehicle_sid) AND (gps_date_time > '2019-06-29 13:24:16'::timestamp without time zone) AND (gps_date_time <= '2019-09-27 13:34:05.927209'::timestamp without time zone) AND (modified_date >= '2019 (...)
19. 125.028 125.028 ↓ 0.0 0 1,359

Index Scan using ix_segment_event_operational_snapshot_12_p2019_06 on segment_event_operational_snapshot_p2019_06 (cost=0.57..8.76 rows=1 width=116) (actual time=0.092..0.092 rows=0 loops=1,359)

  • Index Cond: ((vehicle_sid = vehicle.vehicle_sid) AND (gps_date_time > '2019-06-29 13:24:16'::timestamp without time zone) AND (gps_date_time <= '2019-09-27 13:34:05.927209'::timestamp without time zone) AND (modified_date >= '2019 (...)
20. 3,296.934 3,296.934 ↓ 0.0 0 1,359

Index Scan using ix_segment_event_operational_snapshot_12_p2019_07 on segment_event_operational_snapshot_p2019_07 (cost=0.57..366.42 rows=1 width=116) (actual time=2.426..2.426 rows=0 loops=1,359)

  • Index Cond: ((vehicle_sid = vehicle.vehicle_sid) AND (gps_date_time > '2019-06-29 13:24:16'::timestamp without time zone) AND (gps_date_time <= '2019-09-27 13:34:05.927209'::timestamp without time zone) AND (modified_date >= '2019 (...)
Total runtime : 9,709.298 ms