explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9eiM : Optimization for: plan #4sWT

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.184 1,083.094 ↓ 1.3 4 1

HashAggregate (cost=37,737.36..37,737.39 rows=3 width=98) (actual time=1,083.088..1,083.094 rows=4 loops=1)

  • Group Key: order_lines.id, zalo_food_dishes.store_item_id
2.          

CTE test

3. 195.957 954.047 ↑ 1.0 103,609 1

Hash Join (cost=24,192.23..35,030.46 rows=106,319 width=18) (actual time=510.077..954.047 rows=103,609 loops=1)

  • Hash Cond: (order_line_custom_options.custom_option_id = custom_options.id)
4. 205.596 332.963 ↑ 1.0 103,620 1

Hash Join (cost=3,770.01..9,413.86 rows=106,319 width=8) (actual time=84.892..332.963 rows=103,620 loops=1)

  • Hash Cond: (order_line_custom_options.order_line_custom_id = order_line_customs.id)
5. 43.136 43.136 ↑ 1.0 109,435 1

Seq Scan on order_line_custom_options (cost=0.00..2,173.33 rows=112,133 width=8) (actual time=0.010..43.136 rows=109,435 loops=1)

6. 49.081 84.231 ↑ 1.0 103,184 1

Hash (cost=2,058.67..2,058.67 rows=104,267 width=8) (actual time=84.231..84.231 rows=103,184 loops=1)

  • Buckets: 8,192 Batches: 2 Memory Usage: 2,016kB
7. 35.150 35.150 ↑ 1.0 103,184 1

Seq Scan on order_line_customs (cost=0.00..2,058.67 rows=104,267 width=8) (actual time=0.008..35.150 rows=103,184 loops=1)

8. 264.393 425.127 ↓ 1.1 404,548 1

Hash (cost=13,417.21..13,417.21 rows=381,521 width=18) (actual time=425.127..425.127 rows=404,548 loops=1)

  • Buckets: 4,096 Batches: 16 Memory Usage: 1,314kB
9. 160.734 160.734 ↓ 1.1 404,548 1

Seq Scan on custom_options (cost=0.00..13,417.21 rows=381,521 width=18) (actual time=0.010..160.734 rows=404,548 loops=1)

10. 0.092 1,082.910 ↓ 3.3 10 1

Nested Loop (cost=170.53..2,706.86 rows=3 width=98) (actual time=767.623..1,082.910 rows=10 loops=1)

  • Join Filter: (order_lines.dish_id = dishes.id)
11. 27.784 1,082.568 ↓ 3.3 10 1

Hash Right Join (cost=170.11..2,695.22 rows=3 width=76) (actual time=767.579..1,082.568 rows=10 loops=1)

  • Hash Cond: (test.idcustom = order_lines.id)
12. 1,051.662 1,051.662 ↑ 1.0 103,609 1

CTE Scan on test (cost=0.00..2,126.38 rows=106,319 width=36) (actual time=510.084..1,051.662 rows=103,609 loops=1)

13. 0.005 3.122 ↓ 2.0 4 1

Hash (cost=170.08..170.08 rows=2 width=44) (actual time=3.122..3.122 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
14. 1.703 3.117 ↓ 2.0 4 1

Hash Join (cost=8.38..170.08 rows=2 width=44) (actual time=3.095..3.117 rows=4 loops=1)

  • Hash Cond: (zalo_food_dishes.dish_id = order_lines.dish_id)
15. 1.382 1.382 ↓ 1.0 5,640 1

Seq Scan on zalo_food_dishes (cost=0.00..133.79 rows=5,579 width=36) (actual time=0.038..1.382 rows=5,640 loops=1)

16. 0.008 0.032 ↓ 2.0 4 1

Hash (cost=8.35..8.35 rows=2 width=8) (actual time=0.032..0.032 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
17. 0.024 0.024 ↓ 2.0 4 1

Index Scan using order_lines_order_id_idx on order_lines (cost=0.29..8.35 rows=2 width=8) (actual time=0.020..0.024 rows=4 loops=1)

  • Index Cond: (order_id = 39,603)
18. 0.250 0.250 ↑ 1.0 1 10

Index Scan using dishes_pkey on dishes (cost=0.42..3.87 rows=1 width=34) (actual time=0.024..0.025 rows=1 loops=10)

  • Index Cond: (id = zalo_food_dishes.dish_id)