explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7rP

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 464,158.059 ↓ 4.0 8 1

Unique (cost=349,233.18..349,233.93 rows=2 width=3,173) (actual time=464,158.019..464,158.059 rows=8 loops=1)

  • challans.state_permit_date, challans.national_permit_date, challans.goods_description, challans.tax_upto, challans.insurance_upto, challans.fit_upto, challans.gvw_no, challans.seat_cap, challans."dlVehicleClass", challans.dl_suspend_sent, challans.dl_suspend_refer_no, challans.dl_suspend_response, challans.location_id, challans.challan_source_type, challans.settled_amount, challans.challan_settled, challans.overloading, challans.excess_load, challans.violation_id, challans.device_details, challan_accused.accused_name, challan_accused.accused_father_name, challan_accused.contact_no, challan_accused.email, challan_accused.address, challan_accused.is_active, challan_accused.img, challan_accused.doc_no, challan_accused.type, challan_accused.remark, challan_accused.valid_upto, challan_accused.issue_date, challan_accused.dl_no, challan_accused.dl_issued_by, challan_accused.cctv_image1, challan_accused.cctv_image2, challan_accused.rto_code, veh_impound.impound_type, veh_impound.is_active, doc_impound.impound_type, doc_impound.is_active, challan_status.amount, challan_status.payment_date, challan_status.receipt_no, challan_status.offline_receipt_no, challan_status.payment_type, challan_status.offline_receipt, challan_status.payment_by, challan_status.payment_source, challan_status.transaction_id, challan_status.payment_gateway, challan_status.is_acf, challan_status.acf_amount, challan_status.acf_receipt_no COLLATE "C", users.name, users.user_type, users.is_arto, users.location, ngo_details.name, ngo_details.belt_no, ngo_details.address, rto.rto_name, rto.state_code, rto.rto_add, areas.id, areas.name, challan_vehicle.id, challan_vehicle.owner_name, challan_vehicle.chasis_no, challan_vehicle.vehicle_class, challan_vehicle.vehicle_type, challan_vehicle.insurance_upto, challan_vehicle.fitness_upto, challan_vehicle.permit_upto, challan_vehicle.engine_no, vehicle.regn_no, challan_offences_investigate.status, challan_in_court.id, challan_in_court.is_active, challan_in_court.forword_date, challan_in_court.related_court_id, challan_in_court.release_order_img, challan_in_court.updation_date, challan_in_court.court_receipt_no, challan_in_court.action_by, challan_in_court.dispatch_no, courts.name
  • <= '2018-12-23'::date) AND (date_part('month'::text, date_time) = '12'::double precision))
2. 0.512 464,158.022 ↓ 5.5 11 1

Sort (cost=349,233.18..349,233.18 rows=2 width=3,173) (actual time=464,158.018..464,158.022 rows=11 loops=1)

  • Sort Key: challans.date_time DESC, challan_status.status DESC, challans.id, challans.remark, challans.challan_no, challans.lat_long, challans.officer_id, challans.challan_for, challans.accused_type, challans.challan_doc_no, challans.job_id, challans.witness_1, challans.witness_2, challans.rc_no, challans.vehicle_img, challans.receipt_img, challans.source, challans.destination, challans.hearing_date, challans.challan_source, challans.app_build, challans.app_version, challans.old_rc_no, challans.remark_image, challans.challan_accused_type, challans.emp_id, challans.data_entry_id, challans.book_no, challans.form_no, challans.challan_place, challans.ngo_id, challans.is_downloaded, challans.payment_received_by, challans.witness_3, challans.otp_code, challans.mobile_no, challans.otp_time, challans.location, challans.book_type, challans.old_officer_id, challans."bind_empId", challans.lok_adalat_admin_id, challans.record_room_admin_id, challans.gender, challans.dl_details, challans.updated_at, challans.rta_id, challans.is_dl_suspend, challans.dl_number, challans.acc_name, challans.acc_address, challans.acc_father_name, challans.age, challans.team_id, challans.badge_no, challans.issuing_auth, challans.state_permit_no, challans.national_permit_no,
  • Sort Method: quicksort Memory: 42kB
