explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PAnL : Optimization for: mv_reservations - empty customers

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.048 759.838 ↑ 1.0 100 1

Limit (cost=4.69..12,051.23 rows=100 width=4,021) (actual time=18.113..759.838 rows=100 loops=1)

2. 11.764 759.790 ↑ 37.5 100 1

Nested Loop Left Join (cost=4.69..452,111.20 rows=3,753 width=4,021) (actual time=18.112..759.790 rows=100 loops=1)

3. 0.091 747.426 ↑ 37.5 100 1

Nested Loop Left Join (cost=4.26..422,359.38 rows=3,753 width=4,311) (actual time=17.230..747.426 rows=100 loops=1)

4. 0.166 746.935 ↑ 37.5 100 1

Nested Loop Left Join (cost=3.84..396,294.00 rows=3,753 width=4,281) (actual time=17.221..746.935 rows=100 loops=1)

5. 0.132 746.469 ↑ 37.5 100 1

Nested Loop Left Join (cost=3.41..368,440.25 rows=3,753 width=4,182) (actual time=17.215..746.469 rows=100 loops=1)

6. 0.074 746.037 ↑ 37.5 100 1

Nested Loop (cost=3.13..365,535.96 rows=3,753 width=4,150) (actual time=17.208..746.037 rows=100 loops=1)

7. 0.088 745.063 ↑ 37.5 100 1

Nested Loop Left Join (cost=2.70..337,682.21 rows=3,753 width=4,040) (actual time=17.188..745.063 rows=100 loops=1)

8. 0.164 744.275 ↑ 37.5 100 1

Nested Loop (cost=1.99..311,979.68 rows=3,753 width=3,979) (actual time=17.183..744.275 rows=100 loops=1)

9. 0.112 743.911 ↑ 26.9 100 1

Nested Loop Left Join (cost=1.84..310,970.04 rows=2,691 width=3,962) (actual time=17.177..743.911 rows=100 loops=1)

10. 0.132 742.809 ↑ 27.0 99 1

Nested Loop Left Join (cost=1.41..293,949.19 rows=2,675 width=3,954) (actual time=17.151..742.809 rows=99 loops=1)

  • Join Filter: (r.travellingcustomer_id = pi.id)
  • Rows Removed by Join Filter: 99
11. 0.169 742.677 ↑ 27.0 99 1

Nested Loop Left Join (cost=1.41..293,909.04 rows=2,675 width=2,602) (actual time=17.144..742.677 rows=99 loops=1)

  • Join Filter: (r.bookingcustomer_id = ci.id)
  • Rows Removed by Join Filter: 99
12. 0.110 742.508 ↑ 27.0 99 1

Nested Loop Left Join (cost=1.41..293,868.90 rows=2,675 width=655) (actual time=17.131..742.508 rows=99 loops=1)

13. 0.123 742.101 ↑ 27.0 99 1

Nested Loop (cost=1.12..285,679.58 rows=2,675 width=315) (actual time=17.128..742.101 rows=99 loops=1)

14. 0.175 741.681 ↑ 27.0 99 1

Nested Loop (cost=0.85..284,494.53 rows=2,675 width=205) (actual time=17.121..741.681 rows=99 loops=1)

15. 740.615 740.615 ↑ 27.0 99 1

Index Scan using md_abstract_reservation_pkey on md_abstract_reservation r (cost=0.42..265,036.09 rows=2,675 width=193) (actual time=17.093..740.615 rows=99 loops=1)

  • Filter: ((selectedstartdate >= '2018-04-11 10:00:00+00'::timestamp with time zone) AND (selectedstartdate <= '2020-05-11 18:45:00+00'::timestamp with time zone) AND ((status)::text = 'ACCEPTED'::text))
  • Rows Removed by Filter: 801165
16. 0.891 0.891 ↑ 1.0 1 99

Index Scan using md_offer_pkey on md_offer f (cost=0.42..7.26 rows=1 width=12) (actual time=0.009..0.009 rows=1 loops=99)

  • Index Cond: (id = r.selectedoffer_id)
