explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 15Rls

Settings
# exclusive inclusive rows x rows loops node
1. 14,023.818 354,786.600 ↑ 10.4 7,426,784 1

Unique (cost=97,016,376.64..122,096,480.72 rows=77,169,551 width=2,512) (actual time=328,787.497..354,786.600 rows=7,426,784 loops=1)

  • rows=7509271 loops=1)
  • Planning time: 15.717 ms
  • Execution time: 357058.984 ms
2. 180,597.388 340,762.782 ↑ 8.0 9,673,356 1

Sort (cost=97,016,376.64..97,209,300.52 rows=77,169,551 width=2,512) (actual time=328,787.495..340,762.782 rows=9,673,356 loops=1)

  • Sort Key: challans.date_time DESC, challan_status.status DESC, challans.id, challans.remark, challans.challan_no, challans.lat_long, challans.officer_id, challans.c
  • Sort Method: external merge Disk: 7435528kB
3. 5,067.505 160,165.394 ↑ 8.0 9,673,356 1

Hash Left Join (cost=22,141,909.86..39,127,769.74 rows=77,169,551 width=2,512) (actual time=81,570.191..160,165.394 rows=9,673,356 loops=1)

  • Hash Cond: (((users.area_id)::character varying)::text = ((areas.id)::character varying)::text)
4. 3,464.051 155,097.699 ↑ 2.5 9,673,356 1

Hash Left Join (cost=22,141,862.80..27,121,296.29 rows=24,077,863 width=2,516) (actual time=81,569.991..155,097.699 rows=9,673,356 loops=1)

  • Hash Cond: ((challan_vehicle.vehicle_no)::text = (vehicle.regn_no)::text)
5. 25,057.250 151,633.639 ↑ 2.5 9,673,356 1