3. 0.831 464,157.510 ↓ 5.5 11 1

Nested Loop Left Join (cost=3.53..349,233.17 rows=2 width=3,173) (actual time=50,335.357..464,157.510 rows=11 loops=1)

  • Join Filter: (((areas.id)::character varying)::text = ((users.area_id)::character varying)::text)
  • Rows Removed by Join Filter: 4024
4. 0.458 464,156.448 ↓ 11.0 11 1

Nested Loop Left Join (cost=3.53..349,202.63 rows=1 width=3,158) (actual time=50,335.249..464,156.448 rows=11 loops=1)

  • Join Filter: (challan_in_court.related_court_id = courts.id)
  • Rows Removed by Join Filter: 4389
5. 0.071 464,155.682 ↓ 11.0 11 1

Nested Loop Left Join (cost=3.53..349,171.70 rows=1 width=3,143) (actual time=50,335.155..464,155.682 rows=11 loops=1)

6. 0.059 464,155.534 ↓ 11.0 11 1

Nested Loop Left Join (cost=3.11..349,169.23 rows=1 width=3,071) (actual time=50,335.136..464,155.534 rows=11 loops=1)

7. 0.079 464,155.363 ↓ 8.0 8 1

Nested Loop Left Join (cost=2.68..349,165.02 rows=1 width=3,069) (actual time=50,335.111..464,155.363 rows=8 loops=1)

  • Join Filter: ((vehicle.regn_no)::text = (challan_vehicle.vehicle_no)::text)
  • Rows Removed by Join Filter: 40
8. 0.096 464,155.268 ↓ 8.0 8 1

Nested Loop Left Join (cost=2.68..349,163.71 rows=1 width=3,068) (actual time=50,335.094..464,155.268 rows=8 loops=1)

9. 245.023 464,154.964 ↓ 8.0 8 1

Nested Loop (cost=2.12..349,161.08 rows=1 width=2,450) (actual time=50,335.045..464,154.964 rows=8 loops=1)

  • Join Filter: ((users.location)::text = ((rto.id)::character varying)::text)
  • Rows Removed by Join Filter: 176393
10. 13,499.347 463,380.738 ↓ 176,401.0 176,401 1

Nested Loop Left Join (cost=1.84..349,158.73 rows=1 width=2,421) (actual time=2.230..463,380.738 rows=176,401 loops=1)

  • Join Filter: (ngo_details.id = challans.ngo_id)
  • Rows Removed by Join Filter: 189277822
11. 91,761.992 439,297.331 ↓ 176,401.0 176,401 1

Nested Loop (cost=1.84..349,073.67 rows=1 width=2,376) (actual time=1.911..439,297.331 rows=176,401 loops=1)

  • Join Filter: (challans.officer_id = users.id)
  • Rows Removed by Join Filter: 1787294932
12. 205.317 7,081.409 ↓ 176,401.0 176,401 1

Nested Loop Left Join (cost=1.84..348,127.36 rows=1 width=2,339) (actual time=1.195..7,081.409 rows=176,401 loops=1)

13. 246.503 6,699.795 ↓ 176,297.0 176,297 1

Nested Loop Left Join (cost=1.42..348,124.86 rows=1 width=2,327) (actual time=1.192..6,699.795 rows=176,297 loops=1)

14. 152.529 5,924.407 ↓ 176,295.0 176,295 1

Nested Loop Left Join (cost=0.99..348,122.37 rows=1 width=2,315) (actual time=1.171..5,924.407 rows=176,295 loops=1)

15. 217.582 4,185.223 ↓ 176,295.0 176,295 1

Nested Loop (cost=0.43..348,097.09 rows=1 width=1,878) (actual time=1.130..4,185.223 rows=176,295 loops=1)

