explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lS2p : Optimization for: plan #kObB

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.719 21.531 ↓ 20.0 20 1

Hash Join (cost=17,389.32..17,448.79 rows=1 width=328) (actual time=0.716..21.531 rows=20 loops=1)

  • Hash Cond: ((dest_lines.group_name = source_lines.group_name) AND (dest_lines.dish_name = source_lines.dish_name) AND (dest_lines.custom_name = source_lines.custom_name) AND (dest_lines.option_name = source_lines.option_name))
2.          

CTE source_lines

3. 0.006 0.211 ↓ 2.0 2 1

Nested Loop (cost=1.71..53.46 rows=1 width=125) (actual time=0.125..0.211 rows=2 loops=1)

4. 0.013 0.173 ↓ 2.0 2 1

Nested Loop (cost=1.29..45.01 rows=1 width=104) (actual time=0.099..0.173 rows=2 loops=1)

5. 0.014 0.128 ↓ 2.0 2 1

Nested Loop (cost=0.85..32.10 rows=1 width=48) (actual time=0.072..0.128 rows=2 loops=1)

  • Join Filter: (dish_customs.id = custom_options.dish_custom_id)
  • Rows Removed by Join Filter: 2
6. 0.054 0.054 ↑ 1.0 2 1

Index Scan using dish_customs_pkey on dish_customs (cost=0.42..15.67 rows=2 width=22) (actual time=0.019..0.054 rows=2 loops=1)

  • Index Cond: (id = ANY ('{290378,290402}'::integer[]))
  • Filter: active
7. 0.025 0.060 ↑ 1.0 2 2

Materialize (cost=0.43..16.37 rows=2 width=30) (actual time=0.022..0.030 rows=2 loops=2)

8. 0.035 0.035 ↑ 1.0 2 1

Index Scan using custom_options_pkey on custom_options (cost=0.43..16.36 rows=2 width=30) (actual time=0.021..0.035 rows=2 loops=1)

  • Index Cond: (id = ANY ('{1946335,1946449}'::integer[]))
  • Filter: active
9. 0.032 0.032 ↑ 1.0 1 2

Index Scan using dishes_pkey on dishes (cost=0.43..12.90 rows=1 width=60) (actual time=0.015..0.016 rows=1 loops=2)

  • Index Cond: ((id = dish_customs.dish_id) AND (id = ANY ('{298511,298497}'::integer[])))
  • Filter: active
10. 0.032 0.032 ↑ 1.0 1 2

Index Scan using group_dishes_pkey on group_dishes (cost=0.42..8.44 rows=1 width=25) (actual time=0.015..0.016 rows=1 loops=2)

  • Index Cond: (id = dishes.group_dish_id)
  • Filter: (active AND (eatery_id = 166803))
11.          

CTE dest_lines

12. 1.722 13.101 ↓ 1.8 3,110 1

Nested Loop (cost=1.72..17,335.82 rows=1,699 width=125) (actual time=0.155..13.101 rows=3,110 loops=1)

13. 0.984 5.939 ↓ 2.2 680 1

Nested Loop (cost=1.29..16,905.09 rows=307 width=99) (actual time=0.133..5.939 rows=680 loops=1)

14. 0.402 2.035 ↑ 3.4 730 1

Nested Loop (cost=0.86..15,531.98 rows=2,475 width=81) (actual time=0.060..2.035 rows=730 loops=1)

15. 0.283 0.283 ↑ 1.2 90 1

Index Scan using group_dishes_eatery_id_active_idx on group_dishes group_dishes_1 (cost=0.43..383.88 rows=107 width=25) (actual time=0.035..0.283 rows=90 loops=1)

  • Index Cond: ((eatery_id = ANY ('{1766153,902633,1745948,5716,61026,1556879,243700,1659200,3229,200141}'::integer[])) AND (active = true))
  • Filter: active
16. 1.350 1.350 ↑ 15.4 8 90

Index Scan using dishes_group_dish_id_idx on dishes dishes_1 (cost=0.43..140.34 rows=123 width=60) (actual time=0.007..0.015 rows=8 loops=90)

  • Index Cond: (group_dish_id = group_dishes_1.id)
  • Filter: active
17. 2.920 2.920 ↑ 4.0 1 730

Index Scan using dish_customs_dish_id_idx on dish_customs dish_customs_1 (cost=0.42..0.51 rows=4 width=22) (actual time=0.004..0.004 rows=1 loops=730)

  • Index Cond: (dish_id = dishes_1.id)
  • Filter: active
18. 5.440 5.440 ↑ 3.2 5 680

Index Scan using custom_options_dish_custom_id_idx on custom_options custom_options_1 (cost=0.43..1.24 rows=16 width=30) (actual time=0.005..0.008 rows=5 loops=680)

  • Index Cond: (dish_custom_id = dish_customs_1.id)
  • Filter: active
19. 18.586 18.586 ↓ 1.8 3,110 1

CTE Scan on dest_lines (cost=0.00..33.98 rows=1,699 width=164) (actual time=0.162..18.586 rows=3,110 loops=1)

20. 0.006 0.226 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=164) (actual time=0.226..0.226 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
21. 0.220 0.220 ↓ 2.0 2 1

CTE Scan on source_lines (cost=0.00..0.02 rows=1 width=164) (actual time=0.128..0.220 rows=2 loops=1)