explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ucrm

Settings
# exclusive inclusive rows x rows loops node
1. 3.969 31.789 ↓ 597.0 597 1

Nested Loop Left Join (cost=102.71..1,229.73 rows=1 width=1,084) (actual time=0.501..31.789 rows=597 loops=1)

2.          

CTE basic_data

3. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=5.79..88.97 rows=18 width=46) (actual time=0.003..0.003 rows=0 loops=1)

  • Join Filter: (ct.countryid = cs.countryid)
4. 0.001 0.002 ↓ 0.0 0 1

Nested Loop (cost=1.18..48.64 rows=1 width=44) (actual time=0.002..0.002 rows=0 loops=1)

  • Join Filter: (cs.countryid = fc.countryid)
5. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on tgb_featurecountry fc (cost=0.00..23.38 rows=1 width=5) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (featureid = 2)
6. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=1.18..25.18 rows=7 width=39) (never executed)

  • Hash Cond: (fc2.countryid = cs.countryid)
7. 0.000 0.000 ↓ 0.0 0

Seq Scan on tgb_featurecountry fc2 (cost=0.00..23.38 rows=147 width=5) (never executed)

  • Filter: (featureid = 1)
8. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.08..1.08 rows=8 width=34) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Seq Scan on tgb_countrysettings cs (cost=0.00..1.08 rows=8 width=34) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.61..40.11 rows=2 width=10) (never executed)

11. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.47..39.76 rows=2 width=10) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on mwq_areapolygons ap (cost=4.19..23.14 rows=2 width=4) (never executed)

  • Recheck Cond: (polygongeo ~ '0101000020E6100000915F3FC406FF4740D7DCD1FF72553D40'::geometry)
  • Filter: _st_contains(polygongeo, '0101000020E6100000915F3FC406FF4740D7DCD1FF72553D40'::geometry)
13. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on mwq_areapolygons_polygongeo (cost=0.00..4.18 rows=5 width=0) (never executed)

  • Index Cond: (polygongeo ~ '0101000020E6100000915F3FC406FF4740D7DCD1FF72553D40'::geometry)
14. 0.000 0.000 ↓ 0.0 0

Index Scan using mwq_area_pkey on mwq_area a_1 (cost=0.28..8.30 rows=1 width=10) (never executed)

  • Index Cond: (id = ap.id)
15. 0.000 0.000 ↓ 0.0 0

Index Scan using mwq_city_pkey on mwq_city ct (cost=0.14..0.16 rows=1 width=8) (never executed)

  • Index Cond: (id = a_1.cityid)
16.          

Initplan (forNested Loop Left Join)

17. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on basic_data (cost=0.00..0.36 rows=18 width=1) (actual time=0.000..0.000 rows=0 loops=1)

18. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on basic_data basic_data_1 (cost=0.00..0.36 rows=18 width=1) (actual time=0.000..0.000 rows=0 loops=1)

19. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on basic_data basic_data_2 (cost=0.00..0.36 rows=18 width=1) (actual time=0.001..0.001 rows=0 loops=1)

20. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on basic_data basic_data_3 (cost=0.00..0.36 rows=18 width=1) (actual time=0.000..0.000 rows=0 loops=1)

21. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on basic_data basic_data_4 (cost=0.00..0.36 rows=18 width=1) (actual time=0.000..0.000 rows=0 loops=1)

22. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on basic_data basic_data_5 (cost=0.00..0.36 rows=18 width=4) (actual time=0.001..0.001 rows=0 loops=1)

23. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on basic_data basic_data_6 (cost=0.00..0.36 rows=18 width=1) (actual time=0.001..0.001 rows=0 loops=1)

24. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on basic_data basic_data_7 (cost=0.00..0.36 rows=18 width=1) (actual time=0.001..0.001 rows=0 loops=1)

25. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on basic_data basic_data_8 (cost=0.00..0.36 rows=18 width=516) (actual time=0.000..0.000 rows=0 loops=1)

26. 0.003 0.003 ↓ 0.0 0 1

CTE Scan on basic_data basic_data_9 (cost=0.00..0.36 rows=18 width=4) (actual time=0.003..0.003 rows=0 loops=1)

27. 0.795 27.813 ↓ 597.0 597 1

Nested Loop (cost=9.86..1,136.61 rows=1 width=360) (actual time=0.458..27.813 rows=597 loops=1)

  • Join Filter: (ca.restaurantid = cr.restaurantid)
28. 1.095 25.227 ↓ 597.0 597 1

Nested Loop (cost=9.58..1,136.30 rows=1 width=343) (actual time=0.449..25.227 rows=597 loops=1)

  • Join Filter: (ca.branchid = bd.branchid)
29. 1.044 22.347 ↓ 595.0 595 1

Nested Loop (cost=9.29..1,135.85 rows=1 width=283) (actual time=0.442..22.347 rows=595 loops=1)

30. 0.849 19.518 ↓ 54.1 595 1

Nested Loop (cost=8.87..1,042.87 rows=11 width=271) (actual time=0.434..19.518 rows=595 loops=1)

31. 0.702 17.479 ↓ 54.1 595 1

Nested Loop (cost=8.59..1,037.95 rows=11 width=267) (actual time=0.430..17.479 rows=595 loops=1)

  • Join Filter: (ca.restaurantid = sl.referenceid)
32. 0.728 14.992 ↓ 54.1 595 1

Nested Loop (cost=8.30..1,032.89 rows=11 width=249) (actual time=0.422..14.992 rows=595 loops=1)

33. 0.902 12.470 ↓ 49.8 598 1

Nested Loop (cost=7.88..931.52 rows=12 width=252) (actual time=0.415..12.470 rows=598 loops=1)

