explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4R3j : Optimization for: Optimization for: plan #4Xaa; plan #lK8z

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 20.356 361.833 ↓ 3.0 3 1

Nested Loop Left Join (cost=14,728.81..27,519.34 rows=1 width=192) (actual time=291.991..361.833 rows=3 loops=1)

  • Join Filter: (p.property_id = lp.property_id)
  • Rows Removed by Join Filter: 243121
2.          

CTE lp

3. 47.826 181.674 ↑ 1.0 84,531 1

WindowAgg (cost=12,825.99..14,727.94 rows=84,531 width=38) (actual time=121.262..181.674 rows=84,531 loops=1)

4. 68.170 133.848 ↑ 1.0 84,531 1

Sort (cost=12,825.99..13,037.32 rows=84,531 width=38) (actual time=121.253..133.848 rows=84,531 loops=1)

  • Sort Key: lp_1.property_id, lp_1.date_created DESC, lp_1.sg_license_plate_zid
  • Sort Method: external merge Disk: 3616kB
5. 55.847 65.678 ↑ 1.0 84,531 1

Hash Left Join (cost=4.41..4,586.31 rows=84,531 width=38) (actual time=0.088..65.678 rows=84,531 loops=1)

  • Hash Cond: (lp_1.license_state_code = (st.code)::text)
6. 9.757 9.757 ↑ 1.0 84,531 1

Seq Scan on sg_license_plate lp_1 (cost=0.00..3,328.31 rows=84,531 width=46) (actual time=0.003..9.757 rows=84,531 loops=1)

7. 0.048 0.074 ↑ 1.0 107 1

Hash (cost=3.07..3.07 rows=107 width=3) (actual time=0.074..0.074 rows=107 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
8. 0.026 0.026 ↑ 1.0 107 1

Seq Scan on states st (cost=0.00..3.07 rows=107 width=3) (actual time=0.003..0.026 rows=107 loops=1)

9. 0.004 81.179 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.87..10,884.15 rows=1 width=144) (actual time=62.112..81.179 rows=3 loops=1)

10. 0.003 81.175 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.58..10,881.83 rows=1 width=141) (actual time=62.110..81.175 rows=3 loops=1)

11. 0.011 81.169 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.29..10,879.51 rows=1 width=138) (actual time=62.107..81.169 rows=3 loops=1)

12. 4.569 81.125 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.00..10,877.19 rows=1 width=135) (actual time=62.086..81.125 rows=3 loops=1)

  • Join Filter: (p.vehicle_model_id = mdl.vehicle_model_id)
  • Rows Removed by Join Filter: 33018
  • Filter: (COALESCE(mk.vehicle_make_description, mdl.vehicle_model_description, p.serial_number_or_vin) IS NOT NULL)
13. 0.769 73.940 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.00..10,467.53 rows=1 width=131) (actual time=58.979..73.940 rows=3 loops=1)

  • Join Filter: (p.vehicle_make_id = mk.vehicle_make_id)
  • Rows Removed by Join Filter: 6936
14. 0.104 72.646 ↓ 3.0 3 1

Nested Loop Left Join (cost=0.00..10,381.49 rows=1 width=130) (actual time=58.881..72.646 rows=3 loops=1)

  • Join Filter: (p.state_code = (s.code)::text)
  • Rows Removed by Join Filter: 321
15. 0.016 72.518 ↓ 3.0 3 1

Nested Loop (cost=0.00..10,376.82 rows=1 width=130) (actual time=58.818..72.518 rows=3 loops=1)

  • Join Filter: (p.property_sub_category_id = psc.property_sub_category_id)
  • Rows Removed by Join Filter: 21
16. 0.012 69.379 ↓ 3.0 3 1

Nested Loop (cost=0.00..10,214.97 rows=1 width=138) (actual time=58.064..69.379 rows=3 loops=1)

  • Join Filter: (p.property_category_id = pc.property_category_id)
  • Rows Removed by Join Filter: 9
17. 69.265 69.265 ↑ 2.3 3 1

Seq Scan on sg_property p (cost=0.00..10,197.95 rows=7 width=146) (actual time=57.994..69.265 rows=3 loops=1)

  • Filter: (serial_number_or_vin = '1FMDK06155GA58563'::text)
  • Rows Removed by Filter: 246313
18. 0.006 0.102 ↑ 1.2 4 3

Materialize (cost=0.00..16.51 rows=5 width=8) (actual time=0.008..0.034 rows=4 loops=3)

19. 0.096 0.096 ↑ 1.2 4 1

Seq Scan on sg_cd_property_category_code pc (cost=0.00..16.48 rows=5 width=8) (actual time=0.018..0.096 rows=4 loops=1)

  • Filter: (property_category_desc = ANY ('{Automotive,Motorcycle/Minibike,"Other Motor Vehicle","Motorcycles and Mini-bikes",Automotive}'::text[]))
  • Rows Removed by Filter: 322
20. 3.123 3.123 ↓ 1.1 8 3

Seq Scan on sg_cd_property_sub_category psc (cost=0.00..161.76 rows=7 width=8) (actual time=0.475..1.041 rows=8 loops=3)

  • Filter: (property_sub_category_desc = ANY ('{"Passenger Veh,Tk,Cars,Etc","Motorcycle,Minibikes,Mopeds",Truck,Trailr}'::text[]))
  • Rows Removed by Filter: 5176
21. 0.024 0.024 ↑ 1.0 107 3

Seq Scan on states s (cost=0.00..3.07 rows=107 width=3) (actual time=0.002..0.008 rows=107 loops=3)

22. 0.525 0.525 ↑ 1.0 2,313 3

Seq Scan on sg_cd_vehicle_make_code mk (cost=0.00..57.13 rows=2,313 width=17) (actual time=0.002..0.175 rows=2,313 loops=3)

23. 2.616 2.616 ↑ 1.0 11,007 3

Seq Scan on sg_cd_vehicle_model_code mdl (cost=0.00..272.07 rows=11,007 width=20) (actual time=0.002..0.872 rows=11,007 loops=3)

24. 0.033 0.033 ↑ 1.0 1 3

Index Scan using sg_cd_general_lookup_code_glc_id_key on sg_cd_general_lookup_code color_1 (cost=0.29..2.31 rows=1 width=19) (actual time=0.009..0.011 rows=1 loops=3)

  • Index Cond: (p.vehicle_color_1_glc_id = glc_id)
25. 0.003 0.003 ↓ 0.0 0 3

Index Scan using sg_cd_general_lookup_code_glc_id_key on sg_cd_general_lookup_code color_2 (cost=0.29..2.31 rows=1 width=19) (actual time=0.001..0.001 rows=0 loops=3)

  • Index Cond: (p.vehicle_color_2_glc_id = glc_id)
26. 0.000 0.000 ↓ 0.0 0 3

Index Scan using sg_cd_general_lookup_code_glc_id_key on sg_cd_general_lookup_code color (cost=0.29..2.31 rows=1 width=19) (actual time=0.000..0.000 rows=0 loops=3)

  • Index Cond: (p.vehicle_color_glc_id = glc_id)
27. 260.298 260.298 ↓ 191.6 81,041 3

CTE Scan on lp (cost=0.00..1,901.95 rows=423 width=56) (actual time=40.428..86.766 rows=81,041 loops=3)

  • Filter: (rank = 1)
  • Rows Removed by Filter: 3490
Planning time : 5.201 ms
Execution time : 363.984 ms