explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oLnT

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 20.035 42,562.473 ↑ 127,411.5 723 1

Nested Loop (cost=1,794,246.08..55,647,323.08 rows=92,118,500 width=349) (actual time=11,510.964..42,562.473 rows=723 loops=1)

2. 4.909 41,804.978 ↑ 127.4 723 1

Nested Loop Left Join (cost=1,794,245.82..38,835,776.57 rows=92,118 width=289) (actual time=11,494.562..41,804.978 rows=723 loops=1)

3. 4.642 39,998.353 ↑ 97.9 723 1

Hash Join (cost=1,794,245.57..32,970,850.02 rows=70,747 width=273) (actual time=11,490.964..39,998.353 rows=723 loops=1)

  • Hash Cond: (al.id = (min(al_1.id)))
4. 5.095 34,599.174 ↑ 194.6 727 1

Nested Loop (cost=889,494.78..32,065,568.31 rows=141,494 width=277) (actual time=6,096.406..34,599.174 rows=727 loops=1)

5. 6.114 34,390.667 ↑ 37.5 759 1

Nested Loop Left Join (cost=889,494.53..4,251,024.38 rows=28,488 width=192) (actual time=6,092.064..34,390.667 rows=759 loops=1)

6. 5.838 34,367.096 ↑ 37.5 759 1

Nested Loop Left Join (cost=889,494.39..4,242,321.61 rows=28,488 width=160) (actual time=6,091.934..34,367.096 rows=759 loops=1)

7. 8.562 6,067.256 ↑ 37.5 759 1

Merge Left Join (cost=889,494.13..892,702.32 rows=28,488 width=112) (actual time=6,046.071..6,067.256 rows=759 loops=1)

  • Merge Cond: ((al.vehicle_no)::text = (vg.veh_no)::text)
8. 8.645 6,053.870 ↑ 37.5 759 1

Merge Join (cost=888,474.05..890,931.16 rows=28,488 width=80) (actual time=6,042.319..6,053.870 rows=759 loops=1)

  • Merge Cond: ((vr.vehicle_no)::text = (al.vehicle_no)::text)
9. 28.887 46.803 ↑ 1.1 2,621 1

Sort (cost=1,377.91..1,385.25 rows=2,933 width=26) (actual time=44.919..46.803 rows=2,621 loops=1)

  • Sort Key: vr.vehicle_no
  • Sort Method: quicksort Memory: 261kB
10. 3.926 17.916 ↓ 1.0 2,974 1

Hash Left Join (cost=254.28..1,209.00 rows=2,933 width=26) (actual time=4.338..17.916 rows=2,974 loops=1)

  • Hash Cond: (vr.transporter_id = tm.id)
11. 9.941 13.864 ↓ 1.0 2,974 1

Bitmap Heap Scan on wcl_master_vehicle_registration vr (cost=243.02..1,164.66 rows=2,933 width=15) (actual time=4.174..13.864 rows=2,974 loops=1)

  • Filter: registration_status
12. 3.923 3.923 ↓ 1.6 4,553 1

Bitmap Index Scan on idx_vehstatus (cost=0.00..242.28 rows=2,933 width=0) (actual time=3.923..3.923 rows=4,553 loops=1)

  • Index Cond: (registration_status = true)
13. 0.066 0.126 ↓ 1.3 72 1

