explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bgOI

Settings
# exclusive inclusive rows x rows loops node
1. 330.921 43,648.930 ↑ 13.1 732,114 1

Unique (cost=12,110,717.54..12,784,181.66 rows=9,620,916 width=280) (actual time=43,109.936..43,648.930 rows=732,114 loops=1)

2. 1,238.163 43,318.009 ↑ 11.7 818,838 1

Sort (cost=12,110,717.54..12,134,769.83 rows=9,620,916 width=280) (actual time=43,109.935..43,318.009 rows=818,838 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: quicksort Memory: 389,911kB
3. 1,036.797 42,079.846 ↑ 11.7 818,838 1

Hash Right Join (cost=10,628,909.00..10,994,799.87 rows=9,620,916 width=280) (actual time=40,659.959..42,079.846 rows=818,838 loops=1)

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

Seq Scan on challan_offences_investigate (cost=0.00..188,922.64 rows=5,188,488 width=6) (actual time=0.013..434.766 rows=5,196,502 loops=1)

5. 641.674 40,608.283 ↑ 13.1 732,104 1

Hash (cost=10,316,229.23..10,316,229.23 rows=9,620,916 width=278) (actual time=40,608.283..40,608.283 rows=732,104 loops=1)

  • Buckets: 16,777,216 Batches: 1 Memory Usage: 343,014kB
6. 279.932 39,966.609 ↑ 13.1 732,104 1

Hash Left Join (cost=5,151,249.80..10,316,229.23 rows=9,620,916 width=278) (actual time=24,040.748..39,966.609 rows=732,104 loops=1)

  • Hash Cond: (challans.officer_id = users.id)
7. 140.529 39,586.263 ↓ 1.0 732,104 1

Hash Left Join (cost=5,100,628.64..9,963,582.46 rows=714,624 width=224) (actual time=23,938.714..39,586.263 rows=732,104 loops=1)

  • Hash Cond: ((challan_vehicle.vehicle_no)::text = (vehicle.regn_no)::text)
8. 22.647 39,445.725 ↓ 1.0 732,104 1

Nested Loop Left Join (cost=5,100,627.33..9,960,901.28 rows=714,624 width=223) (actual time=23,938.682..39,445.725 rows=732,104 loops=1)

9. 293.073 34,298.350 ↓ 1.0 732,104 1

Nested Loop Left Join (cost=5,100,626.89..9,223,685.43 rows=714,624 width=140) (actual time=23,938.650..34,298.350 rows=732,104 loops=1)

10. 2,236.459 30,344.757 ↓ 1.0 732,104 1

Hash Join (cost=5,100,626.33..8,579,889.89 rows=714,624 width=119) (actual time=23,938.605..30,344.757 rows=732,104 loops=1)

  • Hash Cond: ((challan_accused.challan_id = challans.id) AND ((challan_accused.type)::text = (challans.accused_type)::text))
11. 4,427.509 4,869.450 ↑ 1.0 2,553,649 1

Bitmap Heap Scan on challan_accused (cost=33,079.53..3,381,920.90 rows=2,564,317 width=50) (actual time=647.267..4,869.450 rows=2,553,649 loops=1)

  • Recheck Cond: ((doc_no)::text = 'No DL'::text)
  • Heap Blocks: exact=713,366
12. 441.941 441.941 ↑ 1.0 2,553,653 1

Bitmap Index Scan on idx_challan_accused_type_challan_id_prtial_docno (cost=0.00..32,438.46 rows=2,564,317 width=0) (actual time=441.941..441.941 rows=2,553,653 loops=1)

13. 5,370.659 23,238.848 ↓ 1.0 15,072,510 1

Hash (cost=4,541,103.99..4,541,103.99 rows=15,041,223 width=80) (actual time=23,238.848..23,238.848 rows=15,072,510 loops=1)

  • Buckets: 16,777,216 Batches: 1 Memory Usage: 1,742,014kB
14. 17,868.189 17,868.189 ↓ 1.0 15,073,215 1

Seq Scan on challans (cost=0.00..4,541,103.99 rows=15,041,223 width=80) (actual time=0.040..17,868.189 rows=15,073,215 loops=1)

  • Filter: (is_active = 1)
  • Rows Removed by Filter: 54,827
15. 3,660.520 3,660.520 ↑ 3.0 1 732,104

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

  • Index Cond: (challan_id = challans.id)
  • Filter: (is_active = 1)
  • Rows Removed by Filter: 0
16. 5,124.728 5,124.728 ↑ 1.0 1 732,104

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

  • Index Cond: (challan_id = challans.id)
17. 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
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.463 100.414 ↑ 13.0 32,878 1

Hash (cost=36,779.93..36,779.93 rows=425,884 width=62) (actual time=100.414..100.414 rows=32,878 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 6,891kB
20. 6.290 91.951 ↑ 13.0 32,878 1

Merge Right Join (cost=17,599.07..36,779.93 rows=425,884 width=62) (actual time=83.381..91.951 rows=32,878 loops=1)

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

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

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

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

23. 20.116 83.941 ↑ 2.4 32,878 1

Sort (cost=17,500.96..17,699.60 rows=79,456 width=54) (actual time=81.782..83.941 rows=32,878 loops=1)

  • Sort Key: (((users.area_id)::character varying)::text)
  • Sort Method: quicksort Memory: 5,976kB
24. 7.860 63.825 ↑ 2.4 32,878 1

Merge Right Join (cost=7,836.07..11,034.08 rows=79,456 width=54) (actual time=53.205..63.825 rows=32,878 loops=1)

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

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

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

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

27. 35.143 52.455 ↓ 1.0 32,878 1

Sort (cost=7,642.53..7,721.62 rows=31,634 width=44) (actual time=49.878..52.455 rows=32,878 loops=1)

  • Sort Key: users.location
  • Sort Method: quicksort Memory: 4,999kB
28. 17.312 17.312 ↓ 1.0 32,878 1

Seq Scan on users (cost=0.00..5,278.02 rows=31,634 width=44) (actual time=0.006..17.312 rows=32,878 loops=1)

Planning time : 2.539 ms
Execution time : 43,770.588 ms