Merge Right Join (cost=22,141,861.49..27,030,999.54 rows=24,077,863 width=2,515) (actual time=81,569.961..151,633.639 rows=9,673,356 loops=1)

  • Merge Cond: (challan_vehicle.challan_id = challans.id)
  • -> Index Scan using challan_vehicle_challan_id_idx on challan_vehicle (cost=0.56..1683406.69 rows=7281918 width=31) (actual time=0.010..14384.23
6. 11,056.454 126,576.389 ↑ 2.5 9,673,356 1

Materialize (cost=22,141,860.93..24,569,108.03 rows=24,077,863 width=2,488) (actual time=81,569.914..126,576.389 rows=9,673,356 loops=1)

7. 3,648.930 115,519.935 ↑ 2.5 9,673,356 1

Merge Left Join (cost=22,141,860.93..24,508,913.38 rows=24,077,863 width=2,488) (actual time=81,569.904..115,519.935 rows=9,673,356 loops=1)

  • Merge Cond: (challans.id = challan_offences_investigate.challan_id)
8. 96,540.505 107,708.426 ↑ 3.2 7,510,555 1

Merge Left Join (cost=22,141,858.58..23,846,702.38 rows=24,077,863 width=2,486) (actual time=81,569.870..107,708.426 rows=7,510,555 loops=1)

  • Merge Cond: (challans.id = challan_in_court.challan_id)
  • -> Merge Left Join (cost=22076554.58..23677292.13 rows=24077863 width=2425) (actual time=81326.274..104734.658 rows=7510547 lo
  • Merge Cond: (challans.id = challan_status.challan_id)
  • -> Merge Left Join (cost=22076554.02..22232757.35 rows=24077863 width=2342) (actual time=81326.230..90288.932 rows=75105
  • Merge Cond: (challans.id = veh_impound.challan_id)
  • -> Sort (cost=22024761.40..22084956.06 rows=24077863 width=2330) (actual time=81106.769..84207.710 rows=7510281 lo
  • Sort Key: challans.id
  • Sort Method: external sort Disk: 5272240kB
  • -> Hash Left Join (cost=6916.84..5199682.45 rows=24077863 width=2330) (actual time=37.875..63130.402 rows=75
  • Hash Cond: (challans.officer_id = users.id)
  • -> Hash Left Join (cost=1142.55..4424828.57 rows=7478753 width=2274) (actual time=5.403..59898.906 row
  • Hash Cond: ((challans.emp_id)::text = (user_detail.emp_id)::text)
  • -> Merge Left Join (cost=2.68..4161969.70 rows=7471281 width=2258) (actual time=0.132..56471.328
  • Merge Cond: (challans.id = doc_impound.challan_id)
  • -> Merge Left Join (cost=0.99..4059173.33 rows=7471281 width=2246) (actual time=0.114..535
  • Merge Cond: (challans.id = challan_accused.challan_id)
  • Join Filter: ((challan_accused.type)::text = (challans.accused_type)::text)
  • Rows Removed by Join Filter: 15833488
  • -> Index Scan using challans_pkey on challans (cost=0.43..1874436.28 rows=7471281 wi
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 38956
  • -> Index Scan using challan_accused_index on challan_accused (cost=0.56..1355031.90
  • -> Index Scan using impound_index on impound doc_impound (cost=0.43..54127.31 rows=944936
  • Index Cond: ((impound_type)::text = 'document'::text)
9. 5.209 5.209 ↑ 1.0 14,870 1

Hash (cost=654.78..654.78 rows=14,926 width=25) (actual time=5.209..5.209 rows=14,870 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 975kB
  • -> Seq Scan on user_detail (cost=0.00..654.78 rows=14926 width=25) (actual time=0.002..2.8
10. 32.245 32.245 ↑ 3.2 15,398 1

Hash (cost=4,163.13..4,163.13 rows=49,574 width=60) (actual time=32.245..32.245 rows=15,398 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1922kB
  • -> Merge Left Join (cost=184.76..4163.13 rows=49574 width=60) (actual time=3.364..27.682 rows=15
  • Merge Cond: ((users.location)::text = (((rto.id)::character varying)::text))
  • -> Index Scan using users_location_idx on users (cost=0.29..1880.22 rows=15398 width=50) (
  • -> Sort (cost=184.47..189.22 rows=1900 width=18) (actual time=3.338..4.271 rows=16568 loop
  • Sort Key: (((rto.id)::character varying)::text)
  • Sort Method: quicksort Memory: 197kB
  • -> Seq Scan on rto (cost=0.00..81.00 rows=1900 width=18) (actual time=0.007..0.448 r
11. 317.417 317.417 ↓ 1.1 343,650 1

Sort (cost=51,792.54..52,591.93 rows=319,756 width=16) (actual time=219.449..317.417 rows=343,650 loops=1)

  • Sort Key: veh_impound.challan_id
  • Sort Method: quicksort Memory: 27322kB
  • -> Index Scan using impound_impound_type_idx on impound veh_impound (cost=0.43..22556.27 rows=319756 width=1
  • Index Cond: ((impound_type)::text = 'vehicle'::text)
12. 10,372.286 10,372.286 ↓ 1.0 7,517,453 1

Materialize (cost=0.56..575,262.33 rows=7,433,809 width=87) (actual time=0.029..10,372.286 rows=7,517,453 loops=1)

  • -> Index Scan using challan_status_index on challan_status (cost=0.56..556677.81 rows=7433809 width=87) (actual ti
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 1850306
13. 317.657 440.764 ↓ 1.1 414,157 1

Sort (cost=65,303.92..66,284.06 rows=392,056 width=65) (actual time=243.586..440.764 rows=414,157 loops=1)

  • Sort Key: challan_in_court.challan_id
  • Sort Method: quicksort Memory: 53099kB
14. 122.908 123.107 ↑ 1.0 391,808 1

Hash Left Join (cost=45.25..28,880.54 rows=392,056 width=65) (actual time=0.218..123.107 rows=391,808 loops=1)

  • Hash Cond: (challan_in_court.related_court_id = courts.id)
  • -> Seq Scan on challan_in_court (cost=0.00..15629.68 rows=392056 width=49) (actual time=0.006..27.732 rows=391808
15. 0.070 0.199 ↑ 1.0 580 1

Hash (cost=26.40..26.40 rows=580 width=20) (actual time=0.199..0.199 rows=580 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
16. 0.129 0.129 ↑ 1.0 580 1

Seq Scan on courts (cost=0.00..26.40 rows=580 width=20) (actual time=0.005..0.129 rows=580 loops=1)

17. 4,162.579 4,162.579 ↓ 1.0 3,979,873 1

Materialize (cost=0.43..183,835.84 rows=3,831,851 width=6) (actual time=0.028..4,162.579 rows=3,979,873 loops=1)

  • -> Index Scan using challan_offences_investigate_index on challan_offences_investigate (cost=0.43..174256.21 rows=3831851 widt
18. 0.003 0.009 ↑ 1.0 5 1

Hash (cost=1.15..1.15 rows=5 width=11) (actual time=0.009..0.009 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.006 0.006 ↑ 1.0 5 1

Seq Scan on vehicle (cost=0.00..1.15 rows=5 width=11) (actual time=0.004..0.006 rows=5 loops=1)

20. 0.098 0.190 ↑ 1.0 641 1

Hash (cost=26.23..26.23 rows=641 width=4) (actual time=0.190..0.190 rows=641 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
21. 0.092 0.092 ↑ 1.0 641 1

Seq Scan on areas (cost=0.00..26.23 rows=641 width=4) (actual time=0.010..0.092 rows=641 loops=1)