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
  • <= '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, cha
  • 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. 775.388 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)

  • -> 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=1762
14. 1,739.184 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)

  • -> 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=
  • Index Cond: ((challan_id = challans.id) AND ((impound_type)::text = 'vehicle'::text))
15. 4,185.223 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)

  • -> Seq Scan on challan_status (cost=0.00..313173.93 rows=16433 width=113) (actual time=1.073..1516.241 rows=350200 loops=1
  • Filter: (((status)::text <> 'Pending'::text) AND (is_active = 1) AND ((payment_date)::date >= '2018-12-01'::date) AND
  • Rows Removed by Filter: 5067297
  • -> Index Scan using challans_pkey on challans (cost=0.43..2.10 rows=1 width=1769) (actual time=0.007..0.007 rows=1 loops=3
  • 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
  • Index Cond: (challan_id = challans.id)
  • Filter: ((type)::text = (challans.accused_type)::text)
  • Rows Removed by Filter: 3
16. 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))
17. 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)

18. 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)

19. 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)
20. 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)
21. 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)

22. 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)
23. 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)
24. 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)

25. 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