explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yRy4 : Optimization for: plan #Yi6

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.003 280.069 ↑ 1.0 10 1

Limit (cost=2,160,727.84..2,160,727.86 rows=10 width=24) (actual time=280.066..280.069 rows=10 loops=1)

2.          

CTE o2

3. 11.776 270.618 ↓ 129.2 9,299 1

Nested Loop Left Join (cost=1.71..2,160,724.84 rows=72 width=24) (actual time=0.082..270.618 rows=9,299 loops=1)

4. 5.608 203.048 ↓ 129.2 9,299 1

Nested Loop (cost=1.28..2,160,343.70 rows=72 width=20) (actual time=0.067..203.048 rows=9,299 loops=1)

5. 3.857 123.048 ↓ 129.2 9,299 1

Nested Loop (cost=0.86..2,160,220.21 rows=72 width=16) (actual time=0.050..123.048 rows=9,299 loops=1)

6. 11.841 11.841 ↓ 1.2 5,650 1

Index Scan using tbl_order_active_status_idx on tbl_order o (cost=0.29..6,217.03 rows=4,686 width=4) (actual time=0.020..11.841 rows=5,650 loops=1)

  • Index Cond: (status = 11)
7. 45.547 107.350 ↓ 2.0 2 5,650

Index Scan using tbl_order_item_order_idx on tbl_order_item oi (cost=0.57..459.66 rows=1 width=16) (actual time=0.013..0.019 rows=2 loops=5,650)

  • Index Cond: (order_id = o.id)
  • Filter: (COALESCE((SubPlan 2), 0) = 0)
  • Rows Removed by Filter: 2
8.          

SubPlan (for Index Scan)

9. 61.803 61.803 ↑ 1.0 1 20,601

Index Scan using tbl_order_reserve_order_item_id_key on tbl_order_reserve tbl_order_reserve_1 (cost=0.29..4.30 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=20,601)

  • Index Cond: (order_item_id = oi.id)
10. 0.000 74.392 ↑ 2.0 1 9,299

Append (cost=0.42..1.70 rows=2 width=8) (actual time=0.005..0.008 rows=1 loops=9,299)

11. 46.495 46.495 ↑ 1.0 1 9,299

Index Scan using tbl_products_pkey on tbl_products p (cost=0.42..0.78 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=9,299)

  • Index Cond: (id = oi.prod_id)
12. 27.897 27.897 ↓ 0.0 0 9,299

Index Scan using tbl_products_int_pkey on tbl_products_int p_1 (cost=0.43..0.91 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=9,299)

  • Index Cond: (id = oi.prod_id)
13. 9.299 37.196 ↓ 0.0 0 9,299

Append (cost=0.42..0.97 rows=2 width=4) (actual time=0.002..0.004 rows=0 loops=9,299)

14. 18.598 18.598 ↓ 0.0 0 9,299

Index Only Scan using tbl_products_pkey on tbl_products p2 (cost=0.42..0.47 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=9,299)

  • Index Cond: (id = p.parent_id)
  • Heap Fetches: 4100
15. 9.299 9.299 ↓ 0.0 0 9,299

Index Only Scan using tbl_products_int_pkey on tbl_products_int p2_1 (cost=0.43..0.49 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=9,299)

  • Index Cond: (id = p.parent_id)
  • Heap Fetches: 0
16.          

SubPlan (for Nested Loop Left Join)

17. 18.598 18.598 ↑ 1.0 1 9,299

Index Scan using tbl_order_reserve_order_item_id_key on tbl_order_reserve (cost=0.29..4.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=9,299)

  • Index Cond: (order_item_id = oi.id)
18. 2.753 280.066 ↑ 7.2 10 1

Sort (cost=3.00..3.18 rows=72 width=24) (actual time=280.065..280.066 rows=10 loops=1)

  • Sort Key: o2.o_id
  • Sort Method: top-N heapsort Memory: 26kB
19. 277.313 277.313 ↓ 129.2 9,299 1

CTE Scan on o2 (cost=0.00..1.44 rows=72 width=24) (actual time=0.084..277.313 rows=9,299 loops=1)

Planning time : 1.277 ms
Execution time : 280.305 ms