16. 1,516.241 1,516.241 ↓ 21.3 350,200 1

Seq Scan on challan_status (cost=0.00..313,173.93 rows=16,433 width=113) (actual time=1.073..1,516.241 rows=350,200 loops=1)

  • Filter: (((status)::text <> 'Pending'::text) AND (is_active = 1) AND ((payment_date)::date >= '2018-12-01'::date) AND ((payment_date)::date <= '2018-12-23'::date))
  • Rows Removed by Filter: 5067297
17. 2,451.400 2,451.400 ↑ 1.0 1 350,200

Index Scan using challans_pkey on challans (cost=0.43..2.10 rows=1 width=1,769) (actual time=0.007..0.007 rows=1 loops=350,200)

  • Index Cond: (id = challan_status.challan_id)
  • Filter: ((traffic_police = 1) AND (is_active = 1) AND ((date_time)::date >= '2018-12-01'::date) AND ((date_time)::date
  • Rows Removed by Filter: 0
18. 1,586.655 1,586.655 ↑ 6.0 1 176,295

Index Scan using challan_accused_index on challan_accused (cost=0.56..25.09 rows=6 width=441) (actual time=0.008..0.009 rows=1 loops=176,295)

  • Index Cond: (challan_id = challans.id)
  • Filter: ((type)::text = (challans.accused_type)::text)
  • Rows Removed by Filter: 3
19. 528.885 528.885 ↓ 0.0 0 176,295

Index Scan using impound_index on impound veh_impound (cost=0.42..2.46 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=176,295)

  • Index Cond: ((challan_id = challans.id) AND ((impound_type)::text = 'vehicle'::text))
20. 176.297 176.297 ↓ 0.0 0 176,297

Index Scan using impound_index on impound doc_impound (cost=0.42..2.46 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=176,297)

  • Index Cond: ((challan_id = challans.id) AND ((impound_type)::text = 'document'::text))
21. 340,453.930 340,453.930 ↑ 1.0 10,133 176,401

Seq Scan on users (cost=0.00..616.99 rows=10,133 width=41) (actual time=0.001..1.930 rows=10,133 loops=176,401)

22. 10,584.060 10,584.060 ↑ 1.0 1,073 176,401

Seq Scan on ngo_details (cost=0.00..50.19 rows=1,073 width=49) (actual time=0.001..0.060 rows=1,073 loops=176,401)

23. 529.203 529.203 ↑ 1.0 1 176,401

Index Scan using rto_pkey on rto (cost=0.28..2.31 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=176,401)

  • Index Cond: (id = 2650)
24. 0.208 0.208 ↑ 1.0 1 8

Index Scan using challan_vehicle_challan_id_idx on challan_vehicle (cost=0.56..2.59 rows=1 width=622) (actual time=0.026..0.026 rows=1 loops=8)

  • Index Cond: (challan_id = challans.id)
25. 0.016 0.016 ↑ 1.0 5 8

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

26. 0.112 0.112 ↑ 4.0 1 8

Index Scan using challan_offences_investigate_index on challan_offences_investigate (cost=0.43..4.09 rows=4 width=6) (actual time=0.014..0.014 rows=1 loops=8)

  • Index Cond: (challan_id = challans.id)
27. 0.077 0.077 ↓ 0.0 0 11

Index Scan using challan_in_court_index on challan_in_court (cost=0.42..2.44 rows=1 width=76) (actual time=0.007..0.007 rows=0 loops=11)

  • Index Cond: (challan_id = challans.id)
28. 0.308 0.308 ↑ 1.0 399 11

Seq Scan on courts (cost=0.00..17.97 rows=399 width=19) (actual time=0.002..0.028 rows=399 loops=11)

29. 0.231 0.231 ↑ 1.0 366 11

Seq Scan on areas (cost=0.00..14.98 rows=366 width=19) (actual time=0.002..0.021 rows=366 loops=11)

Planning time : 21.206 ms
Execution time : 464,158.890 ms