explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pn3q : Optimization for: Optimization for: Optimization for: plan #To0T; plan #CdYd; plan #I9sL

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.022 7.811 ↑ 1.0 1 1

Nested Loop Left Join (cost=33.54..37.68 rows=1 width=228) (actual time=7.810..7.811 rows=1 loops=1)

  • Join Filter: ((j.reference_id)::text = (xc.cart_id)::text)
2. 0.173 7.771 ↑ 1.0 1 1

GroupAggregate (cost=33.25..33.34 rows=1 width=207) (actual time=7.771..7.771 rows=1 loops=1)

  • Group Key: xc.cart_id, i.cart_item_id, i.template_id, i.cart_item_name, i_1.cart_item_id, i_1.template_id, i_1.cart_item_name
3. 0.051 7.598 ↓ 8.0 8 1

Sort (cost=33.25..33.26 rows=1 width=268) (actual time=7.597..7.598 rows=8 loops=1)

  • Sort Key: xc.cart_id, i.cart_item_id, i.cart_item_name, i_1.cart_item_id, i_1.template_id, i_1.cart_item_name
  • Sort Method: quicksort Memory: 27kB
4. 0.021 7.547 ↓ 8.0 8 1

Nested Loop Left Join (cost=3.46..33.24 rows=1 width=268) (actual time=4.567..7.547 rows=8 loops=1)

  • Filter: (COALESCE((ir.sub_available_amount)::numeric, (d.sub_amount)::numeric) > '0'::numeric)
5. 0.017 2.366 ↓ 8.0 8 1

Nested Loop Left Join (cost=3.21..32.68 rows=1 width=270) (actual time=0.376..2.366 rows=8 loops=1)

6. 0.006 2.253 ↓ 8.0 8 1

Nested Loop (cost=2.79..28.92 rows=1 width=239) (actual time=0.311..2.253 rows=8 loops=1)

7. 0.011 2.191 ↓ 8.0 8 1

Nested Loop (cost=2.37..26.31 rows=1 width=231) (actual time=0.288..2.191 rows=8 loops=1)

  • Join Filter: (((xc_1.cart_id)::bpchar = (d.reference_cart_id)::bpchar) AND ((i_1.swap_item_id)::bpchar = (d.reference_cart_item_id)::bpchar))
8. 0.011 2.092 ↓ 4.0 8 1

Nested Loop (cost=2.09..24.00 rows=2 width=277) (actual time=0.255..2.092 rows=8 loops=1)

9. 0.005 2.009 ↓ 3.0 3 1

Nested Loop (cost=1.81..22.60 rows=1 width=269) (actual time=0.228..2.009 rows=3 loops=1)

10. 0.005 0.393 ↑ 1.0 1 1

Nested Loop (cost=1.54..22.21 rows=1 width=235) (actual time=0.212..0.393 rows=1 loops=1)

  • Join Filter: ((i_1.swap_item_id)::bpchar = (i.cart_item_id)::bpchar)
  • Rows Removed by Join Filter: 3
11. 0.005 0.358 ↓ 2.0 2 1

Nested Loop (cost=1.12..19.28 rows=1 width=161) (actual time=0.185..0.358 rows=2 loops=1)

  • Join Filter: ((xc.cart_id)::bpchar = (xc_1.cart_id)::bpchar)
12. 0.008 0.321 ↓ 2.0 2 1

Nested Loop (cost=0.83..17.71 rows=1 width=144) (actual time=0.155..0.321 rows=2 loops=1)

13. 0.208 0.208 ↓ 3.0 3 1

Index Scan using ix_sc_carts_master_account_id on sc_carts xc (cost=0.41..12.58 rows=1 width=53) (actual time=0.119..0.208 rows=3 loops=1)

  • Index Cond: ((master_account_id)::bpchar = 'AC16070029072097'::bpchar)
  • Filter: ((cart_type)::text = 'DIGITAL CAMPAIGN'::text)
  • Rows Removed by Filter: 4
14. 0.105 0.105 ↑ 1.0 1 3

