explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lrv8

Settings
# exclusive inclusive rows x rows loops node
1. 335.668 30,941.550 ↑ 13.0 732,592 1

Unique (cost=14,169,035.72..14,837,404.26 rows=9,548,122 width=280) (actual time=30,224.005..30,941.550 rows=732,592 loops=1)

2. 2,046.617 30,605.882 ↑ 11.7 819,331 1

Sort (cost=14,169,035.72..14,192,906.02 rows=9,548,122 width=280) (actual time=30,224.004..30,605.882 rows=819,331 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,848kB
3. 1,280.368 28,559.265 ↑ 11.7 819,331 1

Hash Right Join (cost=11,416,425.28..12,176,271.93 rows=9,548,122 width=280) (actual time=26,704.405..28,559.265 rows=819,331 loops=1)

  • Hash Cond: (challan_offences_investigate.challan_id = challans.id)
4. 577.864 577.864 ↓ 1.0 5,196,902 1

Seq Scan on challan_offences_investigate (cost=0.00..188,922.64 rows=5,188,488 width=6) (actual time=0.007..577.864 rows=5,196,902 loops=1)

5. 614.340 26,701.033 ↑ 13.0 732,582 1

Hash (cost=10,751,786.32..10,751,786.32 rows=9,548,122 width=278) (actual time=26,701.033..26,701.033 rows=732,582 loops=1)

  • Buckets: 524,288 Batches: 32 Memory Usage: 10,730kB
6. 336.302 26,086.693 ↑ 13.0 732,582 1

Hash Left Join (cost=85,237.69..10,751,786.32 rows=9,548,122 width=278) (actual time=749.906..26,086.693 rows=732,582 loops=1)

  • Hash Cond: (challans.officer_id = users.id)
7. 145.831 25,641.937 ↓ 1.0 732,582 1

Hash Left Join (cost=33,112.09..10,399,816.21 rows=714,624 width=224) (actual time=639.834..25,641.937 rows=732,582 loops=1)

  • Hash Cond: ((challan_vehicle.vehicle_no)::text = (vehicle.regn_no)::text)
8. 678.835 25,496.098 ↓ 1.0 732,582 1

Nested Loop Left Join (cost=33,110.78..10,397,135.02 rows=714,624 width=223) (actual time=639.808..25,496.098 rows=732,582 loops=1)

9. 575.118 21,154.353 ↓ 1.0 732,582 1

Nested Loop Left Join (cost=33,110.34..9,660,513.33 rows=714,624 width=140) (actual time=639.780..21,154.353 rows=732,582 loops=1)

10. 1,620.022 16,916.325 ↓ 1.0 732,582 1

Nested Loop (cost=33,109.78..9,016,813.09 rows=714,624 width=119) (actual time=639.749..16,916.325 rows=732,582 loops=1)

11. 4,644.934 5,072.595 ↑ 1.0 2,555,927 1

Bitmap Heap Scan on challan_accused (cost=33,109.22..3,381,950.58 rows=2,564,317 width=50) (actual time=639.575..5,072.595 rows=2,555,927 loops=1)

  • Recheck Cond: ((doc_no)::text = 'No DL'::text)
  • Heap Blocks: exact=713,809
12. 427.661 427.661 ↑ 1.0 2,556,047 1

Bitmap Index Scan on idx_challan_accused_type_challan_id_prtial_docno (cost=0.00..32,468.14 rows=2,564,317 width=0) (actual time=427.661..427.661 rows=2,556,047 loops=1)

13. 10,223.708 10,223.708 ↓ 0.0 0 2,555,927

Index Scan using idx_challans_accused_type_id__isactive on challans (cost=0.56..2.17 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=2,555,927)

  • Index Cond: (((accused_type)::text = (challan_accused.type)::text) AND (id = challan_accused.challan_id) AND (is_active = 1))
14. 3,662.910 3,662.910 ↑ 3.0 1 732,582

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

  • Index Cond: (challan_id = challans.id)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 0
15. 3,662.910 3,662.910 ↑ 1.0 1 732,582

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

  • Index Cond: (challan_id = challans.id)
16. 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
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.174 108.454 ↑ 13.4 32,881 1

Hash (cost=37,847.47..37,847.47 rows=439,327 width=62) (actual time=108.454..108.454 rows=32,881 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 6,891kB
19. 6.479 100.280 ↑ 13.4 32,881 1

Merge Right Join (cost=18,061.68..37,847.47 rows=439,327 width=62) (actual time=91.316..100.280 rows=32,881 loops=1)

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

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

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

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

22. 20.124 92.099 ↑ 2.5 32,881 1

Sort (cost=17,963.56..18,168.47 rows=81,964 width=54) (actual time=89.753..92.099 rows=32,881 loops=1)

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

Merge Right Join (cost=7,975.86..11,274.19 rows=81,964 width=54) (actual time=61.239..71.975 rows=32,881 loops=1)

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

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

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

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

26. 34.881 60.774 ↑ 1.0 32,881 1

Sort (cost=7,782.32..7,864.52 rows=32,881 width=44) (actual time=57.994..60.774 rows=32,881 loops=1)

  • Sort Key: users.location
  • Sort Method: quicksort Memory: 5,000kB
27. 25.893 25.893 ↑ 1.0 32,881 1

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

Planning time : 2.459 ms
Execution time : 31,005.286 ms