Hash (cost=6.56..6.56 rows=57 width=19) (actual time=0.126..0.126 rows=72 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 4kB
14. 0.060 0.060 ↓ 1.3 72 1

Seq Scan on wcl_transport_master tm (cost=0.00..6.56 rows=57 width=19) (actual time=0.008..0.060 rows=72 loops=1)

15. 3.877 5,998.422 ↑ 43.1 759 1

Sort (cost=887,096.03..887,177.88 rows=32,737 width=54) (actual time=5,997.356..5,998.422 rows=759 loops=1)

  • Sort Key: al.vehicle_no
  • Sort Method: quicksort Memory: 131kB
16. 5,994.545 5,994.545 ↑ 43.1 759 1

Seq Scan on wcl_alert_log al (cost=0.00..884,640.98 rows=32,737 width=54) (actual time=5,988.830..5,994.545 rows=759 loops=1)

  • Filter: ((alert_type_id = 3) AND ((alert_time)::timestamp without time zone >= '2019-03-10 00:00:00+05:30'::timestamp with time zone) AND ((alert_time)::timestamp without time zone <= '2019-03-10 23:59:00+05:30'::timestamp with time zone))
  • Rows Removed by Filter: 6218386
17. 2.415 4.824 ↓ 2.8 981 1

Sort (cost=1,020.08..1,020.96 rows=353 width=42) (actual time=3.739..4.824 rows=981 loops=1)

  • Sort Key: vg.veh_no
  • Sort Method: quicksort Memory: 53kB
18. 0.617 2.409 ↓ 1.1 382 1

Hash Left Join (cost=57.95..1,005.14 rows=353 width=42) (actual time=0.294..2.409 rows=382 loops=1)

  • Hash Cond: (vg.subarea_id = s.id)
19. 1.575 1.728 ↓ 1.1 382 1

Bitmap Heap Scan on wcl_veh_source_destination vg (cost=50.13..968.07 rows=353 width=14) (actual time=0.201..1.728 rows=382 loops=1)

  • Recheck Cond: status
20. 0.153 0.153 ↓ 1.1 382 1

Bitmap Index Scan on idx_veharea_status (cost=0.00..50.04 rows=353 width=0) (actual time=0.153..0.153 rows=382 loops=1)

21. 0.036 0.064 ↑ 1.0 42 1

Hash (cost=4.36..4.36 rows=42 width=36) (actual time=0.064..0.064 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 2kB
22. 0.028 0.028 ↑ 1.0 42 1

Seq Scan on index_subarea s (cost=0.00..4.36 rows=42 width=36) (actual time=0.017..0.028 rows=42 loops=1)

23. 28,294.002 28,294.002 ↑ 1.0 1 759

Function Scan on wcl_dblink_trip_test l (cost=0.26..117.76 rows=1 width=80) (actual time=37.278..37.278 rows=1 loops=759)

  • Filter: (((vehicle_name)::text = (al.vehicle_no)::text) AND (((date_part('year'::text, start_time) = date_part('year'::text, ((al.alert_time)::date)::timestamp without time zone)) AND (date_part('year'::text, end_time) = date_part('year'::text, ((al.alert_time)::date)::timestamp without time zone))) OR (date_part('year'::text, end_time) IS NULL) OR (date_part('year'::text, start_time) IS NULL)))
  • Rows Removed by Filter: 0
24. 17.457 17.457 ↑ 1.0 1 759

Index Scan using idx_ahq_id on index_ahq a (cost=0.14..0.23 rows=1 width=36) (actual time=0.020..0.023 rows=1 loops=759)

  • Index Cond: (id = al.ahq_id)
25. 203.412 203.412 ↑ 17.0 1 759

Function Scan on wcl_halt_status_unique hs (cost=0.25..975.25 rows=17 width=128) (actual time=0.267..0.268 rows=1 loops=759)

  • Filter: (((al.vehicle_no)::text = (hs_vehicle_no)::text) AND (((hs_out_time)::timestamp without time zone - (hs_in_time)::timestamp without time zone) >= '00:30:00'::interval))
26. 0.246 5,394.537 ↓ 3.8 755 1

Hash (cost=904,734.29..904,734.29 rows=200 width=4) (actual time=5,394.537..5,394.537 rows=755 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 27kB
27. 0.580 5,394.291 ↓ 3.8 755 1

HashAggregate (cost=904,718.29..904,734.29 rows=200 width=4) (actual time=5,394.051..5,394.291 rows=755 loops=1)

28. 0.856 5,393.711 ↑ 4.3 755 1

HashAggregate (cost=904,161.71..904,448.19 rows=3,274 width=34) (actual time=5,393.497..5,393.711 rows=755 loops=1)

29. 5,392.855 5,392.855 ↑ 43.1 759 1

Seq Scan on wcl_alert_log al_1 (cost=0.00..903,916.21 rows=32,734 width=34) (actual time=5,387.039..5,392.855 rows=759 loops=1)

  • Filter: ((longitude > 50::double precision) AND (alert_type_id = 3) AND ((alert_time)::timestamp without time zone >= '2019-03-10 00:00:00+05:30'::timestamp with time zone) AND ((alert_time)::timestamp without time zone <= '2019-03-10 23:59:00+05:30'::timestamp with time zone))
  • Rows Removed by Filter: 6218386
30. 1,801.716 1,801.716 ↓ 0.0 0 723

Function Scan on weighbridge_gpswbloaded_unloadedstatus si (cost=0.26..82.75 rows=5 width=64) (actual time=2.492..2.492 rows=0 loops=723)

  • Filter: ((v_no)::text = (al.vehicle_no)::text)
31. 737.460 737.460 ↑ 1,000.0 1 723

Function Scan on wcl_veh_nearbylocation ar (cost=0.26..80.25 rows=1,000 width=64) (actual time=1.019..1.020 rows=1 loops=723)