Index Scan using ix_sc_items_cart_id on sc_items i_1 (cost=0.42..5.12 rows=1 width=91) (actual time=0.034..0.035 rows=1 loops=3)

  • Index Cond: ((cart_id)::bpchar = (xc.cart_id)::bpchar)
  • Filter: ((item_order_status)::text = 'PROCESSING'::text)
  • Rows Removed by Filter: 1
15. 0.032 0.032 ↑ 1.0 1 2

Index Scan using pk_sc_campaigns on sc_campaigns xc_1 (cost=0.29..1.55 rows=1 width=17) (actual time=0.016..0.016 rows=1 loops=2)

  • Index Cond: ((cart_id)::bpchar = (i_1.cart_id)::bpchar)
  • Filter: (coordination_type = 'SL MANAGED'::text)
16. 0.030 0.030 ↓ 2.0 2 2

Index Scan using ix_sc_items_cart_id on sc_items i (cost=0.42..2.92 rows=1 width=74) (actual time=0.007..0.015 rows=2 loops=2)

  • Index Cond: ((cart_id)::bpchar = (xc_1.cart_id)::bpchar)
  • Filter: ((template_id)::bpchar = 'TM18070000993589'::bpchar)
17. 1.611 1.611 ↓ 1.5 3 1

Index Scan using ix_sch_schedule_plan_cart_id on sch_schedule_plan p (cost=0.28..0.36 rows=2 width=34) (actual time=0.014..1.611 rows=3 loops=1)

  • Index Cond: ((cart_id)::bpchar = (xc_1.cart_id)::bpchar)
18. 0.072 0.072 ↓ 1.5 3 3

Index Scan using ix_sch_schedule_schedule_plan_id on sch_schedule s (cost=0.28..1.38 rows=2 width=42) (actual time=0.019..0.024 rows=3 loops=3)

  • Index Cond: ((schedule_plan_id)::bpchar = (p.schedule_plan_id)::bpchar)
19. 0.088 0.088 ↑ 1.0 1 8

Index Scan using ix_sch_schedule_details_schedule_id on sch_schedule_details d (cost=0.28..1.14 rows=1 width=73) (actual time=0.010..0.011 rows=1 loops=8)

  • Index Cond: ((schedule_id)::bpchar = (s.schedule_id)::bpchar)
20. 0.056 0.056 ↑ 1.0 1 8

Index Scan using uk_item_in_market_schedule_detail_id on item_in_market m (cost=0.42..2.60 rows=1 width=25) (actual time=0.007..0.007 rows=1 loops=8)

  • Index Cond: ((schedule_detail_id)::bpchar = (d.schedule_detail_id)::bpchar)
  • Filter: (market_end >= '2020-05-15'::date)
21. 0.096 0.096 ↓ 0.0 0 8

Index Scan using ix_ord_items_composite3 on ord_items i_2 (cost=0.42..3.75 rows=1 width=116) (actual time=0.012..0.012 rows=0 loops=8)

  • Index Cond: (((parent_cart_id)::bpchar = (d.reference_cart_id)::bpchar) AND ((parent_cart_item_id)::bpchar = (d.reference_cart_item_id)::bpchar))
  • Filter: ((schedule_detail_id)::bpchar = (d.schedule_detail_id)::bpchar)
  • Rows Removed by Filter: 1
22. 5.160 5.160 ↓ 0.0 0 8

Function Scan on priv_refund_get_valid_items ir (cost=0.25..0.55 rows=1 width=96) (actual time=0.645..0.645 rows=0 loops=8)

  • Filter: (((order_id)::bpchar = (i_2.order_id)::bpchar) AND ((order_item_id)::bpchar = (i_2.order_item_id)::bpchar))
23. 0.001 0.018 ↓ 0.0 0 1

Append (cost=0.29..4.31 rows=1 width=22) (actual time=0.018..0.018 rows=0 loops=1)

24. 0.017 0.017 ↓ 0.0 0 1

Index Scan using sys_jobs_active_job_type_idx on sys_jobs_active j (cost=0.29..4.31 rows=1 width=22) (actual time=0.017..0.017 rows=0 loops=1)

  • Index Cond: ((job_type)::text = 'bulk_campaign_reattribution'::text)
  • Filter: ((job_status)::text = ANY ('{active,queued,processing,error}'::text[]))
Planning time : 27.973 ms
Execution time : 4.983 ms