explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IbOj

Settings
# exclusive inclusive rows x rows loops node
1. 335.457 50,884.417 ↑ 13.0 731,885 1

Unique (cost=14,138,472.30..14,805,562.92 rows=9,529,866 width=280) (actual time=50,169.855..50,884.417 rows=731,885 loops=1)

2. 2,054.973 50,548.960 ↑ 11.6 818,582 1

Sort (cost=14,138,472.30..14,162,296.97 rows=9,529,866 width=280) (actual time=50,169.854..50,548.960 rows=818,582 loops=1)

  • Sort Key: challans.date_time DESC, challans.id, challans.remark, challans.challan_no, challans.challan_place, challans.is_dl_suspend, challans.accused_type, challan_accused.accused_name, challan_accused.is_active, challan_accused.address, challan_status.amount, challan_status.status, challan_status.payment_date, challan_status.payment_type, users.name, users.user_type, users.is_arto, rto.state_code, rto.rto_name, areas.name, challan_vehicle.id, challan_vehicle.owner_name, challan_vehicle.permanent_address, vehicle.regn_no, challan_offences_investigate.status
  • Sort Method: external merge Disk: 224,632kB
3. 1,358.730 48,493.987 ↑ 11.6 818,582 1

Hash Right Join (cost=11,390,512.45..12,149,649.07 rows=9,529,866 width=280) (actual time=46,496.878..48,493.987 rows=818,582 loops=1)

  • Hash Cond: (challan_offences_investigate.challan_id = challans.id)
4. 642.016 642.016 ↓ 1.0 5,195,619 1

Seq Scan on challan_offences_investigate (cost=0.00..188,922.64 rows=5,188,488 width=6) (actual time=0.014..642.016 rows=5,195,619 loops=1)

5. 682.334 46,493.241 ↑ 13.0 731,875 1

Hash (cost=10,727,143.80..10,727,143.80 rows=9,529,866 width=278) (actual time=46,493.241..46,493.241 rows=731,875 loops=1)

  • Buckets: 524,288 Batches: 32 Memory Usage: 10,725kB
6. 367.200 45,810.907 ↑ 13.0 731,875 1

Hash Left Join (cost=86,017.30..10,727,143.80 rows=9,529,866 width=278) (actual time=821.143..45,810.907 rows=731,875 loops=1)

  • Hash Cond: (challans.officer_id = users.id)
7. 159.081 45,317.728 ↓ 1.0 731,875 1

Hash Left Join (cost=33,791.47..10,375,665.04 rows=712,358 width=224) (actual time=693.453..45,317.728 rows=731,875 loops=1)

  • Hash Cond: ((challan_vehicle.vehicle_no)::text = (vehicle.regn_no)::text)
8. 557.482 45,158.632 ↓ 1.0 731,875 1

Nested Loop Left Join (cost=33,790.16..10,372,992.35 rows=712,358 width=223) (actual time=693.421..45,158.632 rows=731,875 loops=1)

9. 227.156 26,304.275 ↓ 1.0 731,875 1

Nested Loop Left Join (cost=33,789.72..9,638,115.18 rows=712,358 width=140) (actual time=692.955..26,304.275 rows=731,875 loops=1)

10. 1,578.907 20,953.994 ↓ 1.0 731,875 1

Nested Loop (cost=33,789.16..8,996,365.02 rows=712,358 width=119) (actual time=692.911..20,953.994 rows=731,875 loops=1)

11. 6,128.790 6,611.527 ↑ 1.0 2,552,712 1

Bitmap Heap Scan on challan_accused (cost=33,788.60..3,375,010.01 rows=2,556,185 width=50) (actual time=692.590..6,611.527 rows=2,552,712 loops=1)

  • Recheck Cond: ((doc_no)::text = 'No DL'::text)
  • Heap Blocks: exact=713,088
12. 482.737 482.737 ↑ 1.0 2,552,716 1

