explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2SA8

Settings
# exclusive inclusive rows x rows loops node
1. 0.033 432,288.080 ↓ 4.0 8 1

Unique (cost=349,233.18..349,233.94 rows=2 width=3,179) (actual time=432,288.041..432,288.080 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.442 432,288.047 ↓ 5.5 11 1

Sort (cost=349,233.18..349,233.19 rows=2 width=3,179) (actual time=432,288.041..432,288.047 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.772 432,287.605 ↓ 5.5 11 1

Nested Loop Left Join (cost=3.53..349,233.17 rows=2 width=3,179) (actual time=49,402.979..432,287.605 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.440 432,286.580 ↓ 11.0 11 1

Nested Loop Left Join (cost=3.53..349,202.64 rows=1 width=3,164) (actual time=49,402.878..432,286.580 rows=11 loops=1)

  • Join Filter: (challan_in_court.related_court_id = courts.id)
  • Rows Removed by Join Filter: 4389
5. 0.065 432,285.865 ↓ 11.0 11 1

Nested Loop Left Join (cost=3.53..349,171.70 rows=1 width=3,149) (actual time=49,402.801..432,285.865 rows=11 loops=1)

6. 0.060 432,285.734 ↓ 11.0 11 1

Nested Loop Left Join (cost=3.11..349,169.23 rows=1 width=3,077) (actual time=49,402.786..432,285.734 rows=11 loops=1)

7. 0.081 432,285.602 ↓ 8.0 8 1

Nested Loop Left Join (cost=2.68..349,165.02 rows=1 width=3,075) (actual time=49,402.769..432,285.602 rows=8 loops=1)

  • Join Filter: ((vehicle.regn_no)::text = (challan_vehicle.vehicle_no)::text)
  • Rows Removed by Join Filter: 40
8. 0.093 432,285.513 ↓ 8.0 8 1

Nested Loop Left Join (cost=2.68..349,163.71 rows=1 width=3,074) (actual time=49,402.754..432,285.513 rows=8 loops=1)

9. 257.419 432,285.324 ↓ 8.0 8 1

Nested Loop (cost=2.12..349,161.08 rows=1 width=2,456) (actual time=49,402.730..432,285.324 rows=8 loops=1)

  • Join Filter: ((users.location)::text = ((rto.id)::character varying)::text)
  • Rows Removed by Join Filter: 176393
10. 11,987.566 431,675.103 ↓ 176,401.0 176,401 1

Nested Loop Left Join (cost=1.84..349,158.73 rows=1 width=2,427) (actual time=2.110..431,675.103 rows=176,401 loops=1)

  • Join Filter: (ngo_details.id = challans.ngo_id)
  • Rows Removed by Join Filter: 189277822
11. 89,004.488 409,809.081 ↓ 176,401.0 176,401 1

Nested Loop (cost=1.84..349,073.67 rows=1 width=2,382) (actual time=1.874..409,809.081 rows=176,401 loops=1)

  • Join Filter: (challans.officer_id = users.id)
  • Rows Removed by Join Filter: 1787294932
12. 153.070 5,576.006 ↓ 176,401.0 176,401 1

Nested Loop Left Join (cost=1.84..348,127.36 rows=1 width=2,345) (actual time=1.120..5,576.006 rows=176,401 loops=1)

13. 614.187 5,246.639 ↓ 176,297.0 176,297 1

Nested Loop Left Join (cost=1.42..348,124.86 rows=1 width=2,333) (actual time=1.115..5,246.639 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,371.351 4,632.452 ↓ 176,295.0 176,295 1

Nested Loop Left Join (cost=0.99..348,122.37 rows=1 width=2,321) (actual time=1.106..4,632.452 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.006..0.007 rows=
  • Index Cond: ((challan_id = challans.id) AND ((impound_type)::text = 'vehicle'::text))
15. 3,261.101 3,261.101 ↓ 176,295.0 176,295 1

Nested Loop (cost=0.43..348,097.09 rows=1 width=1,884) (actual time=1.091..3,261.101 rows=176,295 loops=1)

  • -> Seq Scan on challan_status (cost=0.00..313173.93 rows=16433 width=113) (actual time=1.061..1229.053 rows=350206 loops=1
  • Filter: (((status)::text <> 'Pending'::text) AND (is_active = 1) AND ((payment_date)::date >= '2018-12-01'::date) AND
  • Rows Removed by Filter: 5068028
  • -> Index Scan using challans_pkey on challans (cost=0.43..2.10 rows=1 width=1775) (actual time=0.005..0.005 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. 315,228.587 315,228.587 ↑ 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.787 rows=10,133 loops=176,401)

18. 9,878.456 9,878.456 ↑ 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.056 rows=1,073 loops=176,401)

19. 352.802 352.802 ↑ 1.0 1 176,401

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

  • Index Cond: (id = 2650)
20. 0.096 0.096 ↑ 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.012..0.012 rows=1 loops=8)

  • Index Cond: (challan_id = challans.id)
21. 0.008 0.008 ↑ 1.0 5 8

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

22. 0.072 0.072 ↑ 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.009..0.009 rows=1 loops=8)

  • Index Cond: (challan_id = challans.id)
23. 0.066 0.066 ↓ 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.006..0.006 rows=0 loops=11)

  • Index Cond: (challan_id = challans.id)
24. 0.275 0.275 ↑ 1.0 399 11

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

25. 0.253 0.253 ↑ 1.0 366 11

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