explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yi6

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=2,160,791.42..2,160,791.44 rows=10 width=28) (actual rows= loops=)

2.          

CTE o2

3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.71..2,160,724.84 rows=72 width=24) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.28..2,160,343.70 rows=72 width=20) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.86..2,160,220.21 rows=72 width=16) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Index Scan using tbl_order_active_status_idx on tbl_order o (cost=0.29..6,217.03 rows=4,686 width=4) (actual rows= loops=)

  • Index Cond: (status = 11)
7. 0.000 0.000 ↓ 0.0

Index Scan using tbl_order_item_order_idx on tbl_order_item oi (cost=0.57..459.66 rows=1 width=16) (actual rows= loops=)

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

SubPlan (for Index Scan)

9. 0.000 0.000 ↓ 0.0

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 rows= loops=)

  • Index Cond: (order_item_id = oi.id)
10. 0.000 0.000 ↓ 0.0

Append (cost=0.42..1.70 rows=2 width=8) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = oi.prod_id)
12. 0.000 0.000 ↓ 0.0

Index Scan using tbl_products_int_pkey on tbl_products_int p_1 (cost=0.43..0.91 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = oi.prod_id)
13. 0.000 0.000 ↓ 0.0

Append (cost=0.42..0.97 rows=2 width=4) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Only Scan using tbl_products_pkey on tbl_products p2 (cost=0.42..0.47 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = p.parent_id)
15. 0.000 0.000 ↓ 0.0

Index Only Scan using tbl_products_int_pkey on tbl_products_int p2_1 (cost=0.43..0.49 rows=1 width=4) (actual rows= loops=)

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

SubPlan (for Nested Loop Left Join)

17. 0.000 0.000 ↓ 0.0

Index Scan using tbl_order_reserve_order_item_id_key on tbl_order_reserve (cost=0.29..4.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (order_item_id = oi.id)
18. 0.000 0.000 ↓ 0.0

Sort (cost=66.58..66.76 rows=72 width=28) (actual rows= loops=)

  • Sort Key: o2.o_id, ((SubPlan 4))
19. 0.000 0.000 ↓ 0.0

CTE Scan on o2 (cost=0.00..65.02 rows=72 width=28) (actual rows= loops=)

20.          

SubPlan (for CTE Scan)

21. 0.000 0.000 ↓ 0.0

Limit (cost=0.84..0.88 rows=1 width=4) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..650,336.95 rows=16,033,700 width=4) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..186,497.27 rows=4 width=4) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..186,488.33 rows=2 width=4) (actual rows= loops=)

  • Join Filter: (p3.id = ps.id)
25. 0.000 0.000 ↓ 0.0

Append (cost=0.00..146,407.45 rows=1,335,425 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Seq Scan on tbl_products_stock ps (cost=0.00..36,162.25 rows=295,780 width=4) (actual rows= loops=)

  • Filter: (cnt >= o2.quantity)
27. 0.000 0.000 ↓ 0.0

Seq Scan on tbl_products_stock_int ps_1 (cost=0.00..103,568.08 rows=1,039,645 width=4) (actual rows= loops=)

  • Filter: (cnt >= o2.quantity)
28. 0.000 0.000 ↓ 0.0

Materialize (cost=0.42..18.14 rows=2 width=4) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Append (cost=0.42..18.13 rows=2 width=4) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Index Scan using tbl_products_parent_idx on tbl_products p3 (cost=0.42..13.97 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (parent_id = o2.p2_id)
  • Filter: ((id <> o2.p_id) AND (visible <> 1) AND (catpro_id !~~ '%_U'::text))
31. 0.000 0.000 ↓ 0.0

Index Scan using tbl_products_int_parent_idx on tbl_products_int p3_1 (cost=0.12..4.15 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (parent_id = o2.p2_id)
  • Filter: ((id <> o2.p_id) AND (visible <> 1) AND (catpro_id !~~ '%_U'::text))
32. 0.000 0.000 ↓ 0.0

Materialize (cost=0.42..8.89 rows=2 width=0) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Append (cost=0.42..8.88 rows=2 width=0) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Index Only Scan using bnd_prod_flags_idx on bnd_prod_flags bpf (cost=0.42..4.43 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (prod_id = o2.p2_id)
35. 0.000 0.000 ↓ 0.0

Index Only Scan using bnd_prod_flags_int_idx on bnd_prod_flags_int bpf_1 (cost=0.42..4.43 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (prod_id = o2.p2_id)
36. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..273,439.50 rows=4,008,425 width=0) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Append (cost=0.00..253,397.38 rows=4,008,425 width=0) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on tbl_products (cost=0.00..93,199.02 rows=887,582 width=0) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on tbl_products_int (cost=0.00..140,156.23 rows=3,120,843 width=0) (actual rows= loops=)