explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CdYd : Optimization for: plan #To0T

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.005 30.205 ↑ 1.0 1 1

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

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

GroupAggregate (cost=766.52..766.61 rows=1 width=207) (actual time=30.173..30.174 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.097 29.998 ↓ 8.0 8 1

Sort (cost=766.52..766.53 rows=1 width=276) (actual time=29.997..29.998 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.033 29.901 ↓ 8.0 8 1

Nested Loop Left Join (cost=15.09..766.51 rows=1 width=276) (actual time=8.877..29.901 rows=8 loops=1)

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

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

6. 0.013 3.096 ↓ 8.0 8 1

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

7. 0.016 3.035 ↓ 8.0 8 1

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

8. 0.013 2.971 ↓ 8.0 8 1

Nested Loop (cost=13.72..758.85 rows=1 width=240) (actual time=2.699..2.971 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.018 2.870 ↓ 8.0 8 1

Nested Loop (cost=13.30..755.91 rows=1 width=217) (actual time=2.685..2.870 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.022 2.796 ↓ 8.0 8 1

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

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

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

  • Hash Cond: ((d.reference_cart_id)::bpchar = (xc.cart_id)::bpchar)
12. 1.190 1.190 ↓ 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.021..1.190 rows=2,129 loops=1)

13. 0.008 0.127 ↓ 3.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.119 0.119 ↓ 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.056..0.119 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.088 0.088 ↑ 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.011..0.011 rows=1 loops=8)

  • Index Cond: ((cart_id)::bpchar = (d.reference_cart_id)::bpchar)
  • Filter: (coordination_type = 'SL MANAGED'::text)
16. 0.056 0.056 ↓ 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.006..0.007 rows=2 loops=8)

  • Index Cond: ((cart_id)::bpchar = (xc_1.cart_id)::bpchar)
  • Filter: ((template_id)::bpchar = 'TM18070000993589'::bpchar)
17. 0.088 0.088 ↓ 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.011 rows=2 loops=8)

  • Index Cond: ((cart_id)::bpchar = (xc_1.cart_id)::bpchar)
  • Filter: ((item_order_status)::text = 'PROCESSING'::text)
18. 0.048 0.048 ↑ 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.006..0.006 rows=1 loops=8)

  • Index Cond: ((schedule_id)::bpchar = (d.schedule_id)::bpchar)
19. 0.048 0.048 ↑ 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.006..0.006 rows=1 loops=8)

  • Index Cond: ((schedule_detail_id)::bpchar = (d.schedule_detail_id)::bpchar)
  • Filter: (market_end >= '2020-05-15'::date)
20. 0.104 0.104 ↓ 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.013 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. 26.656 26.656 ↓ 0.0 0 8

Function Scan on app_refund_list_valid_items ir (cost=0.25..0.55 rows=1 width=104) (actual time=3.332..3.332 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.000 0.026 ↓ 0.0 0 1

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

23. 0.037 0.037 ↓ 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.037..0.037 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 : 20.836 ms
Execution time : 26.589 ms