explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VWEa : Optimization for: Optimization for: plan #9tqZ; plan #uGCt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 13,409.626 147,769.749 ↑ 41.0 46,831,216 1

Hash Join (cost=7,058,321.46..33,529,776.82 rows=1,921,664,393 width=12) (actual time=132,052.210..147,769.749 rows=46,831,216 loops=1)

  • Hash Cond: (o.restaurant_id = r.id)
2. 547.420 51,476.329 ↑ 48.6 454,672 1

Hash Join (cost=2,611,803.24..7,142,445.27 rows=22,114,885 width=12) (actual time=49,164.516..51,476.329 rows=454,672 loops=1)

  • Hash Cond: (mcm2.menu_category_id = mc2.id)
3. 2,416.722 49,566.634 ↑ 41.4 654,697 1

Hash Join (cost=2,535,249.38..6,436,803.05 rows=27,131,501 width=16) (actual time=43,936.464..49,566.634 rows=654,697 loops=1)

  • Hash Cond: (oi2.menu_item_id = mi2.id)
  • Join Filter: (mi1.unique_id <> mi2.unique_id)
  • Rows Removed by Join Filter: 138916
4. 642.189 3,218.099 ↑ 34.6 1,304,662 1

Nested Loop (cost=3.04..2,090,806.54 rows=45,133,366 width=12) (actual time=0.124..3,218.099 rows=1,304,662 loops=1)

5. 128.970 1,948.982 ↓ 7.7 104,488 1

Nested Loop (cost=2.46..763,480.46 rows=13,646 width=16) (actual time=0.117..1,948.982 rows=104,488 loops=1)

6. 183.836 1,539.068 ↓ 8.4 140,472 1

Nested Loop (cost=2.03..738,193.46 rows=16,741 width=20) (actual time=0.099..1,539.068 rows=140,472 loops=1)

7. 269.548 1,073.260 ↓ 7.5 140,986 1

Nested Loop (cost=1.59..713,875.41 rows=18,773 width=24) (actual time=0.085..1,073.260 rows=140,986 loops=1)

8. 118.532 329.118 ↓ 8.5 237,297 1

Nested Loop (cost=1.15..664,648.89 rows=27,849 width=16) (actual time=0.066..329.118 rows=237,297 loops=1)

9. 30.203 30.203 ↓ 4.7 25,769 1

Index Scan using orders_restaurant_id_created_at_status_idx on orders o (cost=0.57..6,726.86 rows=5,440 width=8) (actual time=0.051..30.203 rows=25,769 loops=1)

  • Index Cond: ((restaurant_id = ANY ('{110834,76371,114680,139648,135773,114619,114640,143084,112266,114681,110807,114566,139640,110797,116055}'::integer[])) AND (created_at >= (now() - '14 days'::interval)))
10. 180.383 180.383 ↑ 288.9 9 25,769

Index Scan using index_order_item_on_order_id on order_item oi1 (cost=0.58..94.94 rows=2,600 width=8) (actual time=0.004..0.007 rows=9 loops=25,769)

  • Index Cond: (order_id = o.id)
11. 474.594 474.594 ↑ 1.0 1 237,297

Index Scan using menu_item_pkey on menu_item mi1 (cost=0.44..1.76 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=237,297)

  • Index Cond: (id = oi1.menu_item_id)
  • Filter: ((NOT alcohol) AND (price > '0'::numeric))
  • Rows Removed by Filter: 0
12. 281.972 281.972 ↑ 1.0 1 140,986

Index Only Scan using index_menu_category_memb_on_item_category on menu_category_membership mcm1 (cost=0.44..1.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=140,986)

  • Index Cond: (menu_item_id = mi1.id)
  • Heap Fetches: 140110
13. 280.944 280.944 ↑ 1.0 1 140,472

Index Only Scan using index_menu_category_on_id_and_top_level on menu_category mc1 (cost=0.43..1.50 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=140,472)

  • Index Cond: ((id = mcm1.menu_category_id) AND (top_level = true))
  • Filter: top_level
  • Heap Fetches: 97283
14. 626.928 626.928 ↑ 216.7 12 104,488

Index Scan using index_order_item_on_order_id on order_item oi2 (cost=0.58..71.27 rows=2,600 width=8) (actual time=0.002..0.006 rows=12 loops=104,488)

  • Index Cond: (order_id = oi1.order_id)
15. 5,158.616 43,931.813 ↑ 1.0 14,176,974 1

Hash (cost=2,279,768.91..2,279,768.91 rows=14,697,075 width=16) (actual time=43,931.813..43,931.813 rows=14,176,974 loops=1)

  • Buckets: 1048576 Batches: 32 Memory Usage: 28992kB
16. 16,995.383 38,773.197 ↑ 1.0 14,176,974 1

Hash Join (cost=1,069,226.77..2,279,768.91 rows=14,697,075 width=16) (actual time=15,548.757..38,773.197 rows=14,176,974 loops=1)

  • Hash Cond: (mcm2.menu_item_id = mi2.id)
17. 6,233.785 6,233.785 ↑ 1.0 21,802,103 1

Seq Scan on menu_category_membership mcm2 (cost=0.00..338,314.04 rows=21,802,104 width=8) (actual time=0.028..6,233.785 rows=21,802,103 loops=1)

18. 5,494.636 15,544.029 ↑ 1.0 16,303,569 1

Hash (cost=798,833.94..798,833.94 rows=16,481,027 width=8) (actual time=15,544.029..15,544.029 rows=16,303,569 loops=1)

  • Buckets: 1048576 Batches: 32 Memory Usage: 28109kB
19. 10,049.393 10,049.393 ↑ 1.0 16,303,569 1