Bitmap Index Scan on idx_challan_accused_doc_no_no_dl (cost=0.00..33,149.55 rows=2,556,185 width=0) (actual time=482.737..482.737 rows=2,552,716 loops=1)

  • Index Cond: ((doc_no)::text = 'No DL'::text)
13. 12,763.560 12,763.560 ↓ 0.0 0 2,552,712

Index Scan using idx_challans_accused_type_id_partial on challans (cost=0.56..2.17 rows=1 width=80) (actual time=0.005..0.005 rows=0 loops=2,552,712)

  • Index Cond: (((accused_type)::text = (challan_accused.type)::text) AND (id = challan_accused.challan_id))
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 0
14. 5,123.125 5,123.125 ↑ 3.0 1 731,875

Index Scan using challan_status_index on challan_status (cost=0.56..0.81 rows=3 width=25) (actual time=0.007..0.007 rows=1 loops=731,875)

  • Index Cond: (challan_id = challans.id)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 0
15. 18,296.875 18,296.875 ↑ 1.0 1 731,875

Index Scan using idx_challan_vehicle_challan_id on challan_vehicle (cost=0.43..1.00 rows=1 width=87) (actual time=0.025..0.025 rows=1 loops=731,875)

  • Index Cond: (challan_id = challans.id)
16. 0.011 0.015 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.004 0.004 ↑ 1.0 5 1

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

18. 8.855 125.979 ↑ 13.4 32,866 1

Hash (cost=37,916.70..37,916.70 rows=440,281 width=62) (actual time=125.979..125.979 rows=32,866 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 6,890kB
19. 6.520 117.124 ↑ 13.4 32,866 1

Merge Right Join (cost=18,087.98..37,916.70 rows=440,281 width=62) (actual time=108.070..117.124 rows=32,866 loops=1)

  • Merge Cond: ((((areas.id)::character varying)::text) = (((users.area_id)::character varying)::text))
20. 1.550 1.821 ↑ 1.0 1,072 1

Sort (cost=98.11..100.79 rows=1,072 width=16) (actual time=1.647..1.821 rows=1,072 loops=1)

  • Sort Key: (((areas.id)::character varying)::text)
  • Sort Method: quicksort Memory: 125kB
21. 0.271 0.271 ↑ 1.0 1,072 1

Seq Scan on areas (cost=0.00..44.16 rows=1,072 width=16) (actual time=0.019..0.271 rows=1,072 loops=1)

22. 20.066 108.783 ↑ 2.5 32,866 1

Sort (cost=17,989.86..18,195.22 rows=82,142 width=54) (actual time=106.420..108.783 rows=32,866 loops=1)

  • Sort Key: (((users.area_id)::character varying)::text)
  • Sort Method: quicksort Memory: 5,975kB
23. 7.779 88.717 ↑ 2.5 32,866 1

Merge Right Join (cost=7,979.23..11,284.68 rows=82,142 width=54) (actual time=78.135..88.717 rows=32,866 loops=1)

  • Merge Cond: ((((rto.id)::character varying)::text) = (users.location)::text)
24. 3.092 3.606 ↑ 1.0 1,977 1

Sort (cost=193.54..198.48 rows=1,977 width=18) (actual time=3.406..3.606 rows=1,977 loops=1)

  • Sort Key: (((rto.id)::character varying)::text)
  • Sort Method: quicksort Memory: 203kB
25. 0.514 0.514 ↑ 1.0 1,977 1

Seq Scan on rto (cost=0.00..85.31 rows=1,977 width=18) (actual time=0.013..0.514 rows=1,977 loops=1)

26. 34.412 77.332 ↑ 1.0 32,866 1

Sort (cost=7,785.69..7,867.97 rows=32,911 width=44) (actual time=74.723..77.332 rows=32,866 loops=1)

  • Sort Key: users.location
  • Sort Method: quicksort Memory: 4,998kB
27. 42.920 42.920 ↑ 1.0 32,866 1

Seq Scan on users (cost=0.00..5,316.33 rows=32,911 width=44) (actual time=0.011..42.920 rows=32,866 loops=1)

Planning time : 6.543 ms
Execution time : 50,969.020 ms