explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QMlB

Settings
# exclusive inclusive rows x rows loops node
1. 328.387 37,003.502 ↑ 13.2 731,631 1

Unique (cost=9,404,601.22..10,081,785.84 rows=9,674,066 width=280) (actual time=36,399.468..37,003.502 rows=731,631 loops=1)

2. 1,352.993 36,675.115 ↑ 11.8 818,288 1

Sort (cost=9,404,601.22..9,428,786.39 rows=9,674,066 width=280) (actual time=36,399.468..36,675.115 rows=818,288 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: quicksort Memory: 389,650kB
3. 1,113.153 35,322.122 ↑ 11.8 818,288 1

Hash Right Join (cost=7,915,695.71..8,282,134.29 rows=9,674,066 width=280) (actual time=33,750.983..35,322.122 rows=818,288 loops=1)

  • Hash Cond: (challan_offences_investigate.challan_id = challans.id)
4. 508.289 508.289 ↓ 1.0 5,193,599 1

Seq Scan on challan_offences_investigate (cost=0.00..188,922.64 rows=5,188,488 width=6) (actual time=0.012..508.289 rows=5,193,599 loops=1)

5. 779.136 33,700.680 ↑ 13.2 731,621 1

Hash (cost=7,601,288.57..7,601,288.57 rows=9,674,066 width=278) (actual time=33,700.680..33,700.680 rows=731,621 loops=1)

  • Buckets: 16,777,216 Batches: 1 Memory Usage: 342,872kB
6. 312.809 32,921.544 ↑ 13.2 731,621 1

Hash Left Join (cost=50,472.55..7,601,288.57 rows=9,674,066 width=278) (actual time=99.526..32,921.544 rows=731,621 loops=1)

  • Hash Cond: (challans.officer_id = users.id)
7. 146.594 32,509.914 ↓ 1.0 731,621 1

Hash Left Join (cost=3.56..7,247,055.06 rows=722,224 width=224) (actual time=0.215..32,509.914 rows=731,621 loops=1)

  • Hash Cond: ((challan_vehicle.vehicle_no)::text = (vehicle.regn_no)::text)
8. 40.869 32,363.312 ↓ 1.0 731,621 1

Nested Loop Left Join (cost=2.25..7,244,345.34 rows=722,224 width=223) (actual time=0.198..32,363.312 rows=731,621 loops=1)

9. 0.000 25,006.233 ↓ 1.0 731,621 1

Nested Loop Left Join (cost=1.69..6,584,907.88 rows=722,224 width=140) (actual time=0.157..25,006.233 rows=731,621 loops=1)

10. 580.250 19,300.065 ↓ 1.0 731,621 1

Nested Loop (cost=1.12..5,966,188.86 rows=722,224 width=119) (actual time=0.112..19,300.065 rows=731,621 loops=1)

11. 5,962.495 5,962.495 ↑ 1.0 2,551,464 1

Index Scan using idx_doc_no on challan_accused (cost=0.56..2,334,077.93 rows=2,567,807 width=50) (actual time=0.051..5,962.495 rows=2,551,464 loops=1)

  • Index Cond: ((doc_no)::text = 'No DL'::text)
12. 12,757.320 12,757.320 ↓ 0.0 0 2,551,464

Index Scan using idx_challans_accused_type_id_partial on challans (cost=0.56..1.38 rows=1 width=80) (actual time=0.005..0.005 rows=0 loops=2,551,464)

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

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

  • Index Cond: (challan_id = challans.id)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 0
14. 7,316.210 7,316.210 ↑ 1.0 1 731,621

Index Scan using challan_vehicle_challan_id_idx on challan_vehicle (cost=0.56..0.88 rows=1 width=87) (actual time=0.010..0.010 rows=1 loops=731,621)

  • Index Cond: (challan_id = challans.id)
15. 0.003 0.008 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.005 0.005 ↑ 1.0 5 1

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

17. 7.704 98.821 ↑ 13.4 32,839 1

Hash (cost=36,173.08..36,173.08 rows=439,874 width=62) (actual time=98.821..98.821 rows=32,839 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 6,888kB
18. 6.506 91.117 ↑ 13.4 32,839 1

Merge Right Join (cost=16,362.67..36,173.08 rows=439,874 width=62) (actual time=82.248..91.117 rows=32,839 loops=1)

  • Merge Cond: ((((areas.id)::character varying)::text) = (((users.area_id)::character varying)::text))
19. 1.473 1.706 ↑ 1.0 1,072 1

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

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

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

21. 20.772 82.905 ↑ 2.5 32,839 1

Sort (cost=16,264.56..16,469.73 rows=82,066 width=54) (actual time=80.632..82.905 rows=32,839 loops=1)

  • Sort Key: (((users.area_id)::character varying)::text)
  • Sort Method: quicksort Memory: 5,971kB
22. 14.416 62.133 ↑ 2.5 32,839 1

Merge Left Join (cost=193.83..9,566.13 rows=82,066 width=54) (actual time=3.344..62.133 rows=32,839 loops=1)

  • Merge Cond: ((users.location)::text = (((rto.id)::character varying)::text))
23. 42.635 42.635 ↑ 1.0 32,839 1

Index Scan using users_location_idx on users (cost=0.29..5,843.65 rows=32,839 width=44) (actual time=0.018..42.635 rows=32,839 loops=1)

24. 4.631 5.082 ↓ 17.1 33,868 1

Sort (cost=193.54..198.48 rows=1,977 width=18) (actual time=3.324..5.082 rows=33,868 loops=1)

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

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

Planning time : 4.251 ms
Execution time : 37,089.498 ms