explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nu1S : Optimization for: Optimization for: plan #kObB; plan #lS2p

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.247 45.708 ↓ 2.5 20 1

Hash Join (cost=17,393.56..17,433.99 rows=8 width=68) (actual time=1.719..45.708 rows=20 loops=1)

  • Hash Cond: (dest_lines.hash = source_lines.hash)
2.          

CTE source_lines

3. 0.038 0.379 ↓ 2.0 2 1

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

4. 0.019 0.291 ↓ 2.0 2 1

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

5. 0.018 0.222 ↓ 2.0 2 1

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

  • Join Filter: (dish_customs.id = custom_options.dish_custom_id)
  • Rows Removed by Join Filter: 2
6. 0.112 0.112 ↑ 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.093..0.112 rows=2 loops=1)

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

Materialize (cost=0.43..16.37 rows=2 width=30) (actual time=0.034..0.046 rows=2 loops=2)

8. 0.084 0.084 ↑ 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.061..0.084 rows=2 loops=1)

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

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

  • Index Cond: ((id = dish_customs.dish_id) AND (id = ANY ('{298511,298497}'::integer[])))
  • Filter: active
10. 0.050 0.050 ↑ 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.023..0.025 rows=1 loops=2)

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

CTE dest_lines

12. 21.768 37.965 ↓ 1.8 3,110 1

Nested Loop (cost=1.72..17,340.07 rows=1,699 width=125) (actual time=0.272..37.965 rows=3,110 loops=1)

13. 0.577 7.357 ↓ 2.2 680 1

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

14. 0.434 2.400 ↑ 3.4 730 1

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

15. 0.346 0.346 ↑ 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.044..0.346 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.620 1.620 ↑ 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.009..0.018 rows=8 loops=90)

  • Index Cond: (group_dish_id = group_dishes_1.id)
  • Filter: active
17. 4.380 4.380 ↑ 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.005..0.006 rows=1 loops=730)

  • Index Cond: (dish_id = dishes_1.id)
  • Filter: active
18. 8.840 8.840 ↑ 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.009..0.013 rows=5 loops=680)

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

CTE Scan on dest_lines (cost=0.00..33.98 rows=1,699 width=68) (actual time=0.279..43.054 rows=3,110 loops=1)

20. 0.016 0.407 ↓ 2.0 2 1

Hash (cost=0.02..0.02 rows=1 width=32) (actual time=0.407..0.407 rows=2 loops=1)

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

CTE Scan on source_lines (cost=0.00..0.02 rows=1 width=32) (actual time=0.268..0.391 rows=2 loops=1)