explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.004 9.659 ↑ 1.0 1 1

Nested Loop Left Join (cost=766.81..770.95 rows=1 width=228) (actual time=9.658..9.659 rows=1 loops=1)

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

GroupAggregate (cost=766.52..766.61 rows=1 width=207) (actual time=9.628..9.628 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.079 9.460 ↓ 8.0 8 1

Sort (cost=766.52..766.53 rows=1 width=268) (actual time=9.459..9.460 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.019 9.381 ↓ 8.0 8 1

Nested Loop Left Join (cost=15.09..766.51 rows=1 width=268) (actual time=8.336..9.381 rows=8 loops=1)

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

Nested Loop Left Join (cost=14.84..765.95 rows=1 width=270) (actual time=2.737..3.058 rows=8 loops=1)

6. 0.027 2.982 ↓ 8.0 8 1

Nested Loop (cost=14.41..762.19 rows=1 width=239) (actual time=2.710..2.982 rows=8 loops=1)

7. 0.009 2.915 ↓ 8.0 8 1

Nested Loop (cost=13.99..759.58 rows=1 width=231) (actual time=2.689..2.915 rows=8 loops=1)

8. 0.014 2.866 ↓ 8.0 8 1

Nested Loop (cost=13.72..758.85 rows=1 width=240) (actual time=2.674..2.866 rows=8 loops=1)

  • Join Filter: ((i.cart_item_id)::bpchar = (i_1.swap_item_id)::bpchar)
  • Rows Removed by Join Filter: 8
9. 0.010 2.804 ↓ 8.0 8 1

Nested Loop (cost=13.30..755.91 rows=1 width=217) (actual time=2.660..2.804 rows=8 loops=1)

  • Join Filter: ((d.reference_cart_item_id)::bpchar = (i.cart_item_id)::bpchar)
  • Rows Removed by Join Filter: 8
10. 0.015 2.746 ↓ 8.0 8 1

Nested Loop (cost=12.88..752.98 rows=1 width=143) (actual time=2.640..2.746 rows=8 loops=1)

  • Join Filter: ((xc.cart_id)::bpchar = (xc_1.cart_id)::bpchar)
11. 1.402 2.675 ↓ 8.0 8 1

Hash Join (cost=12.60..751.17 rows=1 width=126) (actual time=2.606..2.675 rows=8 loops=1)

  • Hash Cond: ((d.reference_cart_id)::bpchar = (xc.cart_id)::bpchar)
12. 1.156 1.156 ↓ 1.0 2,129 1

Seq Scan on sch_schedule_details d (cost=0.00..733.05 rows=2,105 width=73) (actual time=0.015..1.156 rows=2,129 loops=1)

13. 0.008 0.117 ↓ 3.0 3 1

Hash (cost=12.58..12.58 rows=1 width=53) (actual time=0.116..0.117 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.109 0.109 ↓ 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.055..0.109 rows=3 loops=1)

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

Index Scan using pk_sc_campaigns on sc_campaigns xc_1 (cost=0.29..1.79 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=8)

  • Index Cond: ((cart_id)::bpchar = (d.reference_cart_id)::bpchar)
  • Filter: (coordination_type = 'SL MANAGED'::text)
16. 0.048 0.048 ↓ 2.0 2 8

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

  • Index Cond: ((cart_id)::bpchar = (xc_1.cart_id)::bpchar)
  • Filter: ((template_id)::bpchar = 'TM18070000993589'::bpchar)
17. 0.048 0.048 ↓ 2.0 2 8

Index Scan using ix_sc_items_cart_id on sc_items i_1 (cost=0.42..2.92 rows=1 width=91) (actual time=0.004..0.006 rows=2 loops=8)

  • Index Cond: ((cart_id)::bpchar = (xc_1.cart_id)::bpchar)
  • Filter: ((item_order_status)::text = 'PROCESSING'::text)
18. 0.040 0.040 ↑ 1.0 1 8

Index Scan using pk_sch_schedule on sch_schedule s (cost=0.28..0.73 rows=1 width=25) (actual time=0.005..0.005 rows=1 loops=8)

  • Index Cond: ((schedule_id)::bpchar = (d.schedule_id)::bpchar)
19. 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)
20. 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=116) (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
21. 6.304 6.304 ↓ 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.788..0.788 rows=0 loops=8)

  • Filter: (((order_id)::bpchar = (i_2.order_id)::bpchar) AND ((order_item_id)::bpchar = (i_2.order_item_id)::bpchar))
22. 0.016 0.027 ↓ 0.0 0 1

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

23. 0.011 0.011 ↓ 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.011..0.011 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 : 28.397 ms
Execution time : 7.753 ms