explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5txu

Settings
# exclusive inclusive rows x rows loops node
1. 333.811 36,451.729 ↑ 13.4 731,807 1

Unique (cost=14,502,065.32..15,186,168.60 rows=9,772,904 width=280) (actual time=35,739.317..36,451.729 rows=731,807 loops=1)

2. 2,024.784 36,117.918 ↑ 11.9 818,487 1

Sort (cost=14,502,065.32..14,526,497.58 rows=9,772,904 width=280) (actual time=35,739.316..36,117.918 rows=818,487 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,608kB
3. 1,329.922 34,093.134 ↑ 11.9 818,487 1

Hash Right Join (cost=11,690,086.19..12,460,746.27 rows=9,772,904 width=280) (actual time=32,151.879..34,093.134 rows=818,487 loops=1)

  • Hash Cond: (challan_offences_investigate.challan_id = challans.id)
4. 614.903 614.903 ↓ 1.0 5,194,943 1

Seq Scan on challan_offences_investigate (cost=0.00..188,922.64 rows=5,188,488 width=6) (actual time=0.010..614.903 rows=5,194,943 loops=1)

5. 641.049 32,148.309 ↑ 13.4 731,797 1

Hash (cost=11,009,799.81..11,009,799.81 rows=9,772,904 width=278) (actual time=32,148.309..32,148.309 rows=731,797 loops=1)

  • Buckets: 524,288 Batches: 32 Memory Usage: 10,724kB
6. 355.485 31,507.260 ↑ 13.4 731,797 1

Hash Left Join (cost=311,310.45..11,009,799.81 rows=9,772,904 width=278) (actual time=915.984..31,507.260 rows=731,797 loops=1)

  • Hash Cond: (challans.officer_id = users.id)
7. 156.312 31,025.206 ↓ 1.0 731,797 1

Hash Left Join (cost=258,299.94..10,649,155.66 rows=722,224 width=224) (actual time=787.679..31,025.206 rows=731,797 loops=1)

  • Hash Cond: ((challan_vehicle.vehicle_no)::text = (vehicle.regn_no)::text)
8. 62.156 30,868.885 ↓ 1.0 731,797 1

Nested Loop Left Join (cost=258,298.63..10,646,445.98 rows=722,224 width=223) (actual time=787.653..30,868.885 rows=731,797 loops=1)

9. 196.991 24,220.556 ↓ 1.0 731,797 1

Nested Loop Left Join (cost=258,298.19..9,901,391.40 rows=722,224 width=140) (actual time=787.608..24,220.556 rows=731,797 loops=1)

10. 0.000 18,900.986 ↓ 1.0 731,797 1

Nested Loop (cost=258,297.63..9,250,756.13 rows=722,224 width=119) (actual time=787.559..18,900.986 rows=731,797 loops=1)

11. 5,661.765 6,234.530 ↑ 1.0 2,552,325 1

Bitmap Heap Scan on challan_accused (cost=258,297.07..3,610,404.20 rows=2,567,807 width=50) (actual time=787.277..6,234.530 rows=2,552,325 loops=1)

  • Recheck Cond: ((doc_no)::text = 'No DL'::text)
  • Heap Blocks: exact=715,051
12. 572.765 572.765 ↑ 1.0 2,558,739 1

Bitmap Index Scan on idx_doc_no (cost=0.00..257,655.12 rows=2,567,807 width=0) (actual time=572.765..572.765 rows=2,558,739 loops=1)

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

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

  • 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,122.579 5,122.579 ↑ 3.0 1 731,797

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

  • Index Cond: (challan_id = challans.id)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 0
15. 6,586.173 6,586.173 ↑ 1.0 1 731,797

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

  • Index Cond: (challan_id = challans.id)
16. 0.006 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: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.003 0.003 ↑ 1.0 5 1

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

18. 9.038 126.569 ↑ 13.6 32,846 1

Hash (cost=38,456.79..38,456.79 rows=447,807 width=62) (actual time=126.569..126.569 rows=32,846 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 6,888kB
19. 6.420 117.531 ↑ 13.6 32,846 1

Merge Right Join (cost=18,289.39..38,456.79 rows=447,807 width=62) (actual time=108.686..117.531 rows=32,846 loops=1)

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

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

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

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

22. 20.222 109.339 ↑ 2.5 32,846 1

Sort (cost=18,191.28..18,400.14 rows=83,546 width=54) (actual time=107.043..109.339 rows=32,846 loops=1)

  • Sort Key: (((users.area_id)::character varying)::text)
  • Sort Method: quicksort Memory: 5,972kB
23. 7.764 89.117 ↑ 2.5 32,846 1

Merge Right Join (cost=7,999.66..11,361.27 rows=83,546 width=54) (actual time=78.498..89.117 rows=32,846 loops=1)

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

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

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

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

26. 36.380 77.770 ↑ 1.0 32,846 1

Sort (cost=7,806.12..7,888.85 rows=33,093 width=44) (actual time=75.097..77.770 rows=32,846 loops=1)

  • Sort Key: users.location
  • Sort Method: quicksort Memory: 4,996kB
27. 41.390 41.390 ↑ 1.0 32,846 1

Seq Scan on users (cost=0.00..5,321.79 rows=33,093 width=44) (actual time=0.011..41.390 rows=32,846 loops=1)

Planning time : 6.298 ms
Execution time : 36,530.411 ms