explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K4g3

Settings
# exclusive inclusive rows x rows loops node
1. 22,781.799 23,273.673 ↓ 1.2 1,266 1

Nested Loop Left Join (cost=40.46..370,461.21 rows=1,068 width=339) (actual time=31.746..23,273.673 rows=1,266 loops=1)

  • Join Filter: (c.companyid = v.companyid)
2. 5.740 28.518 ↓ 1.2 1,266 1

Nested Loop Left Join (cost=40.18..2,440.13 rows=1,068 width=591) (actual time=0.962..28.518 rows=1,266 loops=1)

3. 3.861 21.512 ↓ 1.2 1,266 1

Hash Left Join (cost=39.76..597.43 rows=1,068 width=549) (actual time=0.958..21.512 rows=1,266 loops=1)

  • Hash Cond: (v.type = vt.typeid)
4. 3.743 17.642 ↓ 1.2 1,266 1

Hash Left Join (cost=38.99..593.70 rows=1,068 width=539) (actual time=0.945..17.642 rows=1,266 loops=1)

  • Hash Cond: (v.modelid = vm.modelid)
5. 4.837 13.215 ↓ 1.2 1,266 1

Hash Left Join (cost=16.74..568.64 rows=1,068 width=527) (actual time=0.254..13.215 rows=1,266 loops=1)

  • Hash Cond: (v.brandid = vb.brandid)
6. 8.175 8.298 ↓ 1.2 1,266 1

Bitmap Heap Scan on vehicle v (cost=14.14..563.23 rows=1,068 width=519) (actual time=0.167..8.298 rows=1,266 loops=1)

  • Recheck Cond: ((companyid = 6,995) AND (deleteddate IS NULL))
  • Heap Blocks: exact=439
7. 0.123 0.123 ↓ 1.2 1,266 1

Bitmap Index Scan on uq_vehicle_vehicle_companyid_deleteddate (cost=0.00..13.88 rows=1,068 width=0) (actual time=0.123..0.123 rows=1,266 loops=1)

  • Index Cond: ((companyid = 6,995) AND (deleteddate IS NULL))
8. 0.041 0.080 ↓ 1.0 267 1

Hash (cost=1.67..1.67 rows=266 width=12) (actual time=0.080..0.080 rows=267 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
9. 0.039 0.039 ↓ 1.0 267 1

Seq Scan on vehiclebrand vb (cost=0.00..1.67 rows=266 width=12) (actual time=0.010..0.039 rows=267 loops=1)

10. 0.356 0.684 ↓ 1.0 2,457 1

Hash (cost=13.66..13.66 rows=2,456 width=16) (actual time=0.684..0.684 rows=2,457 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 151kB
11. 0.328 0.328 ↓ 1.0 2,457 1

Seq Scan on vehiclemodel vm (cost=0.00..13.66 rows=2,456 width=16) (actual time=0.008..0.328 rows=2,457 loops=1)

12. 0.004 0.009 ↑ 1.0 14 1

Hash (cost=0.71..0.71 rows=14 width=14) (actual time=0.008..0.009 rows=14 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
13. 0.005 0.005 ↑ 1.0 14 1

Seq Scan on vehicletype vt (cost=0.00..0.71 rows=14 width=14) (actual time=0.004..0.005 rows=14 loops=1)

14. 1.266 1.266 ↓ 0.0 0 1,266

Index Scan using location_pkey on location loc (cost=0.42..1.73 rows=1 width=46) (actual time=0.001..0.001 rows=0 loops=1,266)

  • Index Cond: (locationid = v.homelocationid)
15. 1.255 1.266 ↑ 1.0 1 1,266

Materialize (cost=0.28..1.89 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=1,266)

16. 0.011 0.011 ↑ 1.0 1 1

Index Scan using company_pkey on company c (cost=0.28..1.88 rows=1 width=7) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (companyid = 6,995)
17.          

SubPlan (for Nested Loop Left Join)

18. 3.798 194.964 ↑ 1.0 1 1,266

Limit (cost=2.03..2.04 rows=1 width=16) (actual time=0.153..0.154 rows=1 loops=1,266)

19. 5.064 191.166 ↑ 1.0 1 1,266

Sort (cost=2.03..2.04 rows=1 width=16) (actual time=0.151..0.151 rows=1 loops=1,266)

  • Sort Key: uv.startdate
  • Sort Method: quicksort Memory: 25kB
20. 186.102 186.102 ↑ 1.0 1 1,266

Index Scan using ix_unitvehicle_vehicleid on unitvehicle uv (cost=0.42..2.02 rows=1 width=16) (actual time=0.146..0.147 rows=1 loops=1,266)

  • Index Cond: (vehicleid = v.vehicleid)
  • Filter: (enddate IS NULL)
  • Rows Removed by Filter: 0
21. 2.532 13.926 ↓ 0.0 0 1,266

Limit (cost=0.29..1.46 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=1,266)

22. 11.394 11.394 ↓ 0.0 0 1,266

Index Only Scan Backward using uq_period_vehicleid_measurementdate on period p (cost=0.29..2.63 rows=2 width=8) (actual time=0.009..0.009 rows=0 loops=1,266)

  • Index Cond: (vehicleid = v.vehicleid)
  • Heap Fetches: 70
23. 1.266 220.284 ↓ 0.0 0 1,266

Limit (cost=2.69..2.69 rows=1 width=8) (actual time=0.174..0.174 rows=0 loops=1,266)

24. 3.798 219.018 ↓ 0.0 0 1,266

Sort (cost=2.69..2.70 rows=2 width=8) (actual time=0.173..0.173 rows=0 loops=1,266)

  • Sort Key: up.dateset DESC
  • Sort Method: quicksort Memory: 25kB
25. 215.220 215.220 ↓ 0.0 0 1,266

Index Scan using ix_usageperiods_vehicleid on usageperiods up (cost=0.28..2.68 rows=2 width=8) (actual time=0.170..0.170 rows=0 loops=1,266)

  • Index Cond: (vehicleid = v.vehicleid)
26. 2.532 32.916 ↓ 0.0 0 1,266

Limit (cost=338.14..338.14 rows=1 width=8) (actual time=0.026..0.026 rows=0 loops=1,266)

27. 8.862 30.384 ↓ 0.0 0 1,266

Sort (cost=338.14..338.15 rows=5 width=8) (actual time=0.024..0.024 rows=0 loops=1,266)

  • Sort Key: vcd.next_calibration_date DESC
  • Sort Method: quicksort Memory: 25kB
28. 3.798 21.522 ↓ 0.0 0 1,266

Nested Loop (cost=0.71..338.11 rows=5 width=8) (actual time=0.017..0.017 rows=0 loops=1,266)

29. 17.724 17.724 ↓ 0.0 0 1,266

Index Scan using tacho_parser_data_public_vehicleid_idx1 on tacho_parser_data tpd (cost=0.42..144.71 rows=186 width=4) (actual time=0.014..0.014 rows=0 loops=1,266)

  • Index Cond: (public_vehicleid = v.vehicleid)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using vu_calibration_data_tacho_parser_data_id_idx on vu_calibration_data vcd (cost=0.29..1.04 rows=3 width=12) (never executed)

  • Index Cond: (tacho_parser_data_id = tpd.tacho_parser_data_id)
Planning time : 2.444 ms
Execution time : 23,274.413 ms