17. 0.297 0.297 ↑ 1.0 1 99

Index Scan using md_vehicletype_pkey on md_vehicle_type vt (cost=0.27..0.43 rows=1 width=110) (actual time=0.003..0.003 rows=1 loops=99)

  • Index Cond: (id = f.vehicletype_id)
18. 0.297 0.297 ↑ 1.0 1 99

Index Scan using idx_vehicle_id on md_vehicle v (cost=0.29..3.05 rows=1 width=348) (actual time=0.003..0.003 rows=1 loops=99)

  • Index Cond: (r.vehicle_id = id)
19. 0.000 0.000 ↑ 1.0 1 99

Materialize (cost=0.00..0.03 rows=1 width=1,951) (actual time=0.000..0.000 rows=1 loops=99)

20. 0.001 0.003 ↑ 1.0 1 1

Subquery Scan on ci (cost=0.00..0.02 rows=1 width=1,951) (actual time=0.003..0.003 rows=1 loops=1)

21. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=1,951) (actual time=0.002..0.002 rows=1 loops=1)

22. 0.000 0.000 ↑ 1.0 1 99

Materialize (cost=0.00..0.03 rows=1 width=1,360) (actual time=0.000..0.000 rows=1 loops=99)

23. 0.003 0.004 ↑ 1.0 1 1

Subquery Scan on pi (cost=0.00..0.02 rows=1 width=1,360) (actual time=0.003..0.004 rows=1 loops=1)

24. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=1,951) (actual time=0.001..0.001 rows=1 loops=1)

25. 0.990 0.990 ↑ 6.0 1 99

Index Scan using idx_60741d0f53c674ee on md_item i (cost=0.43..6.30 rows=6 width=16) (actual time=0.009..0.010 rows=1 loops=99)

  • Index Cond: (f.id = offer_id)
  • Filter: (itemcode = ANY ('{101,102}'::integer[]))
  • Rows Removed by Filter: 1
26. 0.200 0.200 ↑ 1.0 1 100

Index Scan using idx_vehicle_type_translation_en on md_vehicle_type_translation t (cost=0.15..0.37 rows=1 width=29) (actual time=0.002..0.002 rows=1 loops=100)

  • Index Cond: (translatable_id = f.vehicletype_id)
27. 0.152 0.700 ↑ 1.0 1 100

Nested Loop Left Join (cost=0.71..6.84 rows=1 width=65) (actual time=0.006..0.007 rows=1 loops=100)

28. 0.300 0.300 ↑ 1.0 1 100

Index Scan using idx_character_driver on md_character c (cost=0.29..4.53 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (r.driver_id = id)
29. 0.248 0.248 ↑ 1.0 1 62

Index Scan using md_character_pkey on md_character cm (cost=0.42..2.29 rows=1 width=34) (actual time=0.004..0.004 rows=1 loops=62)

  • Index Cond: (c.company_id = id)
30. 0.900 0.900 ↑ 1.0 1 100

Index Scan using md_location_pkey on md_location loc (cost=0.43..7.41 rows=1 width=118) (actual time=0.008..0.009 rows=1 loops=100)

  • Index Cond: (id = r.origin_id)
31. 0.300 0.300 ↑ 1.0 1 100

Index Scan using md_area_pkey on md_area are (cost=0.28..0.76 rows=1 width=40) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (loc.cityareaid = id)
32. 0.300 0.300 ↑ 1.0 1 100

Index Scan using md_location_pkey on md_location loc_1 (cost=0.43..7.41 rows=1 width=111) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (r.destination_id = id)
33. 0.400 0.400 ↑ 1.0 1 100

Index Scan using md_character_pkey on md_character dcm2 (cost=0.42..6.94 rows=1 width=34) (actual time=0.003..0.004 rows=1 loops=100)

  • Index Cond: (r.drivercompany_id = id)
34. 0.600 0.600 ↑ 1.0 1 100

Index Scan using md_profile_pkey on md_profile p (cost=0.42..6.37 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=100)

  • Index Cond: (r.profile_id = id)