explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.024 4.716 ↑ 2.0 1 1

Hash Right Join (cost=32.21..36.27 rows=2 width=228) (actual time=4.711..4.716 rows=1 loops=1)

  • Hash Cond: ((j.reference_id)::text = (xc.cart_id)::text)
2. 0.000 0.016 ↑ 1.0 1 1

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

3. 0.016 0.016 ↑ 1.0 1 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.015..0.016 rows=1 loops=1)

  • Index Cond: ((job_type)::text = 'bulk_campaign_reattribution'::text)
  • Filter: ((job_status)::text = ANY ('{active,queued,processing,error}'::text[]))
4. 0.006 4.676 ↑ 2.0 1 1

Hash (cost=31.90..31.90 rows=2 width=207) (actual time=4.676..4.676 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
5. 0.066 4.670 ↑ 2.0 1 1

GroupAggregate (cost=31.80..31.88 rows=2 width=207) (actual time=4.670..4.670 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
6. 0.047 4.604 ↓ 5.0 10 1

Sort (cost=31.80..31.80 rows=2 width=207) (actual time=4.603..4.604 rows=10 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: 30kB
7. 0.033 4.557 ↓ 5.0 10 1

Nested Loop (cost=3.46..31.79 rows=2 width=207) (actual time=3.164..4.557 rows=10 loops=1)

8. 0.000 4.372 ↓ 8.0 8 1

Nested Loop Left Join (cost=3.46..31.62 rows=1 width=269) (actual time=3.119..4.372 rows=8 loops=1)

  • Filter: (COALESCE((ir.sub_available_amount)::numeric, (d.sub_amount)::numeric) > '0'::numeric)
9. 0.010 0.411 ↓ 8.0 8 1

Nested Loop Left Join (cost=3.21..31.06 rows=1 width=271) (actual time=0.150..0.411 rows=8 loops=1)

10. 0.021 0.337 ↓ 8.0 8 1

Nested Loop (cost=2.79..27.30 rows=1 width=248) (actual time=0.132..0.337 rows=8 loops=1)

11. 0.014 0.276 ↓ 8.0 8 1

Nested Loop (cost=2.37..24.69 rows=1 width=240) (actual time=0.119..0.276 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))
12. 0.010 0.214 ↓ 4.0 8 1

Nested Loop (cost=2.09..22.38 rows=2 width=286) (actual time=0.106..0.214 rows=8 loops=1)

13. 0.004 0.174 ↓ 3.0 3 1

Nested Loop (cost=1.81..20.98 rows=1 width=269) (actual time=0.095..0.174 rows=3 loops=1)

14. 0.005 0.151 ↑ 1.0 1 1

Nested Loop (cost=1.54..20.60 rows=1 width=235) (actual time=0.082..0.151 rows=1 loops=1)

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

Nested Loop (cost=1.12..17.66 rows=1 width=161) (actual time=0.072..0.130 rows=2 loops=1)

  • Join Filter: ((xc.cart_id)::bpchar = (xc_1.cart_id)::bpchar)
16. 0.006 0.105 ↓ 2.0 2 1

Nested Loop (cost=0.83..16.10 rows=1 width=144) (actual time=0.058..0.105 rows=2 loops=1)

17. 0.057 0.057 ↓ 3.0 3 1

Index Scan using ix_sc_carts_master_account_id on sc_carts xc (cost=0.41..10.97 rows=1 width=53) (actual time=0.039..0.057 rows=3 loops=1)

  • Index Cond: ((master_account_id)::bpchar = 'AC16070029072097'::bpchar)
  • Filter: ((cart_type)::text = 'DIGITAL CAMPAIGN'::text)
  • Rows Removed by Filter: 4
18. 0.042 0.042 ↑ 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.012..0.014 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
19. 0.020 0.020 ↑ 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.010..0.010 rows=1 loops=2)

  • Index Cond: ((cart_id)::bpchar = (i_1.cart_id)::bpchar)
  • Filter: (coordination_type = 'SL MANAGED'::text)
20. 0.016 0.016 ↓ 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.006..0.008 rows=2 loops=2)

  • Index Cond: ((cart_id)::bpchar = (xc_1.cart_id)::bpchar)
  • Filter: ((template_id)::bpchar = 'TM18070000993589'::bpchar)
21. 0.019 0.019 ↓ 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.011..0.019 rows=3 loops=1)

  • Index Cond: ((cart_id)::bpchar = (xc_1.cart_id)::bpchar)
22. 0.030 0.030 ↓ 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=51) (actual time=0.008..0.010 rows=3 loops=3)

  • Index Cond: ((schedule_plan_id)::bpchar = (p.schedule_plan_id)::bpchar)
23. 0.048 0.048 ↑ 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.005..0.006 rows=1 loops=8)

  • Index Cond: ((schedule_id)::bpchar = (s.schedule_id)::bpchar)
24. 0.040 0.040 ↑ 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.005..0.005 rows=1 loops=8)

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

Index Scan using ix_ord_items_composite3 on ord_items i_2 (cost=0.42..3.75 rows=1 width=108) (actual time=0.008..0.008 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
26. 4.080 4.080 ↓ 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.510..0.510 rows=0 loops=8)

  • Filter: (((order_id)::bpchar = (i_2.order_id)::bpchar) AND ((order_item_id)::bpchar = (i_2.order_item_id)::bpchar))
27. 0.000 0.152 ↑ 2.0 1 8

Append (cost=0.00..0.15 rows=2 width=32) (actual time=0.016..0.019 rows=1 loops=8)

28. 0.056 0.056 ↓ 0.0 0 8

Result (cost=0.00..0.06 rows=1 width=32) (actual time=0.006..0.007 rows=0 loops=8)

  • One-Time Filter: (m.market_start <= '2020-05-15'::date)
29. 0.096 0.096 ↑ 1.0 1 8

Result (cost=0.00..0.06 rows=1 width=32) (actual time=0.011..0.012 rows=1 loops=8)

Planning time : 41.758 ms
Execution time : 5.111 ms