34. 0.759 9.759 ↓ 46.4 603 1

Nested Loop (cost=7.60..844.01 rows=13 width=184) (actual time=0.408..9.759 rows=603 loops=1)

  • Join Filter: (ca.restaurantid = brandrestaurant.restaurantid)
35. 0.847 7.191 ↓ 43.1 603 1

Nested Loop (cost=7.32..833.31 rows=14 width=172) (actual time=0.401..7.191 rows=603 loops=1)

36. 0.614 3.932 ↓ 14.7 603 1

Hash Join (cost=7.03..584.64 rows=41 width=55) (actual time=0.387..3.932 rows=603 loops=1)

  • Hash Cond: (ca.deliverytime = tdt.value)
37. 3.060 3.291 ↓ 12.6 603 1

Bitmap Heap Scan on tgb_coveragepolygon ca (cost=5.37..582.39 rows=48 width=48) (actual time=0.352..3.291 rows=603 loops=1)

  • Recheck Cond: ((polygongeo ~ '0101000020E6100000915F3FC406FF4740D7DCD1FF72553D40'::geometry) AND (restaurantstatus <> 3))
  • Filter: _st_contains(polygongeo, '0101000020E6100000915F3FC406FF4740D7DCD1FF72553D40'::geometry)
  • Rows Removed by Filter: 329
  • Heap Blocks: exact=844
38. 0.231 0.231 ↓ 6.5 932 1

Bitmap Index Scan on tgb_coveragepolygon_polygongeo_idx (cost=0.00..5.36 rows=144 width=0) (actual time=0.231..0.231 rows=932 loops=1)

  • Index Cond: (polygongeo ~ '0101000020E6100000915F3FC406FF4740D7DCD1FF72553D40'::geometry)
39. 0.011 0.027 ↑ 1.0 29 1

Hash (cost=1.30..1.30 rows=29 width=9) (actual time=0.027..0.027 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
40. 0.016 0.016 ↑ 1.0 29 1

Seq Scan on tlb_deliverytimes tdt (cost=0.00..1.30 rows=29 width=9) (actual time=0.005..0.016 rows=29 loops=1)

  • Filter: ((NOT isdeleted) AND (NOT status))
  • Rows Removed by Filter: 1
41. 2.412 2.412 ↑ 1.0 1 603

Index Scan using mwq_restaurant_pkey on mwq_restaurant r (cost=0.28..6.05 rows=1 width=117) (actual time=0.003..0.004 rows=1 loops=603)

  • Index Cond: (id = ca.restaurantid)
  • Filter: ((NOT isdeleted) AND (NOT isdeleted) AND (status <> 3) AND (status <> 3))
42. 1.809 1.809 ↑ 1.0 1 603

Index Scan using tlb_restaurantbrand_resttid_brndid_idx on tlb_restaurant_brand brandrestaurant (cost=0.28..0.75 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=603)

  • Index Cond: ((restaurantid = r.id) AND (brandtype = 1))
  • Filter: (isactive AND (NOT isdeleted))
43. 1.809 1.809 ↑ 1.0 1 603

Index Scan using mwq_branch_pkey on mwq_branch b (cost=0.29..6.72 rows=1 width=68) (actual time=0.003..0.003 rows=1 loops=603)

  • Index Cond: (id = ca.branchid)
  • Filter: ((NOT isdeleted) AND (status <> 3))
  • Rows Removed by Filter: 0
44. 1.794 1.794 ↑ 1.0 1 598

Index Scan using tlb_restaurant_timingstatus_pkey on tlb_coveragepolygon_timingstatus cpt (cost=0.42..8.44 rows=1 width=5) (actual time=0.003..0.003 rows=1 loops=598)

  • Index Cond: (coveragepolygonid = ca.id)
45. 1.785 1.785 ↑ 1.0 1 595

Index Scan using tlb_slug_referenceid_idx on tlb_slug sl (cost=0.28..0.45 rows=1 width=18) (actual time=0.002..0.003 rows=1 loops=595)

  • Index Cond: (referenceid = brandrestaurant.restaurantid)
  • Filter: ((NOT isdeleted) AND (typeid = 1) AND (brandtype = 1))
46. 1.190 1.190 ↑ 1.0 1 595

Index Scan using mwq_area_pkey on mwq_area a (cost=0.28..0.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=595)

  • Index Cond: (id = b.areaid)
47. 1.785 1.785 ↑ 1.0 1 595

Index Scan using tlb_restaurantlistdynamic_restid_areaid_idx on tlb_restaurantlistdynamic rld (cost=0.42..8.44 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=595)

  • Index Cond: ((areaid = ca.areaid) AND (restid = ca.restaurantid))
48. 1.785 1.785 ↑ 1.0 1 595

Index Scan using tlb_branchdetails_branchid_idx on tlb_branch_details bd (cost=0.29..0.44 rows=1 width=68) (actual time=0.003..0.003 rows=1 loops=595)

  • Index Cond: ((branchid = b.id) AND (brandtype = 1))
49. 1.791 1.791 ↑ 1.0 1 597

Index Scan using "tlb_CommonRestuarantFields_restid_idx" on tlb_commonrestuarantfields cr (cost=0.28..0.30 rows=1 width=37) (actual time=0.002..0.003 rows=1 loops=597)

  • Index Cond: (restaurantid = rld.restid)
50. 0.000 0.000 ↓ 0.0 0 597

Index Scan using "tlb_Sponsored_Restaurant_rest_area_brnd_idx" on tlb_sponsored_restaurant sp (cost=0.28..0.48 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=597)

  • Index Cond: ((r.id = restaurantid) AND (areaid = $12) AND (brandtype = 1))