Seq Scan on menu_item mi2 (cost=0.00..798,833.94 rows=16,481,027 width=8) (actual time=0.010..10,049.393 rows=16,303,569 loops=1)

  • Filter: ((NOT alcohol) AND (price > '0'::numeric))
  • Rows Removed by Filter: 8067197
20. 633.425 1,362.275 ↓ 1.0 1,875,276 1

Hash (cost=45,794.86..45,794.86 rows=1,874,800 width=4) (actual time=1,362.274..1,362.275 rows=1,875,276 loops=1)

  • Buckets: 1048576 Batches: 4 Memory Usage: 24671kB
21. 728.850 728.850 ↓ 1.0 1,875,276 1

Seq Scan on menu_category mc2 (cost=0.00..45,794.86 rows=1,874,800 width=4) (actual time=0.008..728.850 rows=1,875,276 loops=1)

  • Filter: top_level
  • Rows Removed by Filter: 424810
22. 3,317.002 82,883.794 ↑ 1.3 9,631,323 1

Hash (cost=4,245,469.89..4,245,469.89 rows=12,254,347 width=4) (actual time=82,883.794..82,883.794 rows=9,631,323 loops=1)

  • Buckets: 1048576 Batches: 32 Memory Usage: 19168kB
23. 11,472.320 79,566.792 ↑ 1.3 9,631,323 1

Hash Join (cost=1,187,178.38..4,245,469.89 rows=12,254,347 width=4) (actual time=24,169.607..79,566.792 rows=9,631,323 loops=1)

  • Hash Cond: (mi_all.id = mcm_all.menu_item_id)
24. 9,519.511 44,250.116 ↑ 1.2 14,513,637 1

Hash Join (cost=16,382.85..2,296,597.03 rows=16,859,034 width=12) (actual time=320.335..44,250.116 rows=14,513,637 loops=1)

  • Hash Cond: (miv.menu_id = m.id)
25. 17,734.379 34,411.290 ↓ 1.0 21,801,665 1

Merge Join (cost=11.55..2,029,879.16 rows=21,801,664 width=12) (actual time=0.060..34,411.290 rows=21,801,665 loops=1)

  • Merge Cond: (miv.menu_item_id = mi_all.id)
26. 9,005.402 9,005.402 ↓ 1.0 21,801,665 1

Index Scan using index_menu_item_version_on_menu_item_id on menu_item_version miv (cost=0.44..875,482.06 rows=21,801,664 width=8) (actual time=0.017..9,005.402 rows=21,801,665 loops=1)

27. 7,671.509 7,671.509 ↑ 1.0 24,370,766 1

Index Only Scan using menu_item_pkey on menu_item mi_all (cost=0.44..826,797.16 rows=24,448,475 width=4) (actual time=0.010..7,671.509 rows=24,370,766 loops=1)

  • Heap Fetches: 3728750
28. 44.804 319.315 ↓ 1.0 133,442 1

Hash (cost=14,705.43..14,705.43 rows=133,269 width=8) (actual time=319.314..319.315 rows=133,442 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 7261kB
29. 85.911 274.511 ↓ 1.0 133,442 1

Hash Join (cost=5,519.65..14,705.43 rows=133,269 width=8) (actual time=108.436..274.511 rows=133,442 loops=1)

  • Hash Cond: (r.menu_group_id = m.menu_group_id)
30. 81.107 81.107 ↑ 1.0 141,024 1

Seq Scan on restaurant r (cost=0.00..7,324.25 rows=141,025 width=8) (actual time=0.007..81.107 rows=141,024 loops=1)

31. 55.516 107.493 ↑ 1.0 171,742 1

Hash (cost=3,365.40..3,365.40 rows=172,340 width=8) (actual time=107.493..107.493 rows=171,742 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8757kB
32. 51.977 51.977 ↑ 1.0 171,742 1

Seq Scan on menu m (cost=0.00..3,365.40 rows=172,340 width=8) (actual time=0.005..51.977 rows=171,742 loops=1)

  • Filter: active
  • Rows Removed by Filter: 3
33. 4,059.860 23,844.356 ↑ 1.4 12,438,192 1

Hash (cost=879,241.36..879,241.36 rows=17,770,894 width=4) (actual time=23,844.355..23,844.356 rows=12,438,192 loops=1)

  • Buckets: 1048576 Batches: 32 Memory Usage: 21892kB
34. 12,259.792 19,784.496 ↑ 1.4 12,438,192 1

Hash Join (cost=76,553.86..879,241.36 rows=17,770,894 width=4) (actual time=1,322.579..19,784.496 rows=12,438,192 loops=1)

  • Hash Cond: (mcm_all.menu_category_id = mc_all.id)
35. 6,206.629 6,206.629 ↑ 1.0 21,802,103 1

Seq Scan on menu_category_membership mcm_all (cost=0.00..338,314.04 rows=21,802,104 width=8) (actual time=0.011..6,206.629 rows=21,802,103 loops=1)

36. 626.170 1,318.075 ↓ 1.0 1,875,276 1

Hash (cost=45,794.86..45,794.86 rows=1,874,800 width=4) (actual time=1,318.075..1,318.075 rows=1,875,276 loops=1)

  • Buckets: 1048576 Batches: 4 Memory Usage: 24671kB
37. 691.905 691.905 ↓ 1.0 1,875,276 1

Seq Scan on menu_category mc_all (cost=0.00..45,794.86 rows=1,874,800 width=4) (actual time=0.006..691.905 rows=1,875,276 loops=1)

  • Filter: top_level
  • Rows Removed by Filter: 424810
Planning time : 8.037 ms
Execution time : 156,413.650 ms