explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VvHT

Settings
# exclusive inclusive rows x rows loops node
1. 332.936 33,853.087 ↑ 12.7 732,887 1

Unique (cost=13,850,233.37..14,502,649.54 rows=9,320,231 width=280) (actual time=33,173.383..33,853.087 rows=732,887 loops=1)

2. 1,973.998 33,520.151 ↑ 11.4 819,630 1

Sort (cost=13,850,233.37..13,873,533.95 rows=9,320,231 width=280) (actual time=33,173.381..33,520.151 rows=819,630 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,920kB
3. 1,270.075 31,546.153 ↑ 11.4 819,630 1

Hash Right Join (cost=11,164,566.57..11,906,656.49 rows=9,320,231 width=280) (actual time=29,679.640..31,546.153 rows=819,630 loops=1)

  • Hash Cond: (challan_offences_investigate.challan_id = challans.id)
4. 599.991 599.991 ↓ 1.0 5,197,080 1

Seq Scan on challan_offences_investigate (cost=0.00..188,922.64 rows=5,188,488 width=6) (actual time=0.020..599.991 rows=5,197,080 loops=1)

5. 619.439 29,676.087 ↑ 12.7 732,877 1

Hash (cost=10,515,791.07..10,515,791.07 rows=9,320,231 width=278) (actual time=29,676.087..29,676.087 rows=732,877 loops=1)

  • Buckets: 524,288 Batches: 32 Memory Usage: 10,732kB
6. 312.900 29,056.648 ↑ 12.7 732,877 1

Hash Left Join (cost=84,281.62..10,515,791.07 rows=9,320,231 width=278) (actual time=796.613..29,056.648 rows=732,877 loops=1)

  • Hash Cond: (challans.officer_id = users.id)
7. 152.526 28,616.214 ↓ 1.0 732,877 1

Hash Left Join (cost=32,268.64..10,171,059.52 rows=699,327 width=224) (actual time=667.370..28,616.214 rows=732,877 loops=1)

  • Hash Cond: ((challan_vehicle.vehicle_no)::text = (vehicle.regn_no)::text)
8. 432.680 28,463.670 ↓ 1.0 732,877 1

Nested Loop Left Join (cost=32,267.32..10,168,435.70 rows=699,327 width=223) (actual time=667.329..28,463.670 rows=732,877 loops=1)

9. 403.887 23,633.728 ↓ 1.0 732,877 1

Nested Loop Left Join (cost=32,266.89..9,449,672.39 rows=699,327 width=140) (actual time=667.289..23,633.728 rows=732,877 loops=1)

10. 68.756 18,832.579 ↓ 1.0 732,877 1

Nested Loop (cost=32,266.33..8,820,098.19 rows=699,327 width=119) (actual time=667.234..18,832.579 rows=732,877 loops=1)

11. 5,552.730 5,977.363 ↓ 1.0 2,557,292 1

Bitmap Heap Scan on challan_accused (cost=32,265.77..3,311,866.65 rows=2,490,921 width=50) (actual time=666.973..5,977.363 rows=2,557,292 loops=1)

  • Recheck Cond: ((doc_no)::text = 'No DL'::text)
  • Heap Blocks: exact=714,049
12. 424.633 424.633 ↓ 1.0 2,557,350 1

Bitmap Index Scan on idx_challan_accused_type_challan_id_prtial_docno (cost=0.00..31,643.03 rows=2,490,921 width=0) (actual time=424.633..424.633 rows=2,557,350 loops=1)

13. 12,786.460 12,786.460 ↓ 0.0 0 2,557,292

Index Scan using idx_challans_accused_type_id__isactive on challans (cost=0.56..2.18 rows=1 width=80) (actual time=0.005..0.005 rows=0 loops=2,557,292)

  • Index Cond: (((accused_type)::text = (challan_accused.type)::text) AND (id = challan_accused.challan_id) AND (is_active = 1))
14. 4,397.262 4,397.262 ↑ 3.0 1 732,877

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

  • Index Cond: (challan_id = challans.id)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 0
15. 4,397.262 4,397.262 ↑ 1.0 1 732,877

Index Scan using idx_challan_vehicle_challan_id on challan_vehicle (cost=0.43..1.00 rows=1 width=87) (actual time=0.006..0.006 rows=1 loops=732,877)

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

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

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

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

18. 8.749 127.534 ↑ 13.3 32,882 1

Hash (cost=37,770.90..37,770.90 rows=438,218 width=62) (actual time=127.534..127.534 rows=32,882 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 6,891kB
19. 6.504 118.785 ↑ 13.3 32,882 1

Merge Right Join (cost=18,035.01..37,770.90 rows=438,218 width=62) (actual time=109.956..118.785 rows=32,882 loops=1)

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

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

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

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

22. 19.763 110.500 ↑ 2.5 32,882 1

Sort (cost=17,936.90..18,141.29 rows=81,757 width=54) (actual time=108.306..110.500 rows=32,882 loops=1)

  • Sort Key: (((users.area_id)::character varying)::text)
  • Sort Method: quicksort Memory: 5,977kB
23. 7.717 90.737 ↑ 2.5 32,882 1

Merge Right Join (cost=7,975.86..11,265.91 rows=81,757 width=54) (actual time=80.416..90.737 rows=32,882 loops=1)

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

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

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

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

26. 35.893 79.457 ↓ 1.0 32,882 1

Sort (cost=7,782.32..7,864.52 rows=32,881 width=44) (actual time=77.027..79.457 rows=32,882 loops=1)

  • Sort Key: users.location
  • Sort Method: quicksort Memory: 5,000kB
27. 43.564 43.564 ↓ 1.0 32,882 1

Seq Scan on users (cost=0.00..5,315.43 rows=32,881 width=44) (actual time=0.017..43.564 rows=32,882 loops=1)

Planning time : 5.499 ms
Execution time : 33,931.664 ms