explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RVVo

Settings
# exclusive inclusive rows x rows loops node
1. 332.950 61,960.416 ↑ 13.7 731,043 1

Unique (cost=14,883,996.02..15,587,250.25 rows=10,046,489 width=280) (actual time=61,227.174..61,960.416 rows=731,043 loops=1)

2. 2,150.034 61,627.466 ↑ 12.3 817,515 1

Sort (cost=14,883,996.02..14,909,112.24 rows=10,046,489 width=280) (actual time=61,227.174..61,627.466 rows=817,515 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,320kB
3. 1,505.621 59,477.432 ↑ 12.3 817,515 1

Hash Right Join (cost=12,001,194.04..12,783,532.40 rows=10,046,489 width=280) (actual time=57,348.337..59,477.432 rows=817,515 loops=1)

  • Hash Cond: (challan_offences_investigate.challan_id = challans.id)
4. 626.438 626.438 ↓ 1.0 5,190,831 1

Seq Scan on challan_offences_investigate (cost=0.00..188,922.64 rows=5,188,488 width=6) (actual time=0.014..626.438 rows=5,190,831 loops=1)

5. 570.531 57,345.373 ↑ 13.7 731,033 1

Hash (cost=11,301,864.15..11,301,864.15 rows=10,046,489 width=278) (actual time=57,345.373..57,345.373 rows=731,033 loops=1)

  • Buckets: 524,288 Batches: 32 Memory Usage: 10,717kB
6. 383.201 56,774.842 ↑ 13.7 731,033 1

Hash Left Join (cost=5,580,294.66..11,301,864.15 rows=10,046,489 width=278) (actual time=28,411.854..56,774.842 rows=731,033 loops=1)

  • Hash Cond: (challans.officer_id = users.id)
7. 153.010 56,266.683 ↑ 1.0 731,033 1

Hash Left Join (cost=5,530,056.13..10,936,116.74 rows=752,847 width=224) (actual time=28,285.145..56,266.683 rows=731,033 loops=1)

  • Hash Cond: ((challan_vehicle.vehicle_no)::text = (vehicle.regn_no)::text)
8. 433.367 56,113.665 ↑ 1.0 731,033 1

Nested Loop Left Join (cost=5,530,054.81..10,933,292.19 rows=752,847 width=223) (actual time=28,285.097..56,113.665 rows=731,033 loops=1)

9. 340.970 46,176.869 ↑ 1.0 731,033 1

Nested Loop Left Join (cost=5,530,054.25..10,036,514.08 rows=752,847 width=140) (actual time=28,285.047..46,176.869 rows=731,033 loops=1)

10. 4,787.194 39,987.635 ↑ 1.0 731,033 1

Hash Join (cost=5,530,053.69..9,359,171.99 rows=752,847 width=119) (actual time=28,285.001..39,987.635 rows=731,033 loops=1)

  • Hash Cond: ((challan_accused.challan_id = challans.id) AND ((challan_accused.type)::text = (challans.accused_type)::text))
11. 7,139.354 7,652.873 ↑ 1.0 2,548,524 1

Bitmap Heap Scan on challan_accused (cost=255,882.92..3,703,272.71 rows=2,670,755 width=50) (actual time=733.698..7,652.873 rows=2,548,524 loops=1)

  • Recheck Cond: ((doc_no)::text = 'No DL'::text)
  • Heap Blocks: exact=711,502
12. 513.519 513.519 ↑ 1.0 2,548,798 1

Bitmap Index Scan on idx_doc_no (cost=0.00..255,215.23 rows=2,670,755 width=0) (actual time=513.519..513.519 rows=2,548,798 loops=1)

  • Index Cond: ((doc_no)::text = 'No DL'::text)
13. 4,339.398 27,547.568 ↑ 1.0 15,051,665 1

Hash (cost=4,547,312.57..4,547,312.57 rows=15,239,606 width=80) (actual time=27,547.568..27,547.568 rows=15,051,665 loops=1)

  • Buckets: 2,097,152 Batches: 16 Memory Usage: 116,998kB
14. 23,208.170 23,208.170 ↑ 1.0 15,052,370 1

Seq Scan on challans (cost=0.00..4,547,312.57 rows=15,239,606 width=80) (actual time=0.164..23,208.170 rows=15,052,370 loops=1)

  • Filter: (is_active = 1)
  • Rows Removed by Filter: 54,728
15. 5,848.264 5,848.264 ↑ 3.0 1 731,033

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

  • Index Cond: (challan_id = challans.id)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 0
16. 9,503.429 9,503.429 ↑ 1.0 1 731,033

Index Scan using challan_vehicle_challan_id_idx on challan_vehicle (cost=0.56..1.16 rows=1 width=87) (actual time=0.013..0.013 rows=1 loops=731,033)

  • Index Cond: (challan_id = challans.id)
17. 0.005 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
18. 0.003 0.003 ↑ 1.0 5 1

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

19. 8.472 124.958 ↑ 12.9 32,811 1

Hash (cost=36,519.76..36,519.76 rows=422,116 width=62) (actual time=124.958..124.958 rows=32,811 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 6,885kB
20. 6.610 116.486 ↑ 12.9 32,811 1

Merge Right Join (cost=17,508.46..36,519.76 rows=422,116 width=62) (actual time=106.033..116.486 rows=32,811 loops=1)

  • Merge Cond: ((((areas.id)::character varying)::text) = (((users.area_id)::character varying)::text))
21. 1.521 1.729 ↑ 1.0 1,072 1

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

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

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

23. 22.089 108.147 ↑ 2.4 32,811 1

Sort (cost=17,410.35..17,607.23 rows=78,753 width=54) (actual time=104.455..108.147 rows=32,811 loops=1)

  • Sort Key: (((users.area_id)::character varying)::text)
  • Sort Method: quicksort Memory: 5,967kB
24. 7.857 86.058 ↑ 2.4 32,811 1

Merge Right Join (cost=7,835.85..11,005.74 rows=78,753 width=54) (actual time=72.584..86.058 rows=32,811 loops=1)

  • Merge Cond: ((((rto.id)::character varying)::text) = (users.location)::text)
25. 3.135 3.552 ↑ 1.0 1,977 1

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

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

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

27. 38.886 74.649 ↓ 1.0 32,811 1

Sort (cost=7,642.31..7,721.39 rows=31,632 width=44) (actual time=69.235..74.649 rows=32,811 loops=1)

  • Sort Key: users.location
  • Sort Method: quicksort Memory: 4,992kB
28. 35.763 35.763 ↓ 1.0 32,811 1

Seq Scan on users (cost=0.00..5,277.96 rows=31,632 width=44) (actual time=0.006..35.763 rows=32,811 loops=1)

Planning time : 2.333 ms
Execution time : 62,033.996 ms