explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LGk6

Settings
# exclusive inclusive rows x rows loops node
1. 336.000 35,192.647 ↑ 13.0 731,930 1

Unique (cost=14,139,744.90..14,806,835.52 rows=9,529,866 width=280) (actual time=34,472.512..35,192.647 rows=731,930 loops=1)

2. 2,032.594 34,856.647 ↑ 11.6 818,635 1

Sort (cost=14,139,744.90..14,163,569.56 rows=9,529,866 width=280) (actual time=34,472.511..34,856.647 rows=818,635 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,648kB
3. 1,310.401 32,824.053 ↑ 11.6 818,635 1

Hash Right Join (cost=11,391,785.04..12,150,921.66 rows=9,529,866 width=280) (actual time=30,896.320..32,824.053 rows=818,635 loops=1)

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

Seq Scan on challan_offences_investigate (cost=0.00..188,922.64 rows=5,188,488 width=6) (actual time=0.012..620.058 rows=5,195,804 loops=1)

5. 636.115 30,893.594 ↑ 13.0 731,920 1

Hash (cost=10,728,416.40..10,728,416.40 rows=9,529,866 width=278) (actual time=30,893.594..30,893.594 rows=731,920 loops=1)

  • Buckets: 524,288 Batches: 32 Memory Usage: 10,726kB
6. 343.556 30,257.479 ↑ 13.0 731,920 1

Hash Left Join (cost=86,644.30..10,728,416.40 rows=9,529,866 width=278) (actual time=789.297..30,257.479 rows=731,920 loops=1)

  • Hash Cond: (challans.officer_id = users.id)
7. 152.437 29,803.246 ↓ 1.0 731,920 1

Hash Left Join (cost=33,796.21..10,376,314.63 rows=712,358 width=224) (actual time=676.928..29,803.246 rows=731,920 loops=1)

  • Hash Cond: ((challan_vehicle.vehicle_no)::text = (vehicle.regn_no)::text)
8. 164.290 29,650.798 ↓ 1.0 731,920 1

Nested Loop Left Join (cost=33,794.89..10,373,641.94 rows=712,358 width=223) (actual time=676.894..29,650.798 rows=731,920 loops=1)

9. 0.000 23,631.148 ↓ 1.0 731,920 1

Nested Loop Left Join (cost=33,794.46..9,638,764.58 rows=712,358 width=140) (actual time=676.853..23,631.148 rows=731,920 loops=1)

10. 415.558 18,606.137 ↓ 1.0 731,920 1

Nested Loop (cost=33,793.90..8,997,013.76 rows=712,358 width=119) (actual time=676.816..18,606.137 rows=731,920 loops=1)

11. 4,980.856 5,426.019 ↑ 1.0 2,552,912 1

Bitmap Heap Scan on challan_accused (cost=33,793.34..3,375,014.75 rows=2,556,185 width=50) (actual time=676.551..5,426.019 rows=2,552,912 loops=1)

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

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

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

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,912)

  • 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.440 5,123.440 ↑ 3.0 1 731,920

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

  • Index Cond: (challan_id = challans.id)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 0
15. 5,855.360 5,855.360 ↑ 1.0 1 731,920

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

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

Hash (cost=1.15..1.15 rows=5 width=11) (actual time=0.011..0.011 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.004..0.004 rows=5 loops=1)

18. 8.453 110.677 ↑ 13.6 32,869 1

Hash (cost=38,355.01..38,355.01 rows=445,941 width=62) (actual time=110.677..110.677 rows=32,869 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 6,890kB
19. 6.450 102.224 ↑ 13.6 32,869 1

Merge Right Join (cost=18,271.58..38,355.01 rows=445,941 width=62) (actual time=92.654..102.224 rows=32,869 loops=1)

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

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

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

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

22. 20.990 94.015 ↑ 2.5 32,869 1

Sort (cost=18,173.47..18,381.46 rows=83,198 width=54) (actual time=91.045..94.015 rows=32,869 loops=1)

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

Merge Right Join (cost=8,026.73..11,374.42 rows=83,198 width=54) (actual time=61.061..73.025 rows=32,869 loops=1)

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

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

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

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

26. 37.689 61.537 ↑ 1.0 32,869 1

Sort (cost=7,833.19..7,916.52 rows=33,334 width=44) (actual time=57.705..61.537 rows=32,869 loops=1)

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

Seq Scan on users (cost=0.00..5,329.02 rows=33,334 width=44) (actual time=0.006..23.848 rows=32,869 loops=1)

Planning time : 2.323 ms
Execution time : 35,262.096 ms