explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2.806 23.698 ↓ 20.0 20 1

Hash Join (cost=17,389.32..17,448.79 rows=1 width=328) (actual time=0.730..23.698 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.003 0.199 ↓ 2.0 2 1

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

4. 0.013 0.162 ↓ 2.0 2 1

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

5. 0.017 0.115 ↓ 2.0 2 1

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

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

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

Materialize (cost=0.43..16.37 rows=2 width=30) (actual time=0.018..0.026 rows=2 loops=2)

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

  • Index Cond: (id = ANY ('{1946335,1946449}'::integer[]))
  • Filter: active
9. 0.034 0.034 ↑ 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.017 rows=1 loops=2)

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

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

CTE dest_lines

12. 1.426 15.368 ↓ 1.8 3,110 1

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

13. 0.655 6.462 ↓ 2.2 680 1

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

14. 0.408 2.157 ↑ 3.4 730 1

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

15. 0.309 0.309 ↑ 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.033..0.309 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.440 1.440 ↑ 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.008..0.016 rows=8 loops=90)

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

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

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

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

20. 0.007 0.213 ↓ 2.0 2 